This guide will explain how to group columns in Excel using the built-in Group feature.
Users can take advantage of groupings to organize and hide data in a spreadsheet when necessary.
The Group feature in Excel allows the user to specify a grouping of rows or columns in a spreadsheet. You can use the group function to expand and hide certain sections of your worksheet easily.
Unlike hiding cells normally, grouping cells and contracting the group is much easier to control.
Suppose you have a spreadsheet with monthly financial data. You may want to hide certain quarters from view to focus on only a specific time.
With column groupings, users can hide different parts of the sheet so that only relevant data is visible. This is the perfect solution when you must use a large spreadsheet and prefer not to create multiple views of the same data.
Now that we have a grasp on when to use the Group Columns feature, let’s see how this works on an actual sample spreadsheet.
A Real Example of Grouping Columns in Excel
The following section provides several examples of how to group columns in Excel. We will also go into detail about the formulas and tools used in these examples.
First, let’s take a look at a real example of a spreadsheet that can benefit from a grouping.
The spreadsheet below contains a monthly revenue report of a certain business. The financial data is further divided by store location.
The spreadsheet owner can use the Group feature to group together the columns that provide monthly data. Once the columns are grouped together, users can click on a button to hide and show the group.
After hiding the group, users can focus on the yearly grand total and average monthly revenue.
You can even add subgroupings to further outline larger tables.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Use our sample spreadsheet to try hiding and expanding columns yourself.
If you’re ready to try grouping columns in your own spreadsheets, read the next section and follow our step-by-step breakdown on how to do it!
How to Group Columns in Excel
This section will guide you through each step needed to group columns in Excel. You’ll learn how to apply a grouping to a range of columns so you can hide and expand certain sections of your spreadsheet easily.
Follow these steps to start using the grouping function:
- Select the range of columns you want to group together. In this example, we’ll group together columns C to N.
- While the columns are still selected, head to the Data tab and click on the Group option. In the dropdown menu, click on the option labeled ‘Group…’. Alternatively, you can use the keyboard shortcut Shift + Alt+ Right Arrow.
- Your columns should now be grouped together. The grouping should be indicated by an outline seen above the column letters. Users can click on the button on the right of the outline to expand or hide the grouping from view.
- You may also add subgroupings under the current grouping. In this step, we’ll add a subgrouping with the first three months in our report.
- If you’ve selected a range of cells rather than the entire column, a dialog box will appear. Excel will ask you to specify what you want to group. Choose the Columns option and click OK.
- Suppose you want to remove all groupings in your columns. We can do this using the Ungroup function. First, select the columns you want to ungroup. In this example, we’ll remove both groupings in our selection.
- In the Data tab, click on the Ungroup option.
- All groupings found in the selection should now be removed.
These are all the steps you need to start grouping and ungrouping columns in Excel.
Frequently Asked Questions (FAQ)
- What is the advantage of grouping over hiding cells?
Hiding cells in Excel has a major flaw. When you hide a row or column in your spreadsheet, it is not clear to the user that cells have been hidden unless they check the row number or column letter.
When you group a row or column and hide it from view, the user can see an outline on the sides of the sheet that indicates a specific section of the worksheet is currently hidden.
This step-by-step guide should provide you with all the information you need to begin grouping columns in your spreadsheet.
You can use these groupings to allow users of your spreadsheet to hide and expand certain sections of your sheet.
Grouping columns is just one example of the many Excel features you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!