Don’t Hide and Unhide Columns in Excel—Use Groups Instead

In Microsoft Excel, you can permanently hide columns containing data you don’t need to have on show. However, grouping columns is often a better way to achieve the same outcome, especially if you’re dealing with large datasets or looking for ways to make your spreadsheet more flexible.

This guide shows you how to group and ungroup columns in Excel, but you can also use exactly the same method to group and ungroup rows.

Why Grouping Is Better Than Hiding

Before I show you how to create column groups in Excel, let me explain the many benefits of using this method over hiding and unhiding columns.

First, when you hide columns in Excel (by right-clicking the relevant column or columns, and clicking “Hide”), it’s easy for you or others to forget that the spreadsheet contains hidden data. Although the column headings give you a subtle reminder that certain columns are hidden, this isn’t a reliable way to keep track of your spreadsheet’s layout, since it’s possible to remove column headings altogether from a spreadsheet.

Second, expanding and collapsing grouped columns is much quicker and more straightforward than hiding and unhiding columns using the right-click menu. So, not only does grouping columns help speed up your Excel workflow and improve your spreadsheet’s organization, but using this method also makes the spreadsheet more user-friendly if others have access to it.

A third bonus of grouping columns in Excel is that you can also create subgroups, which you can’t do when hiding columns. As a result, grouping columns adds flexibility to your spreadsheet, allowing you to show or hide different levels of data with a single click.

Disclaimer: Hiding columns can be a better option than grouping columns in some very specific instances. For example, suppose you have a calculation column whose sole purpose is to enable other columns to display the correct data and will never need to be seen. In this case, you can hide this column from view altogether by right-clicking the column heading, and selecting “Hide.”

How to Group Columns in Excel

Grouping columns in Excel involves a straightforward process with only a few steps.

In this example, you have monthly sales totals for ten shops, and a yearly total at the end.

To analyze the yearly data and make the spreadsheet easier to read, your aim is to collapse columns B to Q, leaving just the shop number in column A and the yearly total in column R on display. To do this, select columns B to Q by clicking the column B heading and dragging the selection across to column Q.

You can only group columns that are next to each other in Excel. You can’t group nonadjacent columns.

Now, in the Data tab on the ribbon, select “Outline,” then click the top half of the “Group” button. If you’re working on a wider screen, the Group button will be clickable without you having to click “Outline” first.

Notice that a line and a minus sign (-) appear above those columns to indicate that they’re grouped.

When you click the minus sign, those columns will be temporarily hidden, and the minus sign turns into a plus sign (+), which you can click to re-expand those columns.

How to Create Subgroups in Excel

Now that you’ve created a group for the whole year, your next aim is to create subgroups for each quarter.

The key to achieving this is to make sure each subgroup is separated by either a blank column or a total column. Indeed, if you try to create groups or subgroups directly next to each other, Excel will treat them as one continuous group.

In this example, the total columns E, I, M, and Q will act as the barrier between each subgroup, meaning we can create subgroups for columns B to D, F to H, J to L, and N to P.

To create the first subgroup for Q1, select columns B to D, and click Outline > Group in the Data tab on the ribbon.

Now, Excel has added an extra grouping indicator line and minus symbol above this subgroup.

Repeat this process for the remaining quarters until all the subgroups have their own indicator line and minus sign.

Click the four minus signs to collapse each quarterly subgroup, leaving just the total columns visible. Then, click the plus signs to expand them again.

The numerical buttons to the left of the indicator lines represent the number of grouping levels you have applied to your data, and you can click these to quickly jump between different grouping views.

Clicking the highest level number reveals all the data that has been grouped, while clicking the lowest level number reduces the data to the most condensed group you have created.

In other words, when you click level number “3,” you’ll see the grand total (level 1), the quarterly totals (level 2), and all the data that contribute to these totals (level 3).

Click level number “2” to collapse all the subgroups together, leaving only the grand totals (level 1) and the quarterly totals (level 2) visible.

Finally, clicking level number “1” leaves just the grand totals visible.

So, as well as clicking the plus and minus signs to expand and collapse individual groups, you can click the level numbers to expand and collapse all groups that fall within each level at the same time.

Excel also has the option to automatically insert groups by identifying columns containing formulas that calculate subtotals in your data. To activate this and see whether it works for your data (depending on how it’s laid out), in the Data tab on the ribbon, click “Outline,” then choose the “Group” drop-down option, and click “Auto Outline.”

How to Ungroup Columns in Excel

To return your columns to their original states, or to leave certain data visible when you collapse other groups, you can use Excel’s Ungroup button.

First, select the grouped columns you want to ungroup (at any level). Then, click “Ungroup” in the Outline drop-down menu of the Data tab on the ribbon.

Alternatively, after selecting the grouped columns, press Alt+Shift+Left Arrow.

In the screenshot below, I’ve ungrouped the data for Q4, as these figures show the most recent statistics in the spreadsheet. As a result, even though collapsing the grouping to level 2 hides the data for the first three quarters, the figures for Q4 remain visible.

To remove all column (and row) groupings in your spreadsheet, in the Data tab, click Outline > Ungroup > Clear Outline.

If you delete some columns within a group, the remaining columns in that group will remain grouped together. However, if you delete all the columns within a group, that group will disappear altogether.

---

Grouping columns together is just one way to make your spreadsheet easier to read. For example, you could freeze the header rows and columns in spreadsheets with large volumes of data, visualize your statistics in chart form, or use cell notes to avoid having too much text in your worksheet.