Pivot Tables in Excel: A Beginner's Guide
Last updated June 2026
Quick answer
To create a pivot table in Excel, click any cell inside your data, go to the Insert tab, click PivotTable, and click OK. A blank pivot appears on a new sheet with a field list on the right. Drag a text field into Rows and a number field into Values — Excel groups and totals automatically.
A pivot table summarizes a long list — hundreds of expense rows, say — into a compact table of totals you can reshape by dragging. You don't write a single formula. The catch is that your source data has to be clean first: one header row at the top, no blank header cells, and one record per row.
How do you create a pivot table?
Click inside your data
Click any single cell within the list you want to summarize — Excel auto-detects the full range out to the surrounding blank rows and columns. You don't need to select the whole table first.
Insert the PivotTable
Go to the Insert tab and click PivotTable (far left). In the dialog, confirm the Table/Range Excel picked covers all your data, leave New Worksheet selected, and click OK.
Drag a field into Rows
A blank pivot and the PivotTable Fields pane appear. From the field list at the top, drag Category down into the Rows box. Excel lists every unique category once, alphabetically.
Drag a field into Values
Drag Amount into the Values box. Because Amount is numeric, Excel labels it Sum of Amount and totals each category. You now have a per-category spending summary.
Group dates into months
To summarize by month, drag Date into Rows, then right-click any date in the pivot and choose Group. In the dialog, select Months (add Years if your data spans more than one), and click OK.
Format the values as currency
Right-click any number in the Values area, choose Number Format, pick Currency, set decimals, and click OK. This formats the whole field at once and survives refreshes — don't format the cells manually.
Refresh after the data changes
A pivot is a snapshot. When you edit or add source rows, right-click anywhere in the pivot and choose Refresh (or press
Alt+F5) to pull in the new numbers. Pivots never update on their own.
| Area | What it does | Example |
|---|---|---|
| Rows | Lists each unique value down the left as a row label. | Drag Category here to get one row per category. |
| Columns | Lists each unique value across the top, creating a grid. | Drag Month here to spread categories across months. |
| Values | The number being aggregated — summed or counted in each cell. | Drag Amount here to total spending per category. |
| Filters | A dropdown above the pivot that limits which records are shown. | Drag Merchant here to view one store at a time. |
Why does the pivot count instead of sum?
When you drop a field into Values, Excel chooses the aggregation automatically: Sum if the column holds only numbers, Count if it contains any text or blank cells. So if Sum of Amount shows up as Count of Amount, one of your Amount cells is text or empty. To switch it back, click the field's dropdown in the Values box, choose Value Field Settings, pick Sum, and click OK — then fix the stray non-numeric cell in the source.
Before you build the pivot, select your data and press Ctrl+T to format it as an Excel Table. New rows you type below it become part of the table automatically, so a single Refresh picks them up — no need to redraw the source range by hand.
Frequently asked questions
- Pivot table vs SUMIFS — which should I use?
- Use a pivot table to explore and reshape totals fast with no formulas; use `SUMIFS` when you need a single answer that updates live inside a report. Pivots are snapshots you refresh;
SUMIFSrecalculates instantly as the data changes. - Why does my data need a header row?
- A pivot table uses the top row of your range as field names in the PivotTable Fields list. If any header cell is blank, Excel refuses to build the pivot until you fill it in — every column must have a unique, non-empty header.
- Why aren't new rows showing up in my pivot?
- Two reasons: pivots are snapshots, so you must right-click and Refresh after adding data, and the new rows must fall inside the pivot's source range. Formatting the source as an Excel Table with
Ctrl+Tfixes the range problem permanently. - Can I edit the cells inside a pivot table?
- No — the value cells are generated output and can't be typed over. To change a number, fix it in the source data and refresh. You can rename row and column labels in place, but the totals themselves are always computed.