To collapse all items in an Excel PivotTable row field at once, right-click any item in that field and choose Expand/Collapse > Collapse Entire Field. This hides the field’s lower-level detail without deleting data; if your PivotTable has nested row fields, collapsing the outermost field usually gives the most compact view.
What “collapse all rows” means in a PivotTable
A PivotTable arranges records into fields and levels. For example, a sales report might have Region and then City in the Rows area, with sales in Values. Expanded, the table shows each region and its cities. Collapsing the Region field hides the city rows beneath each region, leaving the region summaries visible.
In this context, “collapse all rows” usually means collapsing every item in one row field—not hiding every row in the worksheet or removing the PivotTable’s detail. Excel’s Collapse Entire Field command applies to the selected field across its items. That distinction matters in a PivotTable with several row fields: collapsing the outer field can hide all the nested levels, while collapsing an inner field affects that level and may leave its parent categories visible.
Collapsed detail remains part of the PivotTable’s underlying summary structure. You can expand it again, and the displayed subtotals or grand totals generally remain available according to the table’s layout and settings.
Collapse a row field across all its items
In Excel for Microsoft 365 on Windows, use the field’s context menu. Click inside the PivotTable first so Excel displays the PivotTable tabs, then follow these steps:
- Find a visible item in the row field you want to collapse. In a Region-and-City example, right-click a region name to collapse the Region field.
- Choose Expand/Collapse from the context menu.
- Choose Collapse Entire Field.
Excel collapses the items in that field throughout the PivotTable. You do not have to repeat the command for each visible region or other category. If you choose a city item instead, you act on the City field, not necessarily on the higher-level Region field. When in doubt, check which row label you clicked and test on a small PivotTable first.
The ribbon offers related commands when a PivotTable cell is selected. Open PivotTable Analyze and look in the Active Field area for the field’s expand or collapse controls; the available command depends on the selected field and Excel’s ribbon layout. The right-click route is often easier to identify because its menu includes the explicit Collapse Entire Field wording.
Choose the right level in a nested row hierarchy
Which field to collapse depends on what you want readers to see. Suppose a PivotTable has Year, Quarter, and Month in the Rows area. Collapsing Year hides the quarters and months beneath each year, producing a compact annual summary. Collapsing Quarter instead can keep the year headings while hiding months within quarters. The field chosen determines the remaining visible detail.
- Show only top-level categories: Collapse the outermost row field, such as Region or Year.
- Keep parent categories but hide the next detail level: Collapse the relevant lower-level field, such as City or Month.
- Show a mixture of detail: Collapse or expand individual items rather than the entire field.
To collapse just one branch, right-click that particular category and use the collapse option for the item, when available, rather than choosing Collapse Entire Field. For example, you might collapse the cities under the West region while leaving the East region’s cities expanded. This is useful for presenting an overview while keeping one area available for comparison.
Field order matters. In the PivotTable Fields pane, the order of fields in the Rows area defines the hierarchy: the first field is the outer level, and fields below it are nested. If the visible result is not what you expected, inspect that order before assuming the collapse command failed.
Collapse versus hide, filter, and remove
These actions can look similar on screen, but they do different jobs. Collapsing hides lower-level labels in the current view while retaining the hierarchy and its summaries. Filtering limits which items are included in the view. Removing a field from the Rows area changes the layout, and deleting source records changes the data itself.
For instance, collapsing a Product Category field may leave category totals visible while concealing individual products. Filtering the product field to show only one product excludes other products from the displayed report. Removing Product from Rows eliminates that level of detail from the layout altogether. Use collapse when the goal is a shorter report that users can expand later, not when you need to exclude records from analysis.
Also distinguish collapsing from hiding worksheet rows with the row-header menu. Worksheet hiding is not a PivotTable hierarchy command and can make the report confusing or interfere with later layout changes. Prefer the PivotTable’s own expand-and-collapse controls for report detail.
Expand the field again or restore individual detail
To show the hidden items again, right-click a visible item in the same row field and choose Expand/Collapse > Expand Entire Field, if that command is offered. The PivotTable Analyze ribbon also has expand controls for the active field. A plus/minus button beside a category can expand or collapse that individual item; if those buttons are not visible, they may be turned off in the PivotTable display options.
When you want to adjust only one branch, use its own plus/minus control or context-menu option instead of expanding the entire field. That lets you keep most of the report compact while opening a particular category for inspection. If field buttons are missing, select the PivotTable and check PivotTable Analyze for the option to show expand/collapse buttons; the exact placement can vary with the ribbon layout.
Collapsing is a display change, not a reversible data edit. It does not require Undo to recover the detail: use Expand Entire Field or expand the relevant item. If you have made other changes since collapsing, avoid relying on Undo as a way to navigate the PivotTable’s hierarchy.
Why the command may be unavailable or behave differently
If Collapse Entire Field does not appear, first confirm that the selected cell belongs to a PivotTable row field. A value cell, a column label, a blank cell, or a regular worksheet range will present different commands. Click a visible row label and open its context menu again.
- You clicked a value rather than a label: Right-click a category name in the row area, not a number in the Values area.
- The field has no lower-level detail: A field with no expanded child items has nothing visible to collapse, so Excel may omit or disable the option.
- You selected the wrong hierarchy level: A command on an inner field will not necessarily produce the top-level-only view you want. Check the field order in the Rows area.
- The PivotTable is connected to an external source or model: Some interactions and menu choices can vary with the data source, permissions, and Excel version. Try the PivotTable Analyze ribbon as an alternative.
After refreshing a PivotTable, new or changed source items can alter the displayed detail. If the report no longer has the intended compact appearance, apply the collapse command again. Also check whether the source data or field arrangement changed; a refresh updates the report from its source but does not mean the collapse command has removed or filtered records.
Windows, Mac, and Excel for the web
The steps above target Excel for Microsoft 365 on Windows. In Excel for Mac, select a PivotTable row item and inspect its right-click menu for Expand/Collapse and the field-level collapse option. The PivotTable ribbon may use a different arrangement or wording depending on the Mac build, so the context menu is a useful place to start. A Mac right-click can be performed with a two-finger click on a trackpad or the configured secondary-click method.
Excel for the web also supports PivotTable interaction, but its available commands and menus can differ from the Windows desktop app and may change as the web interface is updated. If the field-level command is not available in the browser, open the workbook in the desktop app and use the Windows or Mac steps above. Do not confuse a browser’s worksheet-row controls with PivotTable field controls.
Alternatives for a cleaner PivotTable report
Collapsing is a good quick fix when the data should remain available on demand. Other layout choices may be better if you repeatedly need a particular presentation:
- Move or remove a row field: Use the PivotTable Fields pane to change the hierarchy when a detail level is not needed in that report.
- Use a report filter or slicer: Narrow the categories included in the view when the goal is to analyze a subset, rather than conceal child rows.
- Change the report layout: On the Design tab, options such as Report Layout and Subtotals can change how compact or readable the PivotTable appears. These options affect presentation, not the source records.
- Copy a summary for a fixed presentation: If recipients should not expand the detail, you can copy the relevant output to another location as values, taking care that it will not update like the PivotTable.
For a report that readers will explore, keep the PivotTable and collapse its outer field. For a fixed handout, a separately formatted summary may be easier to control. Choose based on whether the reader needs interactive access to the underlying categories.
Frequently Asked Questions
Can I collapse every row field in a PivotTable with one command?
Excel’s Collapse Entire Field command applies to one selected field at a time. In many hierarchies, collapsing the outermost field hides all the child levels and achieves the compact view people mean by “collapse all.” If you need a different mix of levels, collapse the relevant fields or individual items separately.
Does collapsing a PivotTable delete or filter the detail?
No. It changes which hierarchy items are shown, not which source records exist. Use a filter to restrict the displayed records, and change the source data only if the underlying records themselves need to change.
How do I collapse just one category?
Use the minus button beside that category, if shown, or right-click the category and choose the item-level collapse command. Avoid Collapse Entire Field when you want other categories in that field to remain expanded.
Why can’t I see the plus and minus buttons?
The PivotTable’s expand/collapse buttons may be hidden. Select the PivotTable and look on PivotTable Analyze for the option to show them. You can still use the row-label context menu where the relevant expand and collapse commands are available.
Will the PivotTable stay collapsed after a refresh?
Often the current presentation remains, but a refresh can add or change items and affect what is visible. Check the result after refreshing and repeat the collapse command if the report has expanded detail you no longer want to show.
Related
- Free Habit Tracking Spreadsheet: Build One in Excel
- How to Enter Slide Master View in PowerPoint
- Excel Not Responding? Troubleshoot Common Causes and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Create an Org Chart From Excel Data in Microsoft ExcelYes—you can create an org chart from Excel data by organizing employee names and reporting levels, then inserting…
- Unique Excel Charts: Types, Uses, and How to Build ThemExcel does not have a chart type officially called a “unique chart,” but it does offer distinctive chart…
- How to Add a Quadratic Trendline to an Excel ChartTo add a quadratic trendline in Excel for Microsoft 365 on Windows, create or select an XY (Scatter)…
- Excel Waterfall Chart: Add and Format a Subtotal BarTo add a subtotal to an Excel waterfall chart, include a row containing the cumulative subtotal in the…
- Histogram in Excel Without ToolPak: A Step-by-Step GuideYes. You can make a histogram in Excel for Microsoft 365 without the Analysis ToolPak: select your numeric…
- Power Pivot Connection Settings: Where to Find ThemIn Excel for Microsoft 365 on Windows, find Power Pivot connections by selecting Power Pivot > Manage, then,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.