Pivot table in Excel

A PivotTable answers one shape of question: "for each combination of these categories, what is the aggregate of this number?" Give it a flat table (one row per transaction) and it collapses those rows into a compact summary. Every cell of that summary is an aggregate, usually a sum, of all the source rows that share a category combination. In SQL terms it's SELECT category, SUM(value) ... GROUP BY category, except that you build it by dragging field names into boxes, and you can rearrange it live.

What do the four areas of a pivot table do?

The Field List has four drop zones, and everything else rests on knowing what each one does:

• Rows hold the categories listed down the left. Each distinct value of the field you drop here becomes one row. Drop State in Rows and you get one row per state. Drop a second field, say Product, below it and you get nested rows: each state broken into its products, with subtotals.
• Columns hold the categories spread across the top. They work like Rows, only horizontal. Drop Month in Columns and each month becomes a column. Put Rows and Columns together and you have a cross-tab, a two-dimensional grid.
• Values holds the number that gets aggregated at every Row×Column intersection. Drop Revenue here and each body cell becomes the sum of revenue for that state and month. It's the only area that produces numbers. Everything else produces labels.
• Filters (the "Report Filter" area) is a top-level gate above the whole pivot. Drop Product here and a dropdown appears that subsets the entire pivot to one product or several. It filters everything at once. A Row or Column field does something else and breaks the data out by that field.

Keep one rule in your head. Rows and Columns decide the shape of the question. Values decides the number. Filters decides the subset. A field is a category when it defines groups (Rows/Columns/Filters) and a measure when it's the thing being totalled (Values). Text and dates are usually categories, and amounts are usually measures.

How do you build and rearrange a pivot table in Excel?

To build one, select any cell inside your source Table, then go to Insert ▸ PivotTable ▸ From Table/Range (keyboard: Alt, N, V). Excel confirms the source, which is your Table name, e.g. Sales, and offers New Worksheet. Take it. A blank pivot canvas appears with the PivotTable Fields list on the right. Tick a field's checkbox and Excel guesses an area. Text goes to Rows and numbers go to Values. For more control, drag each field name into the box you want. To get "revenue by state by month," drag State to Rows, Month to Columns and Revenue to Values. Done. The cross-tab fills instantly.

The superpower is rearranging without rewriting. Drag Month out of Columns and into Rows below State, and the same numbers re-form as a nested list. Drag Product into Filters and a dropdown lets you isolate one product. Nothing you built breaks, because there were no formulas to break. The pivot is the formula, and every drag is an edit to it. If the Field List vanishes, click inside the pivot and toggle PivotTable Analyze ▸ Field List. To move the whole pivot, use PivotTable Analyze ▸ Actions ▸ Move PivotTable.

Worked example: a small India sales extract

Here's the dataset we'll keep pivoting, a small India sales extract (revenue in ₹):

Drop State into Rows, Month into Columns and Revenue into Values, and every body cell is a sum. Take Maharashtra's January cell. Two source rows share that state and month, so the cell shows ₹2,50,000 + ₹90,000 = ₹3,40,000. That automatic aggregation of many rows into one cell is the entire point. Worked example 1 builds and verifies the full grid.