To expand a PivotTable field across all its items in Excel for Microsoft 365 on Windows, right-click an item in that field and choose Expand/Collapse > Expand Entire Field. If the PivotTable has several nested fields and you want to show every level, repeat the command for each level; expanding one field does not necessarily open every deeper level at once.
What “expand all” means in a PivotTable
A PivotTable can arrange fields in a hierarchy. For example, putting Year, Quarter, and Month in the Rows area nests quarters under years and months under quarters. The plus and minus controls beside labels let you open or close parts of this hierarchy.
Excel’s Expand Entire Field command applies to a field, not necessarily to every level of the whole report. If you expand the Year field, Excel can display the quarter items beneath each year. You may then need to expand the Quarter field to display months. This distinction explains why a PivotTable may still show collapsed groups after you use a command that sounds like “expand all.”
The exact result depends on the fields in the Rows and Columns areas and on how the PivotTable’s source data or data model is structured. A report with only one row field has no deeper row level to reveal. A report with several nested fields can be opened one level at a time.
Expand an entire field on Windows
Use the context menu when you want to reveal the next level for all items in a field, rather than clicking every plus sign separately:
- Click a cell inside the PivotTable so the PivotTable tabs appear.
- Find an item label in the field you want to expand. For example, click a year label if the next level is quarters.
- Right-click the label, then choose Expand/Collapse > Expand Entire Field.
- Check the PivotTable. If another nested level remains collapsed, right-click an item in that next field and repeat the command.
For instance, imagine a report with years and quarters in Rows. Expanding the Year field displays the quarters under each year. If months are also in Rows, expanding the Quarter field displays the months beneath each quarter. The same approach works with other field combinations, such as region and sales representative, provided those fields are nested in that order.
Some Excel builds also offer a ribbon command for the active field. Select a label in the field, then look on PivotTable Analyze > Active Field for Expand Field. Ribbon layout and available commands can vary with the window size and Excel update, so the right-click menu is a useful route when you cannot locate the button.
Set up the hierarchy before expanding
Excel expands according to the order of fields in the PivotTable, not merely according to the way columns happen to be arranged in the source worksheet. In the PivotTable Fields pane, fields in the Rows area form nested row levels from top to bottom. Fields in the Columns area form nested column levels. Dragging a field higher or lower changes the hierarchy and therefore changes what an expansion reveals.
Suppose a source table has columns for Country, City, and Product. If Country and City are both placed in Rows, expanding Country reveals cities under each country. If Product is placed below City, expanding City can reveal products at the next level. If Product is instead placed in Columns, it will not appear as a nested row level.
Date fields can have an additional wrinkle. Excel may group dates into years, quarters, or months, so a date hierarchy can contain generated group fields as well as the original date field. If the levels shown do not match what you expected, inspect the Rows and Columns areas and the labels in the report before repeating the expand command.
Expand only selected items or use the ribbon
You do not have to expand every item in a field. To open one branch, click its plus sign or right-click that item and choose Expand/Collapse > Expand, if that command is available in the context menu. This is helpful when a report contains many categories but you only need to inspect one, such as a particular region or year.
For a field-wide expansion, choose Expand Entire Field from the context menu or use PivotTable Analyze > Active Field > Expand Field when that button is present. The ribbon action works on the field containing the active cell; it is not a general command to recursively open every field in the PivotTable.
To reverse an expansion, use the corresponding collapse command on a label in the relevant field. Collapse Entire Field closes that level across its items, while a per-item collapse closes only the selected branch. These commands change the displayed layout; they do not delete records from the source data.
Show or hide the plus and minus buttons
The plus and minus icons are controls, not an “expand all” setting. If they are missing, click inside the PivotTable and check PivotTable Analyze > Show > +/- Buttons. Turning this option on displays the controls; it does not automatically expand any groups. Turning it off hides the controls but does not, by itself, collapse the report.
You can still use the right-click Expand/Collapse commands when the buttons are hidden. If you cannot see the PivotTable Analyze tab, first click inside the PivotTable. If the command is still hard to find, widen the Excel window or check for a collapsed ribbon group; a narrow window can hide some labels behind compact menus.
Common reasons expansion does not work as expected
- The wrong field is selected. Expanding a field reveals its next nested level. Click a label in the parent field you actually want to open, then check which field sits below it in the Rows or Columns area.
- There is no lower level. A field at the bottom of the hierarchy has no child field to display. The expand command may be unavailable or make no visible difference.
- A filter limits the items. Expansion does not remove report filters, slicer selections, or hidden-item choices. If expected categories are absent, inspect the field filter and any connected slicers as well as the expansion state.
- The field order is different from what you assumed. Reorder fields in the Rows or Columns area if you want a different parent-child arrangement, then expand the appropriate field.
- The PivotTable uses a data model or external source. Some available hierarchy commands depend on the source and the way its fields are defined. If a command is missing, check whether the source provides a hierarchy and whether the selected cell belongs to the field you intend to expand.
- The report is being confused with source records. Expanding a group reveals another PivotTable level, not necessarily every underlying row. To inspect records behind a summarized value, double-click that value cell. Excel can create a separate worksheet with the underlying records, subject to the source and workbook settings.
Also distinguish expansion from refreshing. Refreshing updates PivotTable results from the source; it is not the command for opening groups. If the report changes after a refresh, first verify the source data, filters, field layout, and current expansion state rather than assuming the refresh itself means “expand all.”
Excel for Mac and Excel for the web
In Excel for Mac, click inside the PivotTable and look for the expand or collapse controls and commands on the contextual PivotTable ribbon or in the right-click menu. The precise ribbon arrangement can differ from Windows, but the important distinction remains: expand the selected item for one branch, or use the field-wide command to open that level across the field. If a particular ribbon button is not visible, use the context menu where available.
Excel for the web also provides PivotTable interaction, but some commands and layouts can differ from the desktop application and may depend on the workbook and current web version. If you cannot find Expand Entire Field in the browser, open the workbook in the desktop app and use the Windows steps above. Menu availability can change, so rely on the command’s wording rather than assuming that showing the plus/minus buttons expands the report.
Frequently Asked Questions
Can I expand every level of an Excel PivotTable with one click?
Excel’s standard field command expands the selected field level across its items; it does not always recursively open every nested level. Repeat Expand Entire Field for each deeper field that you want to show.
Why does Expand Entire Field reveal only one level?
The command opens the next level below the selected field. If the hierarchy is Year, Quarter, and Month, expanding Year can reveal Quarter, while Month remains nested until you expand Quarter.
How do I expand just one group?
Click the plus sign beside that label, or use the item-level Expand command from its context menu. This opens that branch without expanding every item in the field.
Does expanding a PivotTable show all the original data rows?
Not necessarily. It reveals nested PivotTable fields. To see the source records behind a summarized value, double-click the value cell; Excel may display those records on a new worksheet.
Can I expand a PivotTable when the plus signs are hidden?
Yes. The +/- Buttons setting controls whether the icons appear. Use the label’s right-click Expand/Collapse menu, or turn on PivotTable Analyze > Show > +/- Buttons to make the controls visible again.
Choosing the right expansion method
Use Expand Entire Field when you need a complete view of the next level across the report, such as all quarters under all years. Use an individual plus sign when you want to inspect one category without making a large PivotTable longer. If the real goal is to view transactions rather than another summary level, double-click the relevant value instead. Choosing among these actions keeps the report readable and avoids mistaking a display change for a change to the underlying data.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram: How to Show PercentagesExcel’s built-in Histogram chart shows frequency—the number of observations in each bin—not a normalized percentage option. To show…
- Google Sheets Data Chart: Create, Customize, and Fix ItTo make a data chart in Google Sheets, select the cells you want to visualize, then choose Insert…
- Excel Chart Legend Icons: How to Make Symbols BiggerYes—Excel can make chart legend icons look bigger, but it does not provide a separate control for setting…
- Sparklines in Microsoft Excel 365: Create and Format ThemYes. Microsoft Excel for Microsoft 365 includes sparklines: tiny charts placed inside worksheet cells to show trends in…
- Excel Chart in PowerPoint: How to Copy and Link ItTo put an Excel chart in PowerPoint, select the chart in Excel, copy it, then use PowerPoint’s paste…
- View Pivot Table Fields in Google SheetsTo view pivot table fields in Google Sheets, click any cell inside the pivot table; the Pivot table…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.