How to Make a Pivot Table in Excel: A Beginner's Guide
How to Make a Pivot Table in Excel: A Beginner's Guide
A pivot table summarises large amounts of data into a smaller, readable table by grouping and calculating values automatically, without you writing a single formula. You build one by dragging field names into four boxes — Rows, Columns, Values, and Filters — and Excel does the counting, summing, or averaging for you. It's genuinely one of the highest-value skills in Excel, and it's a lot less intimidating than it looks once you've built two or three.
I'm going to be honest about something here: pivot tables were the one Excel feature I avoided for years, purely out of habit, because early on someone showed me a rushed five-minute demo that made no sense and I decided they "weren't for me." I kept doing manual SUMIF formulas and sorting things by hand instead, which worked, but took forever on anything with a few thousand rows. It wasn't until I had to summarise a genuinely messy client dataset — inconsistent naming, dates in three different formats, dozens of duplicate entries — for a report due the next morning, that I finally sat down properly and learned pivot tables out of sheer necessity. What would've taken me most of that evening with formulas took about fifteen minutes once I understood the four boxes. I've been a bit annoyed at myself ever since for putting it off so long.
What do you need before building a pivot table?
Before anything else, your source data needs to be in a proper table format:
- One row per record, no blank rows or blank columns inside the data
- Column headers in the first row, with no merged cells anywhere
- Consistent data types down each column — dates as actual dates, numbers as actual numbers, not text pretending to be numbers
Messy source data is the number one reason a pivot table looks wrong or behaves strangely, and it's worth fixing this before you build anything rather than troubleshooting the pivot table afterwards.
How do I actually create a pivot table?
- Click anywhere inside your data range
- Go to Insert > PivotTable
- Confirm the data range Excel has selected (it usually gets this right automatically) and choose whether to place the pivot table on a New Worksheet or an Existing Worksheet
- Click OK, and you'll see the empty pivot table shell on the left, with the PivotTable Fields panel on the right listing every column from your data
- Drag field names into the four boxes at the bottom of that panel: Filters, Columns, Rows, Values
What's the actual difference between Rows, Columns, Values, and Filters?
This is the part that confuses most beginners, so it's worth explaining plainly.
| Box | What it does | Example |
|---|---|---|
| Rows | Groups your data into rows down the left side | Client names, one per row |
| Columns | Groups your data into columns across the top | Months, one per column |
| Values | The actual numbers being calculated (sum, count, average) | Total sales amount |
| Filters | A dropdown that limits the whole table without changing its layout | Filter the entire table to one region |
A common beginner mistake is dragging a text field like "Client Name" into the Values box, expecting a sum, and getting a count instead — because Excel can't add up text, so it defaults to counting how many entries there are. If you want an actual total, the field you drag into Values needs to be a number column, like sales amount or quantity.
Why is my pivot table showing "Count" instead of "Sum"?
This is the single most common pivot table question people search for, so it deserves a direct answer. It happens for one of two reasons:
- The field you dragged into Values isn't a proper number. If even one cell in that column contains text, a blank, or a number stored as text, Excel treats the whole column as non-numeric and defaults to Count.
- You dragged a text field into Values on purpose without realising Count was the only sensible option for it.
To fix it: click the small dropdown arrow next to the field inside the Values box, choose Value Field Settings, and manually select Sum (or Average, Max, Min, etc.) instead of Count.
How do I refresh a pivot table when my data changes?
Pivot tables don't update automatically the way a normal formula does — you need to manually refresh them, which is one of their few genuine downsides compared to something like a FILTER or SUMIFS formula that recalculates instantly.
- Right-click anywhere inside the pivot table and choose Refresh, or
- Go to PivotTable Analyze > Refresh in the ribbon, or
- Press Alt, then A, then R as a keyboard shortcut
If you've added entirely new rows to your source data (not just edited existing values), you may also need to update the data range itself via PivotTable Analyze > Change Data Source, especially if your source isn't set up as an official Excel Table. Converting your source data into a proper Table (Insert > Table) before building the pivot table avoids this problem entirely, since Tables expand automatically to include new rows.
Should I use a pivot table or a formula like SUMIFS?
Neither is strictly better — they suit different situations. If you've read my post on <a href="https://www.ourwealth.com.au/2026/08/filter-sort-unique-dynamic-arrays-excel.html">FILTER, SORT and UNIQUE</a>, this will sound familiar, since it's a similar trade-off.
| Situation | Better choice |
|---|---|
| Quick one-off summary of large data | Pivot table |
| Need the result to update live as you type, without refreshing | Formula (SUMIFS, FILTER) |
| Want to explore data interactively (drag fields around, drill down) | Pivot table |
| Result needs to feed into other formulas elsewhere in the workbook | Formula |
| Non-technical colleagues need to use it themselves | Pivot table (more forgiving to click around in) |
My honest rule of thumb: if I'm exploring data to understand it, pivot table. If I'm building something that needs to stay live and feed a dashboard, formulas.
FAQ
Why does my pivot table show blank cells instead of zero? By default, Excel leaves cells blank where there's no data rather than showing zero. To change this, right-click inside the pivot table, choose PivotTable Options, and set "For empty cells show" to 0 under the Layout & Format tab.
Can I have two value fields in the same pivot table? Yes — drag a second field into the Values box and it'll sit alongside the first, giving you two calculated columns (or rows, depending on layout) side by side.
Why did my pivot table's formatting disappear after I refreshed it? This happens if "Preserve cell formatting on update" is unticked in PivotTable Options. Turn it on under the Layout & Format tab to stop your number formatting and colours resetting every time you refresh.
How do I group dates into months or years in a pivot table? Right-click any date inside the Rows area, choose Group, and select Months, Quarters, or Years. This works far better than trying to manually reformat a date column beforehand.
Do pivot tables slow down large workbooks? They can add some file size and calculation overhead, especially with several pivot tables pulling from large datasets, but for anything under a few hundred thousand rows on a reasonably modern computer, it's rarely noticeable in day-to-day use.
Comments
Post a Comment