Excel can hide and reveal groups of rows or columns with the small plus and minus buttons that appear beside the worksheet. This is useful when a sheet contains detail you do not need to see all the time, such as monthly figures underneath a quarterly total.
Group rows so they can expand and collapse
- Select the rows you want to place in a group. For example, select rows 5 through 10 if those rows contain the detail you want to hide.
- Open the Data tab on the ribbon.
- In the Outline section, choose Group.
- If Excel asks what you want to group, choose Rows.
A minus button should appear next to the grouped rows. Select it to collapse the group. Excel hides the detail rows and changes the button to a plus sign. Select the plus sign when you want to expand the rows again.
Do the same thing with columns
The process is nearly identical for columns. Select the columns, choose Data > Group, and choose Columns if Excel displays the Group dialog box. The expand and collapse controls appear above the worksheet rather than along its left edge.
Create more than one outline level
You can nest groups when a worksheet has several levels of detail. For example, individual months can form one group while an entire year forms a larger group. Excel displays numbered outline buttons near the row or column headings. Selecting a number changes how much detail is visible across the sheet.
When building nested groups, start with the smallest detail groups and then group the larger sections around them. This usually produces a more predictable outline.
First confirm that the rows or columns are actually grouped. If the outline exists but its symbols are hidden, open Excel’s options and check the display settings for the current worksheet. Look for the option to show outline symbols when an outline is applied.
Also remember that hiding rows manually is not the same as grouping them. The expand/collapse buttons appear when you use Excel’s Outline and Group features.
Remove an expand-and-collapse group
Select the grouped rows or columns and choose Data > Ungroup. This removes the outline without deleting the worksheet data. If a sheet contains many groups that you want to remove at once, use the Clear Outline command available from the Ungroup menu.
Grouping works especially well on reports where readers need to move between a summary and supporting detail without maintaining separate worksheets.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.