Getting Started With Pivot Tables in Excel
The fastest way to turn a long list of rows into an actual summary
By Shehryar Hassan, Microsoft 365 & Azure Consultant
Every few months someone sends me a spreadsheet with a few thousand rows and asks how to "get Excel to add these up by month" or "break this down by department." The answer is almost always a pivot table, and most people who've used Excel for years have never actually built one because the name makes it sound harder than it is.
What a pivot table actually does
A pivot table takes a list of data, like sales records or expense entries, and lets you summarize it without writing a single formula. You drag a column into a box and Excel groups, counts, or totals it for you. Move a different column into a different box and the whole summary changes shape. Nothing about it is permanent, your original data stays exactly as it was.
To build one, click anywhere inside your data, then go to Insert > PivotTable. Excel will guess the range and offer to put the result on a new sheet, which is usually what you want. You'll see a blank grid on the left and a field list on the right with four boxes: Filters, Columns, Rows, and Values.
Drag the column you want to group by (department, region, month) into Rows. Drag the number you want to add up (sales amount, hours, cost) into Values. That's it, you have a working summary. If the numbers show up as a count instead of a sum, click the field in the Values box and change it to Sum.
A few things that trip people up
The most common mistake is a messy source range: merged cells, blank rows in the middle of the data, or a header row that isn't really one row. Pivot tables want a plain table, one header row, no blanks, no merged cells. Fix the source and the pivot table usually fixes itself.
If your source data changes and the pivot table doesn't update, that's normal. Right-click anywhere in the pivot table and choose Refresh. It won't pick up new rows automatically.
Once you're comfortable with Rows and Values, try adding a field to Columns as well. It splits your summary into a grid instead of a single list, which is worth it once you're comparing more than one thing at a time.
Get new posts by email
One note when there is something worth reading. No spam, unsubscribe anytime.
Related articles
Why Your Excel Conditional Formatting Rules Stop Working
If your conditional formatting rules used to work and now don't, the cause is almost always overlapping rules left behind from copy and paste, not a broken feature.
Why Your Word Document Opens in Protected View (and When It's Safe to Turn Off)
Word's Protected View isn't a glitch, it's a safety check for files that come from outside your trusted circle. Here's why it happens and when it's actually fine to click through it.
Why Your Outlook Search Doesn't Find Old Emails (and How to Fix It)
A short guide to why Outlook search misses emails you know exist, and the handful of fixes that actually work.