A PivotTable summarizes a large list in a few clicks, for example total sales by region, or a count of orders by month. To make one, click anywhere in your data, choose Insert > PivotTable, and click OK. Then, in the PivotTable Fields pane, drag a field such as Region into Rows and a number such as Sales into Values. This guide walks through building one from scratch, changing what it calculates, grouping dates, filtering, and keeping it up to date.
Before you start: check your data
- Every column needs a header in the first row, such as Date, Region, Product, and Sales.
- Each row should be one record, such as one sale.
- There should be no blank rows or columns, and no merged cells or subtotal rows in the data.
- Numbers should be stored as numbers and dates as dates, not text.
It helps to turn the data into a table first (click inside it and press Ctrl + T). A PivotTable built from a table picks up new rows automatically when you refresh it.
How to create a PivotTable
- Click any cell inside your data.
- Go to Insert > PivotTable. In some versions, choose From Table/Range from the drop-down.
- Check that the range or table name is correct.
- Choose New Worksheet (recommended) or Existing Worksheet to put it next to your data.
- Click OK. An empty PivotTable appears with the PivotTable Fields pane on the right.
Not sure where to start? Insert > Recommended PivotTables shows several ready-made summaries of your data to pick from.
Build the summary
The Fields pane lists every column header. Tick a field or drag it into one of the four areas:
- Rows: each unique value becomes a row. Drag Region here to get one row per region.
- Values: the numbers to calculate. Drag Sales here and Excel totals it for each region.
- Columns: splits the values across the top. Drag Product here to see each product’s sales by region in a grid.
- Filters: adds a drop-down above the table so you can show one value at a time, such as a single sales rep.
To remove a field, untick it or drag it out of the area. To rearrange, drag fields between areas. The PivotTable updates instantly, and your original data never changes.
Change what the PivotTable calculates
Excel sums number fields and counts text fields by default. To change that:
- Right-click any number in the Values area of the PivotTable.
- Choose Summarize Values By and pick Sum, Count, Average, Max, Min, or More Options.
To show each region’s share of the total, right-click a value, choose Show Values As, and pick % of Grand Total. To format the numbers as currency, right-click a value, choose Value Field Settings, then Number Format.
Group dates by month, quarter, or year
Drag a date field into Rows. Recent versions of Excel group dates automatically into years, quarters, and months. To control it yourself, right-click any date in the PivotTable, choose Group, select Months and Years (or whatever you need), and click OK.
Sort and filter
- Sort: right-click a value and choose Sort > Sort Largest to Smallest to rank regions by sales.
- Filter rows: click the arrow next to Row Labels to tick only the items you want, or use Value Filters > Top 10.
- Slicers: click the PivotTable, then PivotTable Analyze > Insert Slicer. Slicers are clickable buttons that filter the table, which is handy for reports. Slicers also work on ordinary tables; see how to insert a slicer in Excel without a PivotTable.
Keep it up to date
A PivotTable doesn’t update by itself when the source data changes. Right-click inside it and choose Refresh, or press Alt + F5. To refresh every PivotTable in the workbook, use Data > Refresh All. If you added rows outside the original range and your data isn’t a table, use PivotTable Analyze > Change Data Source to include them. See how to refresh a pivot table in Excel.
Change the layout and design
- Design > Report Layout > Show in Tabular Form puts each row field in its own column, which is easier to read and to copy elsewhere.
- Design > Grand Totals and Subtotals turn totals on or off.
- Design > PivotTable Styles changes the colors.
- PivotTable Analyze > PivotChart makes a chart that follows the PivotTable’s filters.
For more changes, see how to edit a pivot table in Excel.
Troubleshooting
“The PivotTable field name is not valid”
One of your columns has no header. Give every column in the range a name, or select only the columns that have headers.
My numbers show as Count instead of Sum
The column contains text or blanks, so Excel treats it as text. Fix the data, then switch it to Sum with Summarize Values By.
I can’t find the Fields pane
Click inside the PivotTable. If it still doesn’t appear, go to PivotTable Analyze > Field List. If you’ve lost track of where the PivotTable is, see how to find a pivot table in Excel.
Can’t change the data because the PivotTable is in the way
PivotTables can’t overlap other data. Move it with PivotTable Analyze > Move PivotTable.
A formula alternative
In Excel for Microsoft 365, the GROUPBY and PIVOTBY functions build the same kind of summary with a formula that updates automatically, with no Refresh needed.
Frequently asked questions
Does a PivotTable change my original data?
No. It reads your data and builds a summary somewhere else.
Can I make a PivotTable from several sheets?
Yes. Turn each range into a table, then tick Add this data to the Data Model when you insert the PivotTable, and create relationships between the tables under Data > Relationships.
How do I delete a PivotTable?
Click inside it, go to PivotTable Analyze > Select > Entire PivotTable, and press Delete.
Summary
Click inside your data, choose Insert > PivotTable, and drag fields into Rows and Values. Use Summarize Values By to change the calculation, Group for dates, and Refresh whenever the data changes.
Related: How to Categorize Data in Excel (Drop-Downs, Formulas and Summaries).
Related: how to unhide all rows and columns in Excel.
Related: count the number of occurrences in Excel.
Related: convert a date to a month in Excel.
Related: how to create a word cloud in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.