A spreadsheet with a thousand rows of sales is not an answer — it’s a haystack. A PivotTable turns it into an answer in about a minute: total sales by region, by product, by month, with no formulas at all. If you’ve never made one, this is the single most useful Excel skill you can learn this week.
What a PivotTable does
A PivotTable summarises a flat table by grouping rows and adding up (or counting, or averaging) the numbers in each group. You drag fields into boxes — rows, columns, values — and the table rebuilds itself instantly. Change your mind about the grouping and you drag again; no formulas to rewrite.
Before you start: clean the source data
A PivotTable is only as good as its source table. Check three things first:
- One header row, no blank rows or columns inside the data. A blank row splits the table in two and the PivotTable will only see the top half.
- Every column has a header with a unique name. Duplicate headers confuse the field list.
- Dates are real dates, numbers are real numbers. A date stored as text won’t group by month; a number stored as text gets counted instead of summed.
Ideally, format the source as an Excel Table first (select the data, press Ctrl+T). A Table grows automatically when you add rows, and the PivotTable can be set to read from it.
Step-by-step: your first PivotTable
- Click any cell inside your sales data.
- Go to Insert → PivotTable. Excel guesses the range — check it covers all your data, then click OK. A new sheet appears with an empty PivotTable and a field list.
- Drag Region into the Rows box. Drag Product into the Columns box. Drag Amount into the Values box.
- You now have a cross-tab: regions down the side, products across the top, totals in the middle, grand totals on the edges.
- To see sales by month instead, drag Date into Rows. Right-click a date and choose Group → Months (and Years, if your data spans more than one year).
Worked example
| Region | Product | Amount |
|---|---|---|
| East | Notebook | $120 |
| East | Pen set | $85 |
| West | Notebook | $200 |
| East | Notebook | $95 |
| West | Pen set | $60 |
Drag Region to Rows and Amount to Values, and the PivotTable shows: East $300, West $260, Grand Total $560. Drag Product to Columns as well and you get the full cross-tab: East/Notebook $215, East/Pen set $85, West/Notebook $200, West/Pen set $60.
Three things to try next
- Change the summary. Click a value, choose Value Field Settings, and switch Sum to Count, Average, Max, or “% of Grand Total”. Count is useful for “how many orders per region”.
- Filter. Drag a field into the Filters box to add a dropdown above the table — pick one product and the whole table recalculates.
- Sort. Right-click a row label and sort largest to smallest to see your best region first.
Common errors
“Count of Amount” instead of “Sum of Amount”. Some cells in the Amount column contain text (or are blank). Excel falls back to counting. Fix the source data, then right-click the field → Value Field Settings → Sum.
New data doesn’t appear. A PivotTable is a snapshot. After adding rows to the source, right-click the PivotTable and choose Refresh. If you used an Excel Table as the source, the range grows automatically; otherwise change the source range under PivotTable Analyze → Change Data Source.
“Cannot group that selection.” Your date column contains a blank cell or a text value. Find and fix it — every cell in the column must be a real date.
The field list disappeared. Click inside the PivotTable, then go to PivotTable Analyze → Field List (or right-click → Show Field List).
Practice file
Download the PivotTable practice file (.xlsx) — 60 rows of sales data (region, product, date, amount) ready to pivot, plus a sheet with the finished cross-tab so you can check your work.
Google Sheets
Google Sheets has PivotTables too (Insert → Pivot table). The layout is a little different — rows, columns, values and filters work the same way — and the concepts in this post transfer directly.
All amounts in this post are in nominal dollars — they are not adjusted for inflation. Example figures are illustrative, not market data.