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.
Expand or collapse all workbook groups instantly, without selecting a range. Try this time-saver and 100+ more modeling shortcuts with Macabacus.
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.
To group rows in Excel manually:
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.
If your Excel data contains patterns like subtotals, you can automatically group rows by following the instructions below:
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.
To manually group columns:
Grouping related columns together makes it easy to show/hide entire categories of data.
If your column data has clear patterns, Excel can automatically detect and group them:
Outlining columns follows the same concept as rows – Excel finds patterns and groups accordingly. This can get tedious in large, complex spreadsheets.
For nested data (groups within groups):
Nested groups create a hierarchical structure, allowing you to collapse and expand the data at different levels.
Once groups are set up, use these shortcuts. Keyboard shortcuts and grouping icons provide quick access to show and hide grouped data.
Keyboard Shortcuts
Grouping Icons
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 has quick shortcuts to collapse and expand.
Try these and 100+ other custom shortcuts with Macabacus
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.
To collapse/expand across multiple 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.
Grouping rows manually or without shortcuts works fine for small datasets, but Excel’s native tools have clear limitations:
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:
Try the Macabacus free trial today to instantly expand or collapse groups across entire workbooks with a simple keyboard shortcut.
Gain access to 100+ shortcuts, formula auditing visualizations, easy Excel-to-PowerPoint linking, and productivity tools to build models and presentations faster than ever.