How to Collapse All Rows in Pivot Table Google Sheets

A detailed, multi-level pivot table in Google Sheets can give you incredible granular insights, but it can also be a nightmare to navigate. When every single row group is expanded, you're left with an endless vertical scroll that makes it impossible to see the big picture. This article will show you the fastest ways to collapse all rows in your pivot table so you can get back to a clean, high-level summary view.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Collapse Pivot Table Rows in the First Place?

Working with pivot tables often means switching between a comprehensive overview and a detailed breakdown. When your pivot table is fully expanded, you're stuck in the weeds. Collapsing all the rows instantly brings you back to a summarized, top-level view, which is useful for several reasons:

• Better Presentation: A collapsed pivot table is clean and easy to read, perfect for sharing in reports or during meetings. It allows your audience to see the main takeaways before diving into the specifics.
• Easier Navigation: It provides a clear starting point. You can analyze the summary data and then selectively expand only the categories you need to investigate further, rather than getting lost in a sea of data.
• Finding the Highlights: Seeing the top-level totals helps you quickly identify which categories are the most significant. For example, in a sales report, you can instantly see which region had the highest revenue without scrolling through hundreds of individual city rows.

In short, knowing how to quickly collapse and expand your pivot table makes your analysis more efficient and your reports much clearer.

The Default Way: Collapsing Rows One at a Time

Before we get to the fast methods, let's cover the standard approach. Google Sheets allows you to collapse each row group individually. If your pivot table has nested groups (e.g., Region > Country > City), you can collapse each level.

To do this, simply click the small minus sign (-) icon that appears to the left of any parent row label. This will hide all the child rows nested underneath it, and the icon will change to a plus sign (+).

For example, if you have a "North America" region with "USA" and "Canada" as sub-rows, clicking the minus icon next to "North America" will hide both country rows. You can then do the same for every other region.

While this works perfectly well for small tables, it quickly becomes time-consuming and frustrating if you have dozens or hundreds of parent rows. You didn't build a pivot table to spend your time clicking tiny minus icons repeatedly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Right-Click Trick: The Easiest Way to Collapse All Rows

This is the quick solution you're looking for. Google Sheets has a built-in feature to collapse all rows at a specific level with just a few clicks. It's hidden in the right-click context menu, which is why many users miss it.

Here’s how to do it in three simple steps:

1. Click on any cell containing a row label in the first (outermost) group of your pivot table. To collapse everything, you must start at the top level.
2. Right-click on that cell to bring up the context menu.
3. Hover over the option that says “Collapse…” and a sub-menu will appear. Click on “Collapse all.”

That's it! Every single parent row in your pivot table will instantly collapse, leaving you with a perfect high-level summary. To reverse the action, you can follow the same steps but select “Expand all” instead.

Collapsing Specific Levels in Nested Rows

What if you don't want to collapse everything? This right-click trick also works for inner levels. Let's use an example where your rows are structured as Category > Sub-Category > Product.

• To collapse everything down to just the main Categories, you would right-click on a Category label (e.g., "Electronics") and choose "Collapse all."
• But if you wanted to see the Sub-Categories while hiding the individual Products, you would find a Sub-Category label (e.g., "Smartphones"), right-click it, and then choose "Collapse all." This would collapse all the Product lists under every Sub-Category, giving you a clean middle-level view.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Alternative Method: Editing Rows in the Pivot Table Editor

Another way to get a high-level summary is to modify the pivot table structure itself. Instead of "collapsing" rows, you temporarily remove the lower-level groups. This method is useful if you want to focus on a top-level view for a while without worrying about accidentally expanding a row.

Here's how to simplify your view using the editor:

1. Click anywhere inside your pivot table to open the Pivot table editor panel on the right side of your screen.
2. In the editor, look for the Rows section. You will see all the fields that make up your row hierarchy (e.g., Country, then City).
3. To see a collapsed view, simply remove the deeper levels. Click the X next to the field you want to hide (e.g., remove "City" to only see the totals for each "Country").
4. Your pivot table will instantly update to show a summarized view.

When you're ready to see the details again, you can click the "Add" button in the Rows section and re-select the field you removed. This is less a true "collapse" and more of a "restructure," but it achieves a similar clutter-free result and can be very effective for analysis.

For Power Users: Automating Collapsing Rows with Apps Script

If you find yourself constantly collapsing and expanding the same pivot tables in a complex reporting sheet, you can automate the process with a simple Google Apps Script. This puts a custom button in your menu that collapses all groups with a single click, saving you time in the long run.

Don't worry if you've never used Apps Script - it's simpler than it looks.

How to Create a Collapse/Expand Script

1. Open the Script Editor: In your Google Sheets menu, go to Extensions > Apps Script. A new browser tab will open with the script editor.
2. Paste the Code: Delete any boilerplate code in the editor and paste the following script:

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Pivot Tools')
      .addItem('Collapse All Rows', 'collapseAllPivotRows')
      .addItem('Expand All Rows', 'expandAllPivotRows')
      .addToUi(),
}

function collapseAllPivotRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(),
  const pivotTables = sheet.getPivotTables(),
  
  if (pivotTables.length === 0) {
    SpreadsheetApp.getUi().alert('No pivot tables found on this sheet.'),
    return,
  }

  pivotTables.forEach(pivotTable => {
    const rowGroups = pivotTable.getRowGroups(),
    rowGroups.forEach(group => {
      group.collapseAll(),
    }),
  }),
}

function expandAllPivotRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(),
  const pivotTables = sheet.getPivotTables(),

  if (pivotTables.length === 0) {
    SpreadsheetApp.getUi().alert('No pivot tables found on this sheet.'),
    return,
  }
  
  pivotTables.forEach(pivotTable => {
    const rowGroups = pivotTable.getRowGroups(),
    rowGroups.forEach(group => {
      group.expandAll(),
    }),
  }),
}

1. Save the Script: Click the floppy disk icon to save the project. Give it a name like "PivotTableHelper."
2. Refresh Your Spreadsheet: Go back to your Google Sheet and refresh the page. You should now see a new menu item called “Pivot Tools” next to “Help.”

Now, anytime you need to manage your pivot tables on that sheet, just click on Pivot Tools > Collapse All Rows, and the script will handle it for you. This functionality will be available any time you open this specific spreadsheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Final Thoughts

Managing the view of your Google Sheets pivot table doesn't have to involve endless clicking and scrolling. Using the quick right-click context menu is the most efficient method for collapsing all rows, while adjusting the Row fields in the Pivot table editor offers another way to achieve a clean, summarized view for better analysis.

Manually refreshing reports and wrestling with pivot tables is exactly the kind of repetitive work we built Graphed to eliminate. Our goal is to let you skip the manual data wrangling entirely. We connect directly to your data sources - like Google Analytics, Shopify, and HubSpot - and allow you to build real-time dashboards just by describing what you want to see in plain English. Instead of building and collapsing reports yourself, you can simply ask for the insight you need and get back to making decisions.