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. Exports from online services often come as JSON, and once you load a JSON file into Excel with Power Query, you can send the results straight to a PivotTable.
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: That automatic text count is also a quick way to rank the words in survey comments before you turn them into a word cloud in Excel.
- 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. To picture those shares as slices, you can make a pie chart in Excel with percentages.
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. If you’d rather have a month column in the source data itself, see how to convert a date to a month in Excel.
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. You can also insert a slicer without a PivotTable on an ordinary table.
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. Automatic refresh options are covered in refreshing a pivot table in Excel. Each PivotTable also keeps a copy of its source data inside the file, so if a big workbook starts to balloon, see ways to make an Excel file smaller.
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.
Renaming fields, changing the source and other edits are in editing a pivot table in Excel.
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.
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. Lost track of where the PivotTable is? Here’s 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.
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.
For quick summaries without a PivotTable, you can count the number of occurrences with a formula, or categorize your data first so the groups are clean.

Matt Jacobs writes and maintains the guides on Support Your Tech, which he started in 2017. He spent most of his career keeping small-business PCs running and still takes the occasional consulting job, so the steps here come from fixing the same problems on other people’s computers. Every guide is checked on his own Windows 11, Windows 10, Mac, iPhone and iPad before it goes up.