TL;DR:
A pivot table summarizes a long list of rows into a small table of totals, without you writing a single formula.
You drag fields into four areas: rows, columns, values and filters. The value area does the adding.
A pivot table does not always update on its own, so refresh it after the source data changes.
You have a sheet with a few hundred sales rows, one per order, and someone asks for total sales by region and month. You could filter and add by hand, or write a dozen formulas. A pivot table gives you the whole summary in about a minute, and you can reshape it by dragging.
This guide builds one from scratch in Excel or Google Sheets. The names of the screens differ slightly between apps and versions, so the steps describe what to look for rather than promising an exact menu.
When a pivot table is the right tool
Reach for a pivot table when you have one row per event (an order, a visit, a ticket) and you want totals, counts or averages grouped by some of its columns. Typical questions: sales per region, tickets per week, hours per project.
Skip it when you only need one number (a single SUM is simpler) or when the question is about individual rows, such as finding one order. A pivot table summarizes. It does not list.
Get the source data into shape first
Pivot tables are fussy about their input, and nearly every beginner problem starts here. Check these before you insert anything.
One header row at the top, with a different, non-empty name over every column.
No blank rows or blank columns inside the data, and no subtotal rows mixed in with the orders.
One kind of thing per column: all dates in the date column, all numbers in the amount column. A number stored as text will be counted instead of added.
A column for every grouping you might want. If you want sales by month, you need a date or month column to group by.
Click once inside the data. Both Excel and Google Sheets will usually guess the full range from there. If you plan to keep adding rows, converting the range to a table in Excel (or giving the pivot a whole-column range in Sheets) means new rows get picked up.
Build it: rows, columns, values and filters
Insert the pivot table: With a cell inside your data selected, use Insert and choose PivotTable (Excel) or Pivot table (Google Sheets). Put it on a new sheet so it never overwrites your data.
Put a field in Rows: Rows are the labels down the left side. Drag Region here and you get one line per region.
Put a field in Columns: Columns are the labels across the top. Drag Month here and each month becomes its own column.
Put a field in Values: This is the number that gets calculated in each cell. Drag Sales here and check it says Sum of Sales, not Count of Sales. Count appears when the field contains text.
Add a filter if you need one: Filters (called Filters in both apps) let you narrow the whole table, for example to one product line, without changing the layout.
Rows, columns, values: that is the whole idea. Each cell in the finished table answers the question, what is the sum of Sales for this row label and this column label?
Worked example: total sales by region and month
Here is a tiny source list. Real ones are longer, but eight rows are enough to check the answer by hand.
Region | Month | Sales |
|---|---|---|
North | Jan | 1200 |
North | Jan | 800 |
North | Feb | 1500 |
South | Jan | 900 |
South | Feb | 600 |
South | Feb | 700 |
South | Jan | 300 |
North | Feb | 500 |
Put Region in Rows, Month in Columns and Sum of Sales in Values. The pivot table adds up every row that shares a region and month.
Region | Jan | Feb | Grand total |
|---|---|---|---|
North | 2000 | 2000 | 4000 |
South | 1200 | 1300 | 2500 |
Grand total | 3200 | 3300 | 6500 |
Check it against the source. North in January is 1200 + 800 = 2000. North in February is 1500 + 500 = 2000. South in January is 900 + 300 = 1200. South in February is 600 + 700 = 1300. The row totals are 2000 + 2000 = 4000 and 1200 + 1300 = 2500, and the grand total is 4000 + 2500 = 6500, which matches adding all eight source rows: 1200 + 800 + 1500 + 900 + 600 + 700 + 300 + 500 = 6500.
Now try a filter. Drag Region into Filters instead of Rows and pick North only. The table shrinks to the North line, and the grand total becomes 4000. You changed the question without touching the data.
Refreshing when the data changes
A pivot table is a snapshot built from the source, and in Excel it does not recalculate when you edit the source. Change one of the eight numbers above and the pivot stays the same until you refresh it. In Excel, right-click inside the pivot table and choose Refresh. Google Sheets pivot tables generally update by themselves when the source range changes.
If you add rows below the original range, a pivot table with a fixed range will ignore them. That is the most common reason a total looks too small. Extend the source range, or use a table or whole-column range as described earlier, then refresh.
Grouping dates: Date columns can usually be grouped by month or year from inside the pivot table. If your dates will not group, they are probably stored as text. Fix the source column first.
Mistakes that make a pivot table lie
Counting when you meant adding. Check the value setting says Sum. A column with one stray text cell often flips to Count.
Blank cells in the region column. Blank labels show up as a blank row or the word (blank), and their sales are easy to miss.
Two spellings of one thing. North and north (with a trailing space) can appear as separate lines. Clean the source with TRIM and consistent capitals.
Forgetting to refresh. If the numbers feel old, they probably are.
Typing over the pivot. Editing cells inside a pivot table does not change the data. Change the source and refresh.
Doing it faster
Once you have built one pivot table, copy it and change the fields rather than starting over. If you are stuck on a particular dialog, DuckyHelper can see your screen when you press the key, so you can ask out loud where a field goes while you keep working. Download it and try it for 10 minutes, no card.
Frequently asked questions
Do I need to know formulas to use a pivot table?
No. The pivot table does the adding for you. Formulas help for checking one cell by hand, as in the example above.
Can a pivot table average instead of add?
Yes. In the value settings you can change Sum to Average, Count, Max or Min. Check the label in the value header to see which one you have.
Get started
