How to Collapse Data in Excel

Staring at a massive spreadsheet packed with rows of daily or weekly data can feel like trying to read a novel in a single glance. When you need the big-picture view, all that detail becomes noise. This article will show you two effective ways to collapse your data in Excel, letting you hide the granular details and focus on the important summaries.

Why Should You Collapse Data in Excel?

Working with large datasets is a core part of business analysis, but raw data isn't always helpful for making decisions. Collapsing data allows you to create a high-level summary or outline, which cleans up your view and makes your reports easier to understand. The primary benefits are:

• Improved Readability: Hiding dozens or hundreds of rows makes your spreadsheet less intimidating and easier to navigate.

• Focus on Summaries: It allows you to feature key totals (like quarterly profits or regional sales) without getting bogged down by the individual entries that make them up.

• Interactive Reports: Viewers can easily expand and collapse sections to drill down into the details they care about, making your reports interactive and user-friendly.

Method 1: Using the Group Feature to Create an Outline

Excel's built-in Group feature is the most direct way to collapse rows or columns. It creates an outline on the side of your sheet with simple plus (+) and minus (-) symbols that you can click to expand or hide sections of your data. This is perfect for datasets that are already structured with subtotals.

Let's use a common sales report as an example. Imagine you have sales data broken down by month, which is then subtotaled for each quarter.

Example Data Structure:

Our goal is to hide the monthly data so we only see the Q1 and Q2 totals.

How to Group Rows Step-by-Step

1. Select a section of rows: Highlight the detail rows you want to hide. In our example, click the row number for "January" and drag down to select the rows for "January," "February," and "March." Do not include the subtotal row ("Q1 Total"). Your selection defines what will be hidden underneath the summary level.

2. Navigate to the Group Command: Go to the Data tab in the Excel ribbon. In the "Outline" section, click the Group button.

3. Confirm the Selection in the Pop-up: A small dialog box will appear asking if you want to group by Rows or Columns. Since we selected entire rows, "Rows" will be the default. Click OK.

You’ll immediately see a new margin appear to the left of your row numbers. A line will span the rows you grouped (January, February, March) and end with a minus (-) sign. Success! Clicking this minus sign will "collapse" the monthly data, hiding it from view. The minus sign will turn into a plus (+) sign, which you can click to expand the data again.

Repeat this process for the other sections. Select the rows for April, May, and June, and group them as well. Now you have two collapsible sections, allowing you to show or hide the monthly breakdown for each quarter.

Outlining Levels (Using the 1 and 2 buttons)

After creating your groups, you'll also notice small numbered buttons (1, 2) at the top of the new outline margin. These are outline levels that control the view for the entire sheet.

• Level 1: Clicking the '1' button collapses all sections, showing only the highest level of your data (in this case, your Q1 and Q2 total rows). This gives you the most summarized view.

• Level 2: Clicking the '2' button expands all grouped sections, showing all the underlying details.

If you create nested groups (e.g., grouping quarters into a year-end total), more levels like '3' and '4' will appear, giving you even more control over the view.

Pro Tip: Use the 'Group' Keyboard Shortcut

You can speed up the process significantly with a keyboard shortcut. Once your rows are selected, press:

• To Group: Alt + Shift + Right Arrow

• To Ungroup: Alt + Shift + Left Arrow

This is much faster than clicking through the ribbon menus, especially when you have many sections to group.

How to Group Columns

The process for collapsing columns is nearly identical. This is useful when you have data spread horizontally, like a timeline or budget.

For example, if you have sales data broken out by individual months, but you want to see just the quarterly totals:

| Jan | Feb | Mar | Q1 Total | Apr | May | Jun | Q2 Total |

1. Select the columns for January, February, and March by clicking on the column letters (e.g., drag from C to E).

2. Go to the Data tab and click Group.

3. Click OK (with "Columns" selected).

An outline will appear above the column letters, allowing you to collapse and expand the monthly columns to show just the quarterly summary.

When to Use Excel's Auto Outline Feature

If your data is consistently formatted with summary formulas (like SUM or SUBTOTAL) at the end of each section, you can let Excel do the grouping for you.

To use it, simply click any single cell within your data range and go to the Data tab, then navigate to Group > Auto Outline. Excel will analyze your formulas and automatically create the groupings. This is a big time-saver, but it relies on a perfectly structured sheet. If it doesn't work as expected, it's usually because your formulas aren't structured in a way Excel recognizes, and you'll have to group manually.

Method 2: Using a PivotTable to Summarize and Collapse Data

While the Group feature is great for static reports, PivotTables offer a more powerful and flexible way to collapse and explore data. A PivotTable automatically summarizes and groups your information, creating an interactive report that requires no manual subtotaling upfront.

They are ideal when your data is a raw list — for example, a long export of sales records with columns for Date, Region, Product, and Sales Amount — and you haven't created any summary rows yet.

How to Collapse Data with a PivotTable Step-by-Step

Let's use a sample of raw sales data:

1. Create the PivotTable

• Click anywhere inside your dataset.

• Go to the Insert tab on the Ribbon and click PivotTable.

• Excel will automatically detect your data range. Simply press OK to create the PivotTable in a new worksheet.

2. Build Your Report Structure

A new pane called "PivotTable Fields" will appear on the right side of your screen. This is where you tell the PivotTable how to organize your data. We want to see sales by region, with the ability to drill down to the city level.

• Drag the "Region" field into the Rows area.

• Next, drag the "City" field into the Rows area, placing it directly under "Region." This creates a hierarchy.

• Finally, drag the "Sales" field into the Values area. Excel will automatically sum the sales for you.

3. Expand and Collapse Your Data

Your finished PivotTable will now show a summary list of regions (North, South). Each region name will have a small plus (+) or minus (-) sign next to it.

• Click the (+) next to a region to expand it and reveal the sales totals for each city within that region.

• Click the (-) to collapse it back to the region summary.

Essentially, the PivotTable has automatically done the grouping for you based on the categories you provided. You can make this even deeper by adding more fields, like "Product Category" under City, creating multiple levels of interactive, collapsible data without writing a single formula.

Which Method Should You Choose?

• Use the Group feature when your data is already structured with summary rows/columns and you just need a quick way to create a collapsible outline for viewing or printing.

• Use a PivotTable when you have raw, transactional data and want a powerful, interactive way to summarize, group, and analyze it without modifying your source sheet. It’s more flexible if you want to change your report on the fly.

Final Thoughts

Structuring your data with groups or PivotTables transforms a chaotic spreadsheet into a clean, professional-looking report. By learning to collapse sections, you enable yourself and others to quickly find high-level insights without getting lost in the weeds, reserving the details for when they’re needed.

But let's be honest: even after mastering these techniques, spending your Monday morning manually downloading CSVs and building reports is a massive time sink. With so many marketing and sales platforms, it's a never-ending cycle. We built Graphed because we wanted to automate this entire process. Instead of building PivotTables, you just connect your data sources once and ask questions like, "Create a dashboard comparing our ad spend vs. revenue by campaign for the last 30 days." Graphed instantly builds a real-time dashboard that updates automatically, giving you back hours every week.