To create a pivot table in Excel, click any cell in your list, go to Insert > PivotTable > From Table/Range, check the range Excel picked and click OK. Excel opens an empty PivotTable on a new sheet by default, with the PivotTable Fields pane beside it. Tick a category such as Region, which goes into Rows, and a number column such as Amount, which goes into Values as a sum.
On our six-row sales list in Excel for Microsoft 365 on Windows 11, version 25H2, that gave East 35, West 50 and a Grand Total of 85. The empty PivotTable you see after OK is normal, because the report only takes shape when you add fields. Most real problems start in the source list, and the fixes below show what Excel did when we gave it a blank header, a merged header and text in a number column.
The short version
- Prepare the list: one header row with a different name in every column, and no blank rows or merged headers.
- Create it: select the list, then Insert > PivotTable > From Table/Range and click OK.
- From the keyboard: press Alt, N, V, T one after another, then Enter.
- Fill it in: tick a category for Rows and a number column for Values in PivotTable Fields.
- Keep it current: select a PivotTable cell and press Alt + F5, or right-click and choose Refresh, after the list changes.
Ways to make a PivotTable in Excel
| Route | Use it when | What you get |
|---|---|---|
| Insert > PivotTable > From Table/Range | You have one clean list or Excel table | An empty PivotTable and the field pane, on a new sheet by default |
| Alt, N, V, T | You prefer the keyboard in Excel for Windows | The same dialog, with your range filled in |
| Insert > Recommended PivotTables | You want Excel to suggest a layout | A filled-in PivotTable on a new sheet |
| Data > Analyze Data | You want suggestions in a side pane | Insight cards, some with an Insert PivotTable button |
| Insert > PivotTable in Excel for the web | You work in a browser | A pane of cards, each with New sheet and Existing sheet buttons |
| Insert > PivotTable > From Data Model | You have several related tables | Fields from several tables, with relationships between them where needed |
Whichever route you take, the PivotTable reads your list and builds its summary somewhere else. Microsoft says your actual data doesn't get altered in any way, and our six source rows stayed exactly as typed after each PivotTable we built from them.
Get your list ready before you start
Give every column one header in a single row, with a different, non-blank name for each, and keep one record per row with no blank rows or columns in the middle. Microsoft also says to avoid double rows of headers or merged cells, and the fixes further down show why.
Our practice list sits on a sheet named Sales, with Region, Product, Amount and Units in A1:D1 and six sales in A2:D7. Region and Product are categories to group by, while Amount and Units are numbers to add up. If rows, columns and worksheet tabs are still new to you, start with our Excel basics for beginners before building the summary.
A PivotTable groups identical entries, so a typo such as Est next to East becomes a row of its own, and a drop-down list in the column stops that at the source. If a record was pasted in twice, remove duplicate rows in Excel first, because the PivotTable adds it twice.
On a long list, freeze the header row so the column names stay in view while you check the records. Before you restructure a workbook other people rely on, make a copy of your Excel file first.
Create a PivotTable in Excel on Windows
Select your list, or click a cell inside it, then open the Insert tab. We selected A1:D7, and Excel carried that range into the next dialog. The PivotTable button at the left of the ribbon is a split button.
Its upper half opens the creation dialog straight away. On our PC the arrow under it opened a short menu with From Table/Range, From External Data Source and From Data Model. From Data Model was grey, because the workbook had no Data Model yet.
Choose From Table/Range. In Excel for Microsoft 365 the dialog is titled PivotTable from table or range, although older help pages still call it Create PivotTable. Check the Table/Range box first, since it decides which rows get counted. Ours read Sales!$A$1:$D$7, the whole list.
Under Choose where you want the PivotTable to be placed, New Worksheet is selected by default, and Existing Worksheet unlocks the Location box for a cell on a sheet you already have. Leave Add this data to the Data Model unticked for a single list. Then click OK.
Excel added a new sheet with a grey box named PivotTable1 that said To build a report, choose fields from the PivotTable Field List. The PivotTable Fields pane opened on the right, and two extra ribbon tabs appeared, PivotTable Analyze and Design. A PivotTable made this way starts empty, while Recommended PivotTables, covered below, inserts one already filled in.
If the Table/Range box shows the wrong range, type the right one over it before you click OK. We selected only A1:C4 on purpose, and the dialog showed Sales!$A$1:$C$4, which would have left out the Units column and three of the six records. Typing Sales!$A$1:$D$7 over it fixed the source, and the finished PivotTable counted all six records for the full 85.
Add fields to Rows, Columns, Values and Filters
The top of the PivotTable Fields pane lists your column headers as fields, and the bottom holds four areas. Rows lists each item down the side, Columns lists items across the top, Values holds the numbers being summarized, and Filters limits the whole report to the items you pick. You can tick a field or drag it straight into an area.
When you tick a field, Excel chooses the area for you. Microsoft's page says that by default non-numeric fields are added to Rows, date and time fields to Columns, and numeric fields to Values. We ticked Region and then Amount, and Excel put Region in Rows and Amount in Values as Sum of Amount, giving East 35, West 50 and a Grand Total of 85.
The header cell read Row Labels rather than Region, which is how a PivotTable made from the Insert tab labels its rows by default. To check a total, add the source numbers yourself. East is 10 plus 20 plus 5, which makes 35.
To split each total by a second category, drag that field into Columns. For this one we chose Existing Worksheet and typed Sales!$F$2 in Location so the summary sat beside its source, then ticked Region and Amount and dragged Product into Columns. The 85 split into Pad 45 and Pen 40, with East at 20 and 15 and West at 25 and 25.
Use an Excel table so new rows are included
A plain range such as A1:D7 stays that size, so records added below it can be left out. An Excel table grows as you add rows, and Microsoft says rows added to a table are included automatically in the PivotTable when you refresh the data. That makes a table the better source for a list you keep adding to.
Select the list and choose Insert > Table. Our Create Table dialog showed the range, the name Table1 under What is the name of your table?, and a ticked My table has headers box. After OK, the list took on banded rows and filter buttons.
With a cell inside the table selected, we pressed Alt, N, V, T one after another, the keyboard route Microsoft gives for Excel on Windows. The same PivotTable dialog opened with Table1 in the Table/Range box instead of cell addresses. Pressing Enter accepted it, and Region plus Amount gave the same 35, 50 and 85.
When you insert a row in Excel inside the table for a new record, check that the record is part of the table, then refresh the PivotTable so it picks it up. Our guide to Excel keyboard shortcuts worth learning explains the KeyTips letters that appear when you press Alt, which is how sequences like Alt, N, V, T work.
Let Excel suggest a PivotTable
If you are not sure which fields to use, select a cell in the list and choose Insert > Recommended PivotTables. Our dialog listed four suggestions on the left, starting with Sum of Amount by Region and Product, and previewed the selected one on the right. A Blank PivotTable button and a Change Source Data link sat along the bottom.
Clicking OK inserted the suggestion on a new sheet, already filled in, with Region down the side, Product across the top and the same 85 total. Microsoft's PivotTable page notes that PivotTable Recommendations use the connected experience in Microsoft 365, and that you cannot use them if you opt out of it.
Analyze Data can build a PivotTable too, but it was not where Microsoft's help page puts it. That page says to select the Analyze Data button on the Home tab, while our Home tab had no such button. It sat on the Data tab as an unlabelled icon in the Data Tools group, and a tip in Excel said Analyze Data is now found in the Data tab.
Its pane offered a Discover insights card showing Amount by Region and Product, with an Insert PivotTable button that added it on a new sheet. When we typed total Amount by Region into the question box instead, the answer was a bar chart with only an Insert PivotChart button.
If a chart is what you need, you can make a graph in Excel from the list itself. The PivotTable from Analyze Data also headed its rows Region rather than Row Labels, so it can look slightly different from one made on the Insert tab.
Create a PivotTable in Excel for the web
Select the list and choose Insert > PivotTable, and the Insert PivotTable pane opens on the right with a Source box at the top. Ours already read Sheet1!A1:D7, and Microsoft says you can change it by selecting data on the grid or typing a range in the box.
Below the Source box sit cards. The first is Create your own PivotTable, and the ones under it are recommended layouts such as Amount by Region and Product. Each card has its own New sheet and Existing sheet buttons, and neither was preselected, so click New sheet on the card you want, or click Existing sheet, give a location and click Insert.
We clicked New sheet on the Create your own card. Excel for the web added a sheet with the PivotTable Fields pane already open and a single PivotTable tab on the ribbon, and ticking Region and Amount gave East 35, West 50 and 85. The web PivotTable started in A2 and headed its rows Region, where the desktop one started in A3 with Row Labels.
Existing sheet asks where to put the PivotTable, with the prompt Select where the PivotTable should be placed by entering a location below or using your mouse to select a location on the grid. We typed Sheet1!F2 for the recommended card and clicked Insert, and the filled-in PivotTable appeared at F2. Microsoft says recommended PivotTables are available only to Microsoft 365 subscribers.
Create a PivotTable in Excel on a Mac or iPad
Excel for Mac follows the same route in Microsoft's steps. Select a cell in the source data or table range and go to Insert > PivotTable, and Excel shows the Create PivotTable dialog with your range or table name selected. Choose New Worksheet or Existing Worksheet, click OK, then add fields from the field list.
On an iPad, Microsoft's steps are close to the web ones: select a cell in the source data or table range and go to Insert > PivotTable. Choose Insert on new sheet, or type the cell where it should go in the Destination field, then tap Insert. Microsoft says PivotTable on iPad needs Excel on iPad version 2.82.205.0 or later, so update the app from the App Store if the button is missing.
Refresh a PivotTable after the data changes
On our PC a PivotTable kept showing the old data until we refreshed it. Right-click anywhere inside it and choose Refresh, or select a PivotTable cell and press Alt + F5, which is what we used in the repairs below. With several PivotTables, Microsoft's route is the arrow under PivotTable Analyze > Refresh, then Refresh All.
Microsoft says newer versions of Excel can refresh PivotTables built on workbook data automatically, but that Auto Refresh is currently available only to participants of the Microsoft 365 Insider program. Where you have it, Microsoft puts the switch on the PivotTable Analyze tab and in PivotTable Options.
Refresh only rereads the range the PivotTable already uses. If you added records below a plain range, select a PivotTable cell and use PivotTable Analyze > Change Data Source to widen it. If you have since turned the list into an Excel table, use the same button and type the table's name, such as Table1, in the Table/Range box, which is how Microsoft says to switch a PivotTable to a different Excel table.
Refresh also matters when you build a second PivotTable from the same range. In our blank-header test, a new PivotTable made after we had fixed the header still showed the old field name until we refreshed it.
Fix Field2, Field3 or a missing field name
Excel did not refuse a bad header on our PC. We cleared the Amount header in C1 and created a PivotTable from A1:D7, and no error message appeared. The field list showed Field3 where Amount should be, so the report still worked but under a meaningless name.
Merged headers fail in the same quiet way. When we merged A1:B1, Excel warned Merging cells only keeps the upper-left value and discards other values, and the Product header was gone after OK. The next PivotTable listed Region, Field2, Amount and Units.
The fix belongs in the source, not the PivotTable. Type the missing header back, or unmerge cells in Excel and retype each header, then select a PivotTable cell and refresh. After we unmerged A1:B1, typed Product into B1 and pressed Alt + F5, the field list read Region, Product, Amount and Units again.
Fix a PivotTable that counts instead of adding
If the Values area says Count of where you expected Sum of, check the number column for text first, then the field's Value Field Settings. Microsoft puts it this way: if Excel interprets your data as text, the data is displayed as a COUNT. One cell is enough, and we typed unknown over a single Amount value to see what happens.
Ticking Amount did not even reach Values. Excel treated the column as a category and put it in Rows, listing each amount, unknown included, under East and West. When we dragged Amount from Rows to Values, the report showed Count of Amount with 3, 3 and a total of 6, the number of records rather than their sum.
We typed 15 back into it and refreshed, and the PivotTable still said Count of Amount. On our PC the field kept its Count setting until we changed it by hand.
Open the arrow on the field in the Values area and choose Value Field Settings. On the Summarize Values By tab, pick Sum instead of Count and click OK. Ours then showed Sum of Amount with East 35, West 50 and 85.
The same list offers Average, Max, Min and Count Numbers, which is how you change what a PivotTable calculates on purpose. For the spread of a column, calculate the standard deviation in Excel with a formula instead. If numbers stored as text are throwing off ordinary formulas too, our guide to SUM formulas that are not working covers the usual causes.
When the PivotTable button is grey or Excel asks to overwrite
With the default protection settings, a protected sheet greys out the PivotTable button and gives no message. We protected our list with Review > Protect Sheet, leaving the permissions as they were, and on the Insert tab, PivotTable, Recommended PivotTables and Table all turned grey. Clicking PivotTable did nothing at all.
The Protect Sheet dialog has a permission named Use PivotTable and PivotChart, which Microsoft's protection page calls Use PivotTable reports. By default only Select locked cells and Select unlocked cells were ticked. Choosing Review > Unprotect Sheet brought the buttons back, and the PivotTable worked straight away.
If the sheet has a password you do not know, ask whoever set it, then unprotect an Excel worksheet you are allowed to edit. If you protect sheets to lock cells in Excel, unprotect the sheet before you create a PivotTable from its data, since that is what brought the buttons back on our PC.
Placing a PivotTable on cells that already hold data brings a question rather than an error. We typed Reserved in F2, chose Existing Worksheet at Sales!$F$2, and Excel asked Inserting your PivotTable here will result in overwrite of existing data in the sheet "Sales". Do you want to replace the data?
Yes writes over those cells, so choose it only if you mean to. Before you place a PivotTable on a sheet that already holds data, save the workbook, and if this question appears, answer No and choose an empty cell for Location instead. With the Location changed to Sales!$F$15, the PivotTable went in below the source and Reserved stayed untouched in F2.
Get the PivotTable Fields pane back
The PivotTable Fields pane and the PivotTable Analyze and Design tabs only show while a cell inside the PivotTable is selected. When we clicked a cell outside it, all three disappeared, and that is often all that has happened when the pane seems to be gone.
If you closed the pane with its X, click inside the PivotTable and choose PivotTable Analyze > Field List, in the Show group at the right of the ribbon. On our sheet that brought the pane back with all four areas.
PivotTables from several tables or a database
From Data Model is for several related tables at once. Microsoft says to use it if your workbook contains a Data Model and you want a PivotTable from multiple tables, custom measures or very large datasets, and that sometimes you need to create a relationship between two tables first. It also notes that Data Models are not supported on Excel for Mac.
For records that share the same columns but sit on separate sheets, combining them into one table first is usually simpler than a Data Model. From External Data Source uses an existing external data connection, and Microsoft's steps for it go through Choose Connection.
How we tested this guide
We tested this in Excel for Microsoft 365 on Windows 11, version 25H2, and in Excel for the web in Microsoft Edge, building each PivotTable from a made-up sales list.
Frequently Asked Questions
What is the fastest way to create a pivot table in Excel?
Click a cell in your list, press Alt, N, V, T one after another and press Enter to accept the range and a new sheet. Then tick a category and a number column in the PivotTable Fields pane.
Why is my PivotTable blank?
A PivotTable made with Insert > PivotTable stays empty until you add fields. Tick a category for Rows and a number column for Values in the PivotTable Fields pane, and the report fills in.
Why does my PivotTable count instead of sum?
Check the number column for text first, since one text cell was enough on our sheet. Fix it and refresh, then open Value Field Settings, where Count may still be selected, and change it to Sum.
Why do new rows not show up in my PivotTable?
Refresh first. If the source is a plain range, widen it with PivotTable Analyze > Change Data Source, or point it at an Excel table by typing the table's name in the Table/Range box there, since Microsoft says rows added to a table are included when you refresh.
Where did the PivotTable Fields pane go?
It only shows while a cell inside the PivotTable is selected. If you closed it, click inside the PivotTable and choose PivotTable Analyze > Field List.
Can I create a PivotTable in Excel for the web?
Yes. Insert > PivotTable opens a pane where you pick New sheet or Existing sheet on a card, and ours gave the same totals as the desktop app.
How do I delete a PivotTable?
Microsoft says to select the entire PivotTable range and press Delete. If the PivotTable sits on its own sheet with nothing else you need, deleting that sheet removes it too.