To collapse the detail in an Excel PivotTable, right-click an item in the row or column field you want to hide, then choose Expand/Collapse > Collapse Entire Field. This collapses that field throughout the PivotTable; Excel does not provide one universal command that collapses every field in every layout at once.
What “collapse all” means in a PivotTable
A PivotTable can arrange several fields in a hierarchy. For example, a sales report might show Region, then City, then Store in the Rows area. Expanding a Region reveals its cities; expanding a City reveals its stores. Collapsing hides lower-level detail while leaving the higher-level summary visible.
“Collapse all” can mean different things depending on the layout. You might want to hide every City and Store and show only Regions, or hide Stores while leaving both Regions and Cities visible. The right command depends on which level you want to retain. Collapsing a field changes the report’s display, not the source records or the underlying totals.
There is no single global collapse control that automatically handles every row and column field in every PivotTable. The context-menu command works on a chosen field. In a nested report, collapsing a higher-level field can hide all the lower-level detail beneath it; collapsing a lower-level field leaves the higher-level headings in place.
Collapse an entire field
Use Collapse Entire Field when you want to hide the detail for a particular level throughout the report, rather than closing just one group. For a Region > City > Store report, collapsing the Region field can leave Regions visible and hide the nested City and Store rows.
- Click inside the PivotTable and find a visible label belonging to the field you want to collapse. Use a row or column label, not a number in the Values area.
- Right-click that label.
- Choose Expand/Collapse, then select Collapse Entire Field.
- Check the result. If the report still shows a level you meant to hide, collapse the appropriate higher-level field as well.
The command applies to the selected field, not just the one label you clicked. For example, if you right-click one City and choose Collapse Entire Field, Excel collapses the City level across the PivotTable’s groups. This is useful when a report contains many regions and you do not want to close each city group individually.
Collapse just one group
If you want to hide details under one item but leave other items expanded, use the item-specific collapse command instead. Right-click the particular group label and choose Expand/Collapse > Collapse. In a report grouped by Region, this can close the cities under North while leaving South expanded.
Some PivotTables display a small minus button beside an expanded label. Clicking that minus collapses the group beside it; clicking the plus button expands it again. This is a quick way to adjust one part of a report without opening the context menu. If the buttons are missing, check the display option described below.
Be careful not to confuse an item-specific collapse with a whole-field collapse. The first changes one group, which is handy for exploring a report. The second changes the same hierarchy level across the field, which is better when preparing a compact view for presentation or printing.
Collapse using the PivotTable ribbon
Excel also provides expand and collapse controls on the contextual PivotTable ribbon. Select a PivotTable item, open PivotTable Analyze, and look for the Active Field controls. Depending on the selected field and the ribbon layout, Excel offers commands such as Collapse Field or Expand Field. The right-click menu is often the clearest route because it explicitly offers Collapse Entire Field and item-level choices.
To show or hide the plus and minus buttons, click inside the PivotTable and use PivotTable Analyze > Show > +/- Buttons. The buttons are a display aid: hiding them does not expand or collapse the report, and showing them does not change the data. If you cannot find a ribbon command, widen the Excel window or inspect the contextual ribbon tabs, which can rearrange or condense controls when space is limited.
Choose the right level in a nested report
With several fields in the Rows or Columns area, think of the field list as a set of nested levels. The top field is the broadest grouping; fields beneath it provide more detail. To display only the broadest groups, collapse at that higher level. To keep some detail, collapse a lower-level field instead.
For example, suppose a PivotTable has Year, Quarter, and Month in the Rows area, in that order. If you want to show annual totals without quarters or months, collapse the Year level. If you want annual and quarterly totals but not individual months, collapse the Quarter level. The same principle applies to columns, such as Product Category > Product Name.
Before collapsing, check the order of fields in the PivotTable Fields pane. If the hierarchy is not what you expect, a field may be in the wrong area or in an unexpected position. Dragging fields to a different order changes the report structure; collapsing a field only hides detail within the existing structure.
Expand the details again
To reopen one group, click its plus button or right-click its label and choose Expand/Collapse > Expand. To show a whole field’s detail, use Expand Entire Field from the corresponding menu, or use the ribbon’s expand control when available. If you cannot see a plus button, the field may already be expanded as far as its available child items allow, or the buttons may be turned off.
Expanding a field does not change the source data or add new records. It reveals child items that the PivotTable already contains. If the expected detail does not appear, confirm that the source data includes those records and that the relevant field is placed in the Rows or Columns area rather than only in Filters or Values.
Common problems and things to check
- The menu has no collapse option: Right-click a row or column item inside the PivotTable, not a worksheet cell outside it, a value cell, or a field heading that does not represent an item. The available options depend on what you select.
- Only one group closes: You chose Collapse, which applies to the selected item. Choose Collapse Entire Field if you want the same level collapsed throughout the report.
- Some detail remains visible: The remaining rows may belong to another field or a higher level that is still expanded. Identify the visible level in the field list and collapse the appropriate field.
- Plus and minus buttons are absent: Turn them on with PivotTable Analyze > Show > +/- Buttons. You can still use the right-click menu when the buttons are hidden.
- The report changes after refresh: Refreshing updates the PivotTable from its source and may affect which items are available or how the report appears. Review the expanded and collapsed state after a refresh, especially if the source structure has changed.
- The PivotTable has no hierarchy: A single row field with no lower-level fields has no nested detail to collapse. Collapse controls are meaningful when items have child items or when another field is nested beneath them.
Also distinguish collapsing from filtering. A filter removes selected items from the visible report; collapsing hides lower-level detail while retaining the parent group and its summary. If a Region disappears entirely, check the field filter rather than assuming that collapse removed it.
When a collapsed view is useful
A collapsed PivotTable is useful when the reader needs the totals first and can open details only when needed. A manager might receive a view showing sales by Region, while an analyst can expand a Region to inspect cities and then stores. Collapsing can also reduce page length when printing or make a dashboard less crowded.
Keep the audience in mind. A collapsed summary can make a report easier to scan, but it can also conceal the detail someone needs to verify a total. Consider leaving clear field labels, preserving useful subtotals, and telling readers that groups can be expanded. If the report must always open in a specific state, save the workbook after setting that view and confirm the state before sharing it.
Excel for Mac and Excel for the web
The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac also supports PivotTable expand and collapse operations, though ribbon placement and context-menu wording can vary by release. Control-click or a two-finger click can open a contextual menu on a Mac when a conventional right-click is unavailable. Look for the same expand/collapse choices on a PivotTable item.
Excel for the web has a more compact interface, and available PivotTable commands can differ from desktop Excel. If the full-field option is not visible in the web interface, open the workbook in the desktop app and use the context-menu steps above. The exact options can depend on the workbook and the features supported by the web version.
Frequently Asked Questions
Is there a keyboard shortcut to collapse all PivotTable rows?
Excel does not provide a universal keyboard shortcut that collapses every field in a PivotTable at once. Use Expand/Collapse > Collapse Entire Field on the relevant row or column field, then repeat for another field if the layout requires it.
Does Collapse Entire Field hide the data?
No. It hides lower-level items in the PivotTable display. It does not delete source rows, change the source table, or remove the group’s summary. Expand the field again to view its detail.
How do I collapse only one year or category?
Right-click that specific year or category label and choose Expand/Collapse > Collapse. Do not choose Collapse Entire Field if other groups should remain open.
Why can’t I see Collapse Entire Field?
Make sure you right-click a label in a field with expandable detail. A value cell, an unrelated worksheet cell, or a field without child items may not offer the command. Also confirm that the PivotTable is selected and that the field is nested in Rows or Columns.
Can I collapse a PivotTable without hiding its subtotals?
Usually, yes: collapsing hides the child items while leaving the parent summary visible. Whether a subtotal appears and where it is positioned depends on the PivotTable’s layout and subtotal settings, so check the report after changing its view.
Related
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Gantt Chart Schedule: Create and Track ProjectsAn Excel Gantt chart schedule is a task list paired with a date-based timeline, where colored cells show…
- Bubble Chart in Excel With Four Quadrants: How to Make OneYes. In Excel, make a bubble chart from three data fields—an X value, a Y value, and a…
- Excel Graphs: Ignore Zero Values Without Hiding DataYes. To keep zero values out of an Excel graph without changing your source data, make a helper…
- Can You Power Pivot Two Excel Sheets? How to Do ItYes. In Excel for Microsoft 365 on Windows, turn each sheet’s data range into an Excel table, add…
- Excel Pivot Chart Not Showing All Data? FixesIf an Excel PivotChart is not showing all the data, first clear filters on the chart, its connected…
- How to Create a Dashboard in Excel: A Practical GuideTo create a dashboard in Excel, organize your source data in a clean table, summarize it with PivotTables…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.