Group, Collapse & Expand Excel Rows/Columns on ALL Worksheets

Collapse Expand All Columns - Macabacus

Grouping rows in Excel lets you collapse and expand parts of your spreadsheet with a click. This makes large datasets easier to read by hiding repeated values.

We’ll show you how to quickly group rows in Excel using both regular Excel features and the Macabacus add-in.

Key Sections

 

Create Financial Models 10x Faster with Macabacus

Gain access to 100+ shortcuts, formula auditing visualizations, easy Excel-to-PowerPoint linking and productivity tools to help you accelerate financial modeling and presentations.

Start your Free Trial
 

Why Group Rows in Excel Spreadsheets?

Grouping rows is useful when your data contains many duplicate values. For example, you may have a dataset with sales figures for multiple products over several years. Without grouping, you’d see the product name repeated on every row. You may also have many different sections to your worksheet. You would then have to scroll down hundreds of rows to get to a section of your workbook.

By grouping rows by product, you can collapse each product to a single row. This simplifies the view and reduces scrolling. Click the arrow next to a product to expand the group and see the individual years again. Grouping also lets you subtotal numeric data at different levels. You can quickly sum figures by product, year, or overall.

Finally, grouping visually separates data into sections. This improves readability of large datasets.

How to Group Rows in Excel

Selecting and Grouping Rows

To group rows in Excel manually:

  1. Select the rows to group together.
  2. Navigate to the Data tab.
  3. Press the Group button to group the selected rows.

Grouping rows allows you to show and hide parts of your data with a single click (we’ll show you how to do that later on in this post). This makes it easy to focus on specific sections of a large dataset.

Grouping Rows Automatically

If your Excel data contains patterns like subtotals, you can automatically group rows by following the instructions below:

  1. Click any cell in your data range.
  2. On the Data tab, open the Group menu.
  3. Select Auto Outline. Excel will detect and group rows based on your data’s structure.

The outlining feature saves time by detecting and applying groups for you based on patterns in your data. This works well for structured data like financial models.

How to Group Columns in Excel

Column Grouping

To manually group columns:

  1. Select your column’s cells that you want to group.
  2. Go to the Data tab.
  3. Press the Group button.

Grouping related columns together makes it easy to show/hide entire categories of data.

Column Grouping Automatically

If your column data has clear patterns, Excel can automatically detect and group them:

  1. Choose any cell in your data range.
  2. Go to the Data tab.
  3. Open the Group menu and select Auto Outline.

Outlining columns follows the same concept as rows – Excel finds patterns and groups accordingly. This can get tedious in large, complex spreadsheets.

Grouping Nested Data

For nested data (groups within groups):

  1. First group the innermost data.
  2. Keep this group collapsed.
  3. Group the larger outer data set.
  4. This makes a hierarchy. The numbering allows you to collapse all (1), show the outer group (2), or display all groups (3).

Nested groups create a hierarchical structure, allowing you to collapse and expand the data at different levels.

Collapsing and Expanding Groups

Once groups are set up, use these shortcuts. Keyboard shortcuts and grouping icons provide quick access to show and hide grouped data.

Keyboard Shortcuts

  1. Select the full sheet with Ctrl + A.
  2. To collapse all groups, press Alt + A + H.
  3. To expand them, press Alt + A + J.

Expand Collapse All Worksheets

Grouping Icons

  1. Click the numbers (usually 1 and 2) on the side of the Excel window.
  2. 1 collapses groups, 2 expands them.

It’s important to note that these won’t work if your goal is to expand or collapse all worksheets. Use Macabacus for multi-sheet grouping.

Macabacus Shortcuts

Macabacus has quick shortcuts to collapse and expand.


Try these and 100+ other custom shortcuts with Macabacus

  • Collapse grouped rows: Alt + Shift + –
  • Expand grouped rows: Alt + Shift + =
  • Collapse grouped columns: Ctrl + Alt + Shift + –
  • Expand grouped columns: Ctrl + Alt + Shift + =

Macabacus extends grouping functionality beyond what’s built into Excel and can also help you perform this action  across all sheets or the whole workbook.

Expand All Columns - Macabacus

Collapsing and Expanding All Worksheets using Macabacus

To collapse/expand across multiple sheets:

  1. Highlight multiple sheets/tabs.
  2. Use the Macabacus shortcuts above.
  3. Confirm you want to apply to all selected sheets.

This allows you to quickly show or hide groups on multiple sheets at the same time. The “Prepare to Share” tool will also help you collapse all groups before sending the workbook. This removes clutter by hiding grouped rows and columns before sharing with others.

Prepare to Share - Collapse Groupings

When to Use Macabacus for Grouping Rows in Excel

Grouping rows manually or without shortcuts works fine for small datasets, but Excel’s native tools have clear limitations:

  • Grouping large sheets with many rows is tedious
  • Expanding/collapsing many groups requires excessive clicking
  • Can’t collapse groups on all sheets at once

This is where Macabacus shines. It lets you instantly expand or collapse all groups with a keyboard shortcut. It is extremely useful if you work with:

  • Large, complex Excel workbooks
  • Dashboards or reports with many grouped rows
  • Multi-sheet workbooks requiring group consistency

Try the Macabacus free trial today to instantly expand or collapse groups across entire workbooks with a simple keyboard shortcut.

Create Financial Models 10x Faster with Macabacus

Gain access to 100+ shortcuts, formula auditing visualizations, easy Excel-to-PowerPoint linking and productivity tools to help you accelerate financial modeling and presentations.

Start your Free Trial
 

Discover more topics

Build an operating model
In this tutorial, we will walk through how to build a general industry business operating model.
Read more
Build an M&A model
In this section, we demonstrate how to model a merger of two public companies in Excel.
Read more
Build an LBO model
In this tutorial, we will walk you through building an LBO model in Excel.
Read more
Asset and Stock Deals
The first step in purchase price allocation, or PPA, is to determine the purchase price.
Read more