Part of the free Module 10: Pivot Tables · Lesson 19 of 20 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.
PivotTable Options is the dialog that controls how a pivot table behaves: what it shows in empty and error cells, whether column widths reset on refresh, whether old items linger in filters, and whether the report refreshes when the file opens. Right-click any cell in the pivot table and choose PivotTable Options, or go to PivotTable Analyze > PivotTable > Options. This lesson explains every tab and then fixes the ten most common pivot table problems.
How to open the PivotTable Options dialog
- Click any cell inside the pivot table.
- Right-click and choose PivotTable Options, or open the PivotTable Analyze tab (called Analyze in Excel 2016 and 2019) and click PivotTable > Options.
- The dialog opens with six tabs: Layout & Format, Totals & Filters, Display, Printing, Data and Alt Text.
- The Name box at the top renames the pivot table. Give it a meaningful name such as
ptRevenue; slicer connections, VBA and GETPIVOTDATA are easier to read when the report is not called PivotTable7. - Change the settings you need and click OK. Settings apply to this pivot table only, not to every pivot table in the workbook.
Two settings that people search for are not in this dialog. Field-level settings (subtotals, layout, repeat labels, number format) live in Field Settings and Value Field Settings, covered in Value Field Settings and Subtotals and Grand Totals. Workbook-wide defaults live in File > Options > Data > Edit Default Layout (Excel 2019 and 365 only).
Layout and Format tab
This tab decides how cells look after every refresh.
- Merge and centre cells with labels: centres outer row and column labels across their group. Handy for printing, but it breaks copy and paste, so leave it off for working reports.
- When in compact form indent row labels: sets the indent of nested labels in compact layout (default 1 character).
- Display fields in report filter area and Report filter fields per column: arrange several page filters down or across the top of the report.
- For error values show: type
0or a dash and every #DIV/0! from a calculated field is replaced. Tick it; the default is to show the raw error. - For empty cells show: the intersection of Product and Month with no sales shows blank by default. Enter
0so the grid reads as a number table. - Autofit column widths on update: untick this to stop the pivot table resizing your columns every time you refresh. It is the most common formatting complaint and this single box fixes it.
- Preserve cell formatting on update: keeps manual fills, fonts and number formats through a refresh. Keep it on.
Totals and Filters tab
- Show grand totals for rows / for columns: the same switches as Design > Grand Totals.
- Allow multiple filters per field: by default a label filter replaces a value filter on the same field. Tick this to apply, for example, “Product begins with P” and “Revenue greater than 50,000” together. Applying a Top 10 filter and a label filter at the same time needs this option too. Filters are explained in Filters in a Pivot Table.
- Use Custom Lists when sorting: with this on, a field containing Jan, Feb, Mar sorts in calendar order rather than alphabetically. Untick it if a custom list is hijacking a sort you expect to be A to Z.
Display tab
- Show expand/collapse buttons: the plus and minus buttons next to grouped labels. Turn them off for a clean report that will be copied into a slide.
- Show contextual tooltips and Show properties in tooltips: the hover text over values and labels.
- Display field captions and filter drop downs: untick to hide the field header row and its filter arrows. The pivot table still works; users just cannot filter from the grid.
- Classic PivotTable layout: lets you drag fields directly onto the grid, as in Excel 2003. Useful for teaching, rarely for daily work.
- Show items with no data on rows / columns: greyed out for a normal pivot table; available for OLAP and Data Model reports. For a normal report use Field Settings > Layout & Print > Show items with no data instead.
- Field List: Sort A to Z or Sort in data source order: changes the order of names in the PivotTable Fields pane. Sort A to Z helps when the source has 60 columns.
Printing tab
- Print expand/collapse buttons when displayed on PivotTable: usually off.
- Repeat row labels on each printed page: prints the outer label again at the top of every page of a long report.
- Set print titles: uses the pivot table’s header row as the print title for the sheet, so you do not need to set it in Page Layout.
Data tab: the settings that stop most problems
| Setting | Default | What it does | Recommendation |
|---|---|---|---|
| Save source data with file | On | Stores a copy of the data (the pivot cache) inside the workbook | Keep on unless file size matters and the source is always available |
| Enable show details | On | Allows double-click drill-down to the source rows | Turn off on reports shared with people who must not see row-level data |
| Refresh data when opening the file | Off | Refreshes the pivot table each time the workbook opens | Turn on when the source is a Table or external query in the same file |
| Number of items to retain per field | Automatic | How many deleted source items stay in filter drop-downs | Set to None to remove ghost items after the next refresh |
| Enable cell editing in the values area | Off | What-if analysis on Data Model pivot tables | Leave off |
| Disable automatic grouping of Date/Time columns | Off (Excel 2016+) | Stops Excel splitting a date field into Years, Quarters and Months on drop | Turn on if you prefer to group dates yourself |
The Number of items to retain per field setting is the one to remember. When a product is renamed in the source, the old name stays in the filter list until the cache forgets it. Set the option to None, refresh, and the old name disappears. The same trick removes deleted employees from slicers.
Keyboard shortcuts for pivot tables
| Action | Shortcut |
|---|---|
| Insert a pivot table | Alt + N + V (then T for From Table/Range in Excel 365) |
| Refresh the active pivot table | Alt + F5 |
| Refresh every pivot table in the workbook | Ctrl + Alt + F5 |
| Select the whole pivot table | Ctrl + A with a cell inside it, or Ctrl + Shift + * |
| Group the selected items | Alt + Shift + Right arrow |
| Ungroup the selected group | Alt + Shift + Left arrow |
| Hide the selected item | Ctrl + - (minus) |
| Open the PivotTable Analyze ribbon tab | Alt + J + T |
| Open the Design ribbon tab | Alt + J + Y |
| Legacy PivotTable Wizard (multiple ranges) | Alt + D + P |
Worked example: stop column widths and ghost items resetting
Open the practice file Pivot-Table.xlsx and build a pivot table with Product in Rows and Revenue in Values. Widen the Revenue column, then press Alt + F5. The column snaps back to its automatic width. Now rename one product in the source data, say Product A to Product AA, and refresh again. The Product filter drop-down lists both names, although Product A no longer exists.
| Symptom | Setting to change | Result after refresh |
|---|---|---|
| Column width resets | Layout & Format > untick Autofit column widths on update | Your width stays |
| Old product still in the filter | Data > Number of items to retain per field = None | Only current names appear |
| Blank cells in the grid | Layout & Format > For empty cells show = 0 | Zeros instead of blanks |
Make the three changes, click OK, press Alt + F5 once more and check all three symptoms are gone. These three settings are worth changing on every pivot table you plan to keep.
Troubleshooting: pivot table errors and how to fix them
| Error or symptom | Cause | Fix |
|---|---|---|
| The PivotTable field name is not valid | A blank header cell in the source range, or the range includes an empty column | Type a heading in every column of row 1 and re-select the range in Change Data Source |
| Cannot group that selection | The date field contains text, blanks or mixed values | Convert the column to real dates (Text to Columns > Date), remove blanks, refresh, then group |
| Data source reference is not valid | The workbook name contains square brackets, or the source range was deleted or renamed | Rename the file without brackets; point the pivot table at the correct range |
| A PivotTable report cannot overlap another PivotTable report | A refresh would push one pivot table into the cells of another | Leave several blank rows between reports or move one to its own sheet |
| Values show Count instead of Sum | Text or blank cells in the number column | Clean the column, refresh, then switch the field to Sum in Value Field Settings |
| New rows do not appear | The source is a fixed range and the new rows are outside it | Convert the source to a Table (Ctrl + T) or extend the range in Change Data Source |
| Dates split into Years, Quarters and Months automatically | Automatic date grouping (Excel 2016 and later) | Press Ctrl + Z straight after dropping the field, or tick Disable automatic grouping on the Data tab |
| PivotTable Fields pane has disappeared | The pane was closed | Click inside the pivot table and choose PivotTable Analyze > Show > Field List |
| The PivotTable report was saved without the underlying data | Save source data with file was off and the source is not available | Reconnect the source and refresh; then tick Save source data with file |
| Grouping one pivot table changes another | Both reports share the same pivot cache | Create the second report with Alt + D + P and answer No to sharing, or accept it as a feature |
Tips and common mistakes
- Name every pivot table. PivotTable1 to PivotTable9 in one workbook makes slicer connections and VBA unreadable.
- Set the three refresh-proofing options first (Autofit off, empty cells 0, retain None) before you spend time on formatting.
- Do not merge cells on a report that anyone will copy; the pasted block loses its structure.
- Turn off Enable show details on confidential reports. A double-click otherwise reveals every source row, as shown in Show Details.
- Refresh on open is not the same as live data. The workbook still shows old figures until it is opened again or refreshed.
- Options are per pivot table. A new report starts with the defaults; use Edit Default Layout in Excel 2019 or 365 to change the starting point.
- Keep the source clean. Nine of the ten errors above come from the data, not the pivot table.
Practice exercise
- Build a pivot table from the practice file with Supervisor Name in Rows, Product in Columns and Revenue in Values. Rename it
ptSupervisorin PivotTable Options. - Set empty cells to show 0 and turn off Autofit column widths, then widen column A and refresh to confirm the width holds.
- Change one Supervisor Name in the source, refresh, and remove the old name from the filter list using Number of items to retain per field.
- Turn off Enable show details and try double-clicking a value. Turn it back on.
- Tick Allow multiple filters per field, then apply a label filter and a Top 3 value filter on Product at the same time.
Key takeaways
- Right-click any pivot cell and choose PivotTable Options; settings apply to that report only.
- Untick Autofit column widths on update to keep your formatting through a refresh.
- Set Number of items to retain per field to None to clear deleted items from filters and slicers.
- For error values show and For empty cells show turn #DIV/0! and blanks into clean zeros.
- Most pivot table errors come from the source data: blank headers, text dates, total rows or fixed ranges.
- Alt + F5 refreshes one report; Ctrl + Alt + F5 refreshes every report in the workbook.
Related lessons
- Pivot Table course: all 20 lessons
- Refresh a pivot table and change its data source
- Group dates and text in a pivot table
- Conditional formatting and number formats in a pivot table
- Excel shortcut keys course
- IFERROR formula for handling errors outside the pivot table
- Microsoft Support: Keyboard shortcuts in Excel
- Microsoft Support: Refresh PivotTable data
Frequently asked questions
How do I stop a pivot table from changing column width on refresh?
Right-click the pivot table, choose PivotTable Options, open the Layout & Format tab and untick Autofit column widths on update. Leave Preserve cell formatting on update ticked. From then on the widths, fills and number formats you set survive every refresh, including Refresh All.
Why do deleted items still appear in my pivot table filter?
The pivot cache keeps a memory of items that used to exist so that filters can be restored. In PivotTable Options, on the Data tab, set Number of items to retain per field to None and refresh. The old items vanish from the filter drop-down and from any slicer connected to the report.
How do I show zero instead of blank in a pivot table?
Open PivotTable Options, Layout & Format tab, tick For empty cells show and type 0. Excel fills every empty intersection with a zero that follows the field’s number format. For errors from calculated fields, tick For error values show and type 0 or a dash in the same way.
Where is the PivotTable Options button in Excel?
Click inside the pivot table so the PivotTable Analyze tab appears (labelled Analyze in Excel 2016 and 2019), then click PivotTable at the far left and choose Options. The right-click menu on any pivot cell has the same PivotTable Options entry, which is faster.
Can I refresh a pivot table automatically when the file opens?
Yes. In PivotTable Options, Data tab, tick Refresh data when opening the file. Excel refreshes that pivot table each time the workbook opens. It does not refresh while the file is open; use Alt + F5, Ctrl + Alt + F5 or a short VBA Workbook_Open or Worksheet_Change macro for that.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.