Pivot tables explained, and the formulas that do the same job

Pivot tables have a reputation for being advanced. They are not. A pivot table answers one kind of question, "how much of this, per that?", and it answers it with a few clicks instead of a formula. Total sales per region. Number of orders per customer. Average delivery time per carrier and month.

This guide explains what a pivot table actually computes, how to build one step by step, and the handful of problems that trip people up. It also shows the formulas that produce the same numbers, because knowing them makes you much better at reading pivot tables, and because some jobs, tests and sheets need the formula version.

What a pivot table does

Start with a table where each row is one record: one order, one transaction, one timesheet entry. A pivot table groups those rows by the values in one column and then summarizes another column for each group.

Say you have eight orders with a region, a product and an amount. A pivot table with Region in Rows and Sales in Values gives you one row per region and the total of its sales. Add Product to Columns and you get a grid: regions down the side, products across the top, and a total in every cell. That is all a pivot table is. Everything else is options on top.

Under the hood, every number in that grid is a conditional sum, count or average: "the sum of Sales where Region is North and Product is Shoes". That is exactly what SUMIFS calculates, which is why the formulas later in this guide give the same results.

Getting the data ready

Most pivot table problems are really data problems. Before you insert one, check that your data follows these rules:

• One header row, with a unique, non-empty name in every column. Pivot tables use the headers as field names.
• One record per row. No subtotal rows, no blank rows in the middle and no notes under the data.
• One kind of value per column. The Sales column should contain only numbers. A single "n/a" turns the column into text as far as the pivot table is concerned.
• No merged cells. Unmerge them and fill in the values.

The quickest way to make data pivot-ready is to click inside it and press Ctrl+T to make it an Excel table. A table grows automatically when you add rows, so a pivot table built on it picks up new data when you refresh.

Building a pivot table step by step

Here is the whole process in Excel for Windows and Mac. The menus differ slightly between versions, but the steps are the same.

1. Click any cell in your data. You do not need to select the whole range; Excel finds the edges.
2. Choose Insert, then PivotTable. Excel suggests the range or table and offers to put the pivot table on a new worksheet. Accept both.
3. Look at the PivotTable Fields pane. At the top is a list of your column headers. At the bottom are four boxes: Filters, Columns, Rows and Values.
4. Drag a field to Rows. Drag Region to Rows and you get one row per region.
5. Drag a number field to Values. Drag Sales to Values and Excel adds "Sum of Sales", the total per region, plus a grand total.
6. Optionally, drag a field to Columns. Drag Product to Columns and the single column of totals becomes a grid of regions by products.
7. Optionally, drag a field to Filters. A field in Filters gives you a drop-down above the pivot table to show, say, only one year.

That is it. To change the layout, drag the fields between the boxes; to remove one, drag it out of the pane.

Sum, count, average and more

By default Excel sums number fields and counts text fields. To change that, click the field in the Values box, choose Value Field Settings, and pick Sum, Count, Average, Max, Min or one of the others.

Here is what each of the most common settings computes, written as the formula you would use instead. First, the sum per region:

Count tells you how many rows each group has, regardless of the amounts:

And Average divides the sum by the count:

Two other settings are worth knowing. Show Values As (in the same dialog) turns sums into percentages: % of Grand Total, % of Column Total, % of Row Total, or the difference from the previous month. And you can add the same field to Values twice, once as Sum and once as Count, to see both side by side.

Percent of total

"% of Grand Total" divides each group's sum by the total of everything. In formula form, that is a SUMIF divided by a SUM:

The pivot table version updates when you refresh. The formula version updates as soon as the data changes. Our share of total exercise asks for exactly this formula.

Rows and columns together

When you put one field in Rows and another in Columns, every cell in the grid has two conditions. In formula form that is SUMIFS, with one condition from the row label and one from the column header:

The dollar signs are what make this work. $E2 locks the column but lets the row change as you fill down, so each row reads its own region. F$1 locks the row but lets the column change as you fill right, so each column reads its own product. Write it once in F2, fill it across and down, and you have rebuilt the pivot table with formulas. The cross-table exercise drills exactly this, and it is one of the best exercises there is for understanding mixed references.

Grouping dates

Put a date field in Rows and recent versions of Excel group it into years, quarters and months automatically. If yours does not, or you want a different grouping, right-click a date in the pivot table and choose Group. You can group by days, months, quarters and years, or any combination.

If grouping is grayed out, the date column contains at least one value that is not a real date, usually text that looks like a date. Find it, fix it, refresh.

Numbers can be grouped too. Right-click a number in Rows, choose Group, and set the start, end and interval to get bands such as 0–99, 100–199 and so on. That is handy for order sizes or ages.

Filters and slicers

The Filters box gives you a drop-down above the pivot table. Slicers do the same job with buttons: select the pivot table, choose PivotTable Analyze (or Options), then Insert Slicer, and tick the fields. Slicers are easier for other people to use, and one slicer can control several pivot tables built on the same data, which is the start of a simple dashboard.

You can also filter inside Rows or Columns with the small arrow on the label, for example to show the top ten customers by sales.

Making a pivot table readable

A fresh pivot table is correct but rarely pretty. A few settings make it something you can paste into a report.

Number format. Formatting the cells directly is lost when the pivot table refreshes. Instead, open Value Field Settings, click Number Format, and set it there: thousands separators, no decimals, or a percentage. The format then sticks.

Report layout. On the Design tab, Report Layout has three options. Compact form, the default, puts all row fields in one column with indents. Outline and Tabular form give each row field its own column, which is easier to read and easier to copy into another sheet. Choose Repeat All Item Labels as well if you plan to copy the result somewhere else, so every row has its full labels.

Subtotals and grand totals. The same tab lets you turn subtotals and grand totals off, or move them. With two fields in Rows, subtotals per group can be useful; with one field, they only repeat the grand total.

Sorting. Click the arrow on a row label and choose More Sort Options to sort by the value, such as regions by total sales, largest first. Sorting by the label is the default and is rarely what a reader wants.

Field names. "Sum of Sales" is accurate but clumsy. Click the header in the pivot table and type a new name, such as "Sales". It cannot be exactly the same as the source column name, so "Sales " with a trailing space is a common trick.

Calculated fields

Sometimes the number you need is not in the data but can be calculated from it: margin from sales and cost, or price per unit from revenue and quantity. A calculated field adds a new field to the pivot table with a formula such as =Sales-Cost. Find it under PivotTable Analyze, Fields, Items & Sets, Calculated Field.

Calculated fields work on the sums, not on the individual rows. A margin percentage defined as =Profit/Sales gives the right answer for each group, because it divides the group's total profit by its total sales. A field that multiplies two columns row by row, such as price times quantity, gives the wrong answer, because it multiplies the sums. Calculate that in a new column of the source data instead and refresh.

A worked example from start to finish

Here is how a typical request plays out. Your manager sends a sheet of last quarter's 600 orders and asks: "Which region sold the most, and how did each product do in each region?"

1. Click inside the data and press Ctrl+T, so it is a table. Check that the Sales column is right-aligned all the way down, which means numbers.
2. Insert a pivot table on a new sheet. Drag Region to Rows and Sales to Values. Sort the regions by Sum of Sales, largest first. That answers the first question.
3. Drag Product to Columns. Now each cell is one region's sales of one product. Set the number format to whole numbers with a thousands separator.
4. Add Sales to Values a second time and set Show Values As to % of Row Total. Now you can see each product's share within each region, which is often the more interesting answer.
5. Before sending, check one number by hand: a SUMIFS for one region and one product should give the same total as the pivot table. If it does, send it.

The whole thing takes five minutes, and step 5 is the one that saves you from an embarrassing correction later.

Refreshing, and other things that go wrong

These are the problems people run into most, roughly in order of how often.

The numbers did not change when the data did. Pivot tables are snapshots. After changing the source data, right-click the pivot table and choose Refresh, or press Alt+F5. Refresh All on the Data tab refreshes every pivot table in the workbook.

New rows are missing. If you added rows below the original source range, refreshing is not enough. Choose Change Data Source and extend the range, or better, turn the source into a table with Ctrl+T so it grows on its own.

"Count of Sales" instead of "Sum of Sales". The column has text or blanks somewhere. Fix the data, then change the field setting to Sum. A number stored as text is the usual culprit; it aligns left in the cell.

The same group appears twice. "North" and "North " (with a space) are different values to a pivot table. Clean the source column with TRIM, or Find and Replace, and refresh.

"(blank)" appears as a group. Some rows have an empty cell in that field. Fill them in, or filter (blank) out if they really are missing.

A formula pointing into the pivot table returns strange results. Clicking a pivot table cell while writing a formula inserts a GETPIVOTDATA function rather than a plain reference. It is designed to keep working when the pivot table changes shape. If you want a normal reference, type the cell address instead of clicking, or turn off Generate GetPivotData in the PivotTable options.

Pivot table or formulas?

Both give the same numbers. They are good at different things.

Use a pivot table when:

• You are exploring data and do not know yet which summary you need. Dragging fields around is much faster than rewriting formulas.
• The summary is a one-off, for a meeting or an email.
• You need grouping by month or quarter, or a quick top ten.

Use formulas when:

• The summary must keep a fixed layout, such as a monthly report with the same rows every time.
• The results must update without anyone pressing Refresh.
• Other calculations depend on the results, such as a budget comparison or a commission calculation.
• The summary must combine data in ways a pivot table cannot, such as a ratio of two different sources.

Many good spreadsheets use both: a pivot table to explore and check, and a SUMIFS table for the version that goes in the report. When the two agree, you can trust the numbers. That cross-check is also a good habit in finance work, which our Excel for finance guide covers.

In Excel 365, the UNIQUE function gives you the list of row labels automatically: =UNIQUE(A2:A9) spills the distinct regions down a column, and a SUMIF next to it gives a formula-based summary that grows with the data.

Pivot tables in Google Sheets

Google Sheets has pivot tables too. Select the data and choose Insert, then Pivot table. The editor on the right has the same four areas, called Rows, Columns, Values and Filters, and the same choice of Sum, Count, Average and others under "Summarize by". Two differences: Sheets updates its pivot tables automatically when the data changes, with no Refresh needed, and it can suggest pivot tables for you. Every formula in this guide works in Sheets unchanged.

Pivot tables in Excel tests

Employer tests ask about pivot tables in two ways. Knowledge questions ask which area a field goes in, or how to change Sum to Count. Practical tasks hand you data and ask for a summary, "total sales per region and product", which you can build with a pivot table if the test environment has one, or with SUMIF and SUMIFS if it is a browser simulator without pivot tables. Knowing both routes means you are covered either way.

If you are preparing for a test, practice the formula route until it is quick, because it shows the examiner that you understand what the summary computes. Then make sure you can insert a pivot table, change the summary function and refresh it in your own copy of Excel.

Summary

A pivot table groups the rows of a table by one or two fields and sums, counts or averages another field for each group. Build one with Insert, PivotTable, then drag fields into Rows, Columns, Values and Filters. Keep the source data clean and in a table, refresh after every change, and check the value field setting when a sum turns into a count.

Every number in a pivot table is a conditional sum, count or average, which you can also write as SUMIF, COUNTIF, AVERAGEIF or SUMIFS. The summary tables exercises below build each one of them, starting with a single total per region and ending with a full cross-table.