Others

How to Collapse Excel Subtotal Groups to Show Only Subtotal Rows

Phi Hung Vo Oct 1, 2026 869 views

Question details

The user needs to collapse multiple groups of subtotal rows in Excel simultaneously to view only the subtotal summaries without extensive scrolling.

How to Collapse Excel Subtotal Groups to Show Only Subtotal Rows

Product: Excel
Device & OS: not provided
Scenario: Managing and viewing large datasets with subtotals applied.
Observed behavior: Thousands of detailed rows remain visible, requiring excessive scrolling to locate and view the subtotal rows.

Before you start

Ensure that you have already applied the Subtotal feature to your dataset, which automatically generates the outline grouping controls on the left side of your worksheet.

Solution 1Recommended

Use Outline Level Buttons to Collapse Subtotal Groups

This is the most efficient way to collapse all subtotal groups simultaneously using Excel's built-in outline controls.

When you apply subtotals in Excel, it automatically outlines the data into different levels. You can use the outline level buttons at the top left of the worksheet to expand or collapse these levels with a single click.

Locate Outline Controls

Look above the row headings on the far-left side of your Excel window for the outline level numbers, which are typically 1, 2, and 3.

Select the Appropriate Level

Click on the number '2' button. Level 2 collapses all the detailed data rows, leaving only the subtotal rows and the grand total visible.

Expand Specific Groups (Optional)

If you need to view the detailed rows for a specific subtotal while keeping the rest collapsed, click the '+' (plus) button next to that specific subtotal row in the left margin.

Use Outline Level Buttons to Collapse Subtotal Groups

Quick Tip: Clicking the '1' button will collapse everything to show only the Grand Total, while clicking '3' will fully expand all detailed data rows.

Powerful Data Management

Easily Manage Subtotals and Grouped Data in WPS Spreadsheet

WPS Spreadsheet provides intuitive data grouping and subtotal features. You can effortlessly manage large datasets, apply subtotals, and collapse outlines with a single click, just like you would in Microsoft Excel.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
2. Apply Subtotals: Navigate to the 'Data' tab and click on 'Subtotal' to automatically calculate and group your data based on your preferred columns.
3. Collapse Outline: Click the outline number '2' located in the top-left margin to instantly collapse all detailed rows and view only the subtotal summaries.

Fully compatible with Microsoft Excel (.xlsx) formats and subtotal outlines.Intuitive interface for quickly grouping and summarizing large datasets.Free and lightweight alternative to heavy spreadsheet software.Seamless transition with a familiar layout and outline control buttons.

microsoft office alternative - wps office

Frequently Asked Questions

Why don't I see the outline level numbers in Excel?

Outline level numbers only appear after you have grouped rows or applied the Subtotal feature. If you have applied subtotals but still do not see them, go to File > Options > Advanced, scroll to the 'Display options for this worksheet' section, and ensure that 'Show outline symbols if an outline is applied' is checked.

Can I collapse only specific subtotal groups instead of all of them?

Yes. Instead of clicking the level 2 button at the top, click the minus (-) sign located in the left margin next to the specific subtotal group you want to collapse. The rest of the groups will remain expanded.

How do I completely remove the subtotals and outline groups?

To remove the subtotals, click anywhere inside your dataset, go to the Data tab, click on Subtotal, and then click the 'Remove All' button located in the bottom left corner of the Subtotal dialog box.

How do I copy and paste only the visible subtotal rows?

After collapsing the data to level 2, highlight the visible subtotal rows. Press Alt + ; (semicolon) on your keyboard to select only the visible cells. Then, copy them (Ctrl + C) and paste them into a new location. This prevents the hidden detailed rows from being copied.

Office Quick Solutions