The PivotTable button is on the Insert tab, at the far left of the ribbon. Click any cell in your data, go to Insert > PivotTable, and Excel builds one on a new sheet. If you’re looking for a pivot table that already exists in a workbook, open View > Navigation in Excel for Microsoft 365 to see every one, or check each sheet tab, including hidden sheets. Both are covered below, along with how to find the field list, the source data and the PivotTable tabs.
- Click any cell inside your data. It should have headings in the first row and no completely blank rows or columns.
- Open the Insert tab.
- Click PivotTable in the Tables group. In Excel for Microsoft 365, choose From Table/Range if a menu appears.
- Check the range, choose New Worksheet and click OK.
Not sure how to lay it out? Insert > Recommended PivotTables, right next to it, shows ready-made layouts based on your data. For a full walkthrough, see how to make a pivot table in Excel.
On a Mac or in Excel for the web
It’s in the same place: Insert > PivotTable. Excel for the web asks whether to put it on a new or existing sheet, then opens the field list on the right.
The PivotTable button is greyed out
- The sheet is protected. Go to Review > Unprotect Sheet.
- You’re editing a cell. Press Esc to leave the cell first.
- Several sheets are grouped. If the title bar says “Group”, right-click a tab and choose Ungroup Sheets.
- The file is in Compatibility Mode or opened read-only. Save it as an .xlsx file with File > Save As.
Find existing pivot tables in a workbook
Use the Navigation pane (Microsoft 365)
Go to View > Navigation. A pane opens on the right with each sheet and the items on it, including tables, charts and PivotTables. Click a PivotTable in the list to jump straight to it.
Check every sheet, including hidden ones
Pivot tables are often placed on their own sheet, which may be hidden. Right-click any sheet tab and choose Unhide to see hidden sheets. If the tabs themselves are missing, see how to show tabs in Excel.
Spot one on a sheet
Click a cell in what looks like a summary table. If it’s a pivot table, two extra tabs appear on the ribbon: PivotTable Analyze and Design (called PivotTable Analyze and Options/Design in some versions). The cell may also show a filter arrow next to row or column labels.
Find the PivotTable Fields list
The field list is where you drag fields into Rows, Columns, Values and Filters. If it has disappeared:
- Click any cell inside the pivot table. The list only shows when a pivot cell is selected.
- Go to PivotTable Analyze > Field List to toggle it on.
- Or right-click inside the pivot and choose Show Field List.
If the list floats in the middle of the screen, drag its title bar to the right edge to dock it again. To change fields, filters or layout once it’s open, see how to edit a pivot table in Excel.
Find the source data of a pivot table
- Click any cell in the pivot table.
- Go to PivotTable Analyze > Change Data Source.
- Excel shows the source range or table name and highlights it. Click Cancel to leave it unchanged, or select a new range and click OK.
To see the rows behind one number, double-click that value. Excel creates a new sheet listing exactly those source rows.
If the numbers look out of date, click PivotTable Analyze > Refresh. See how to refresh a pivot table for refreshing on open and refreshing every pivot at once.
Alternatives to a classic pivot table
| Tool | When to use it |
|---|---|
| Slicers | Clickable filter buttons for a pivot table or an Excel table. See how to insert a slicer without a pivot table. |
PIVOTBY and GROUPBY | Formula versions in Excel for Microsoft 365 that update automatically. See how to use PIVOTBY. |
| PivotChart | A chart linked to a pivot table. Insert > PivotChart. |
Frequently asked questions
Why can’t I see the PivotTable Analyze tab?
It only appears while a cell inside a pivot table is selected. Click inside the pivot and it shows up next to the other ribbon tabs.
Can I move a pivot table to another sheet?
Yes. Click inside it, go to PivotTable Analyze > Move PivotTable, and pick a new location.
How do I delete a pivot table?
Click inside it, go to PivotTable Analyze > Select > Entire PivotTable and press Delete. The source data isn’t affected.
Summary
- Create one: Insert > PivotTable.
- Find existing ones: View > Navigation, or check hidden sheets.
- Field list: click inside the pivot, then PivotTable Analyze > Field List.
- Source data: PivotTable Analyze > Change Data Source.
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.