Why this matters
PivotTables are named in Excel job adverts for roles from accounts officers to Excel specialists. They are the fastest way to answer "how much, by what?" questions. A table of 5,000 sales becomes "total sales by region and month" in under a minute, with no formulas, and you can rearrange it to answer the next question just as fast.
What a PivotTable does
A PivotTable groups your rows by the values in one or more columns and then calculates something for each group: a sum, a count or an average. You decide the layout by placing fields (your column headers) into four areas:
- Rows: the categories down the left, such as Branch.
- Columns: categories across the top, such as Month.
- Values: what to calculate, such as Sum of Amount.
- Filters: narrow the whole PivotTable, such as only one product category.
Building one
- Click any cell in your tidy table (an Excel table is best, because new rows will be included).
- Choose Insert, then PivotTable, and place it on a new sheet.
- In the field list, drag or tick Branch into Rows and Amount into Values.
Excel shows each branch once with its total. Now drag Month into Columns and you have a grid of branch by month. Drag Branch out and Item in, and you have totals by item instead. That rearranging is the "pivot".