Pivot Tables Basics
Thousands of rows of sales, expenses, or survey answers — and you need totals by month, by region, by person. Pivot tables do that in seconds with dragging, not formulas: they group, count, and sum your data however you ask. The scariest-looking Excel feature is secretly the friendliest. For gentler lookups first, see the lookup wizard.
TL;DR / Quick Fix
Quick fix: pivot tables summarize thousands of rows in seconds — select your data, insert a pivot table, then drag fields into rows and values. No formulas needed. Start with one question your data should answer, like total sales per month, and let the pivot do the counting.
Follow these steps
- Prepare clean data. Give your data headers in the first row, no blank rows inside, and one kind of thing per column. Pivots love tidy tables and choke on messy ones.
- Insert the pivot table. Select any cell in your data, then Insert, PivotTable. Accept the defaults — a new sheet with an empty pivot layout appears.
- Drag a field to Rows. Drag the category you want to group by — month, region, salesperson — into the Rows area. Your data instantly groups itself.
- Drag a field to Values. Drag the number you want summarized — sales, quantity — into Values. Excel sums it per group automatically; switch to count or average if that fits better.
- Add a second dimension. Drag another field to Columns to split each row group further — sales per month per region. This is where pivots start feeling like magic.
- Refresh when data changes. Pivots do not update live. Right-click and Refresh after editing the source data, or your summary quietly goes stale.
What NOT to do
- Do not build a pivot on messy data. Blank rows and mixed columns produce nonsense — clean first, pivot second.
- Do not fear the empty layout. The blank pivot looks intimidating for exactly thirty seconds, until your first drag.
- Do not forget to refresh. A pivot shows the data as of its last refresh, not as of right now.
- Do not use pivots for tiny lists. Ten rows do not need a pivot — sort and eyeball instead.
Frequently asked questions
Do I need formulas for pivot tables?
No — that is the beauty of them. Dragging fields replaces the formulas you would otherwise write by hand.
Why is my pivot showing counts instead of sums?
Excel guesses the aggregation. Right-click a value, choose Summarize Values By, and pick Sum.
Can I pivot data from multiple sheets?
Yes, with the data model, but start with single-table pivots until the basics feel natural.