How to make a pivot table in Excel
To make a pivot table in Excel, click any cell in your data, choose Insert > PivotTable, and drag fields into Rows and Values. Excel then totals the data by category without a single formula. This guide covers the five steps, how to refresh the result, and how to check a pivot total with a formula.
The lessons linked at the end let you practice the same summaries on small sample tables, so you can learn the moves before you touch real data.
1. Prepare the data
A pivot table needs a clean list: one header row with a different name in every column, one record per row, and no blank rows or columns inside the data. Merged cells and subtotals in the middle of the list cause wrong totals. If your numbers are stored as text, fix them first, because a pivot table counts text instead of adding it.
2. Insert the PivotTable
Click any cell in the list and go to Insert > PivotTable (on Windows the keys are Alt, N, V). Excel selects the whole list for you. Choose New Worksheet so the pivot table has room to grow, then click OK. If you are not sure which summary you want, try Insert > Recommended PivotTables, which proposes a few ready-made layouts.
3. Drag the fields
The field list on the right shows your column headers. Drag a text field, such as Region, into Rows. Drag a number field, such as Sales, into Values. Excel adds the numbers up for each region. Drag a second field into Columns to split the totals across the top, or into Filters to pick one slice at a time.
Values are summed by default for numbers. To count rows or take an average instead, click the field in the Values box and choose Value Field Settings.
4. Check the total with a formula
Before you trust any summary, check one number by hand. SUMIFS (or SUMIF with one condition) produces the same total as the pivot table for one category, and a match proves that the data and the layout are right.
Example: North total, as the pivot table shows it
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | North total | |
| 2 | North | 100 | 180 | |
| 3 | South | 80 | ||
| 4 | North | 50 | ||
| 5 | East | 70 | ||
| 6 | South | 20 | ||
| 7 | North | 30 |
100 + 50 + 30 = 180. The pivot table row for North must show the same number.
5. Refresh and group
A pivot table does not update when the source data changes. Right-click the table and choose Refresh, or press Alt+F5. If you add rows below the list, turn the source into an Excel table first (Ctrl+T) so the pivot table sees the new rows. To group dates into months or years, right-click a date in the Rows area and choose Group.
Common mistakes
Blank cells or a blank row in the data. Excel stops at the blank row and leaves the rest out of the pivot table.
Fix: Remove blank rows, give every column a header, and turn the list into an Excel table.
Forgetting to refresh. You change a number in the source, but the pivot table keeps showing the old total.
Fix: Right-click the pivot table and choose Refresh, or press Alt+F5.
Numbers stored as text. The pivot table shows Count instead of Sum, and the totals are wrong.
Fix: Convert the column to real numbers, then refresh the pivot table and set the field back to Sum.
Frequently asked questions
Do I need formulas to make a pivot table?
No. A pivot table summarizes the data from a field list. Formulas such as SUMIFS are useful to check a total or when you need a fixed layout.
Why does my pivot table show Count instead of Sum?
Excel counts a field when it contains text or blank cells. Fix the source so every cell in the column is a number, then refresh.
Can I make a pivot table from more than one sheet?
Yes, but it is easier to combine the data into one table first. Power Query can append or merge sheets and keep the result up to date.
Related functions
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.
More on pivot tables
The guide shows how to build a pivot table and check one total. This piece adds how to read a pivot table before you trust it, how to show percentages instead of sums, and how to keep a formula next to it without breaking it.
Read the result before you trust it
A pivot table always shows a clean grid, so a wrong total looks as believable as a right one. Check three things every time. First, compare the grand total with =SUM(B2:B7) on the source data. Second, look at the label above the numbers: Sum of Sales and Count of Sales look alike but mean different things. Third, scan the row labels for near duplicates such as "North" and "North " with a space, which the pivot table shows as two rows.
=SUMIFS(B2:B7,A2:A7,"North")Show percentages instead of sums
A total is not always what the reader needs. Right-click a number in the Values area, choose Show Values As, and pick % of Grand Total to see each region's share. % of Column Total fits a table where each column is a month, and Running Total in shows how a figure builds up. The pivot table keeps the underlying sum, so you can switch back at any time.
Formulas next to a pivot table
If you click a pivot cell while building a formula, Excel writes a GETPIVOTDATA reference that points at one number by name, not by position. It keeps working when the table is rearranged, but many people find it confusing. To get plain cell references instead, type the address by hand rather than clicking.
A pivot table also grows and shrinks when you refresh it, so never place data right below it. For a fixed layout that updates with the data, build it with SUMIFS and COUNTIFS. To go deeper, read [the conditional sums lesson](/learn/conditional-sums) or [how to count unique values](/guides/count-unique-values-in-excel).