When working on an extensive Excel worksheet, you can avoid getting confused and overwhelmed by organizing columns into groups.
This will enable you to easily show and hide different areas of the worksheet so that only relevant data is visible.
In this tutorial, I will show you three methods to group columns in Excel.
Note: Ensure that the worksheet does not have any hidden columns before applying any of the following methods.
This Tutorial Covers:
ToggleMethod #1: Select the Columns to be Grouped and Apply the Group Command
When we use this method, we first select the columns that we want to group and then apply the Group command.
The following dataset shows the sales of various tablets for three quarters.
In the above dataset, I want to group all the columns between the Tablet column and the Grand Total Column (so that all the months and quarters sales data could be easily hidden with a click).
Below are the steps to group columns in Excel:
- Select the columns by clicking the header of column B, holding down the mouse button, and dragging across the column headers to the header of column M.
- Select the Data tab, in the Outline group, click the downward arrow on the Group button and choose the Group option.
Alternatively, you can use the keyboard shortcut Shift + Alt + Right Arrow.
Level 1 outline is created as shown below (a gray line appears over the columns that have been grouped):
When you click the outline number 1 button in the top left corner (or you click the minus button in the top right corner), all the columns within the group are hidden, as shown below:
To unhide the columns, click the plus (+) button on top of the group.
How to Create Inner Groups
In the above example, we have created only one outline where all the selected columns were grouped.
Now you can follow the same steps and create inner groups within the main group.
For example, in our data set, we have grouped all the month’s data together as the main outline, and then we can also create inner groups for each quarter.
Below is our dataset where we have a Level 1 outline where all the columns in between columns A and N are grouped.
We want to create an inner group of columns within this group.
Below are the steps to create an inner group of columns:
- Select the columns we want to be included in the inner group. We want to include columns B, C, and D in this case.
- Select the Data tab, in the Outline group, click the downward arrow on the Group button and choose the Group option.
Alternatively, you can use the keyboard shortcut Shift + Alt + Right Arrow.
The inner group is created:
Note that because only adjacent columns can be grouped, we will have to repeat the process for any other group we want to create.
We can group the three columns preceding the Q2 Total column and the three columns preceding the Q3 Total column in the same way.
We now have 2 levels of column groups:
- One outer level 1 group is made up of columns B to M.
- Three inner level 2 groups; columns B-D, columns E-H, and columns J-K.
To collapse all the inner groups, click the level 2 button on the left of the groups.
To collapse each inner group click the minus (-) button on top of the group.
Method #2: Select Cells in the Columns to be Grouped and Apply the Group Command
This method is a variation of Method #1.
Instead of selecting the columns to be grouped and then applying the Group command, we select cells in the columns we want to group and apply the Group command.
The following dataset shows the sales of various tablets for three quarters.
We want to group all the columns between the Tablet and the Grand Total column.
Below are the steps to group columns in Excel:
- Select at least one cell in the columns we want to group.
- Select the Data tab, in the Outline group, click the downward arrow on the Group button and choose the Group option.
Alternatively, you can use the keyboard shortcut Shift + Alt + Right Arrow.
- In the Group dialog box that opens, select the Columns option and hen click Ok.
Level 1 outline is created as shown below:
Once the outline has been created, you can quickly hide the columns by clicking on the minus button at the top right part of the worksheet (the minus button is at the end of the gray line).
As soon as you click on the minus button, all the columns you have grouped will be hidden, and you will get the result as shown below.
To unhide the columns click the plus (+) button on top of the group.
Method #3: Use the Auto Outline Option
If your dataset has a predictable pattern, you can use the Auto Outline option in the Outline group to group the columns automatically.
The following dataset has a predictable pattern.
Columns B, C, D, F, G, H, J, K, and L contain the same type of data, the monthly sales figures for various tablets.
Each of the columns E, I, and M contains the summation of the three preceding columns. Column N contains the Grand Total of columns E, I, and M.
We can easily group columns in this dataset using the Auto Outline option.
We use the following steps to group the columns between the Tablet column and the Grand Total column.
- Select the tab of the worksheet containing the dataset to activate the worksheet.
- Select the Data tab, in the Outline group, click the down arrow on the Group button and choose the Auto Outline option.
Levels 1 and 2 outlines are created as shown below:
The columns of the dataset have been grouped into one level 1 outline and three level 2 outlines. There are three level 2 outlines because the data represents the tablet sales for three quarters.
When you click the outline number 1 button in the top left corner (or the minus button in the top right corner at the end of the gray line), all the columns within the group are hidden, as shown below:
We can unhide the columns or expand the group by clicking the plus (+) button on top of the group.
To hide the months in groups of threes, click on the minus (-) buttons of the level 2 outline.
In the following example, we have clicked the first minus (-) button of the level 2 outline to hide the first three months:
To collapse all the level 2 groups, we click the level number 2 button on the left of the outline area:
How to Group Columns When Summary Columns are On the Right of Detail Columns
Sometimes your dataset may have the summary columns on the left of the detail columns, as shown below:
You need to do the following before using the Auto Outline feature:
- Click the dialog box launcher icon in the bottom right corner of the Outline group.
- In the Settings dialog box that appears, deselect the ‘Summary columns to right of detail‘ option and click OK.
You can now use the Auto Outline option as explained earlier in this method, and the result will be as follows:
Notice that the Auto Outline feature has generated only one level of grouping with three groups, and the minus (-) buttons are on the left of the groups and not on the right.
How to Ungroup Particular Columns
To ungroup particular columns do the following:
- Select the columns you want to ungroup, for example, columns B-D in the following dataset,
- On the Data tab, in the Outline group, click the Ungroup button.
Alternatively, you can use the shortcut Shift + Alt + Left Arrow Key.
Columns B-D are ungrouped as seen below:
How to Clear an Outline in Grouped Columns
To remove all the column groupings, do the following:
- Open the Data tab, in the Outline group, click the down arrow below the Ungroup button and choose the Clear Outline option.
The other steps would instantly remove the outline in all the column groupings would be gone.
In this tutorial, I covered some methods you can use to group columns in Excel quickly.
If your data is arranged where there’s a predictable pattern, you can use the Auto Outline option, which would automatically create one or more levels of grouping for your data set.
You can also manually select the columns (or the cells in the columns you want to group) and then group them using the options in the ribbon.
Other Excel articles you may also like:
- How to Group Worksheets in Excel
- How to Group Numbers in Pivot Table in Excel
- Create Groups in Quick Access Toolbar in Excel
- How to Freeze Multiple Columns in Excel?
- Delete Blank Columns in Excel (3 Easy Ways + VBA)
- Select Till End of Data in a Column in Excel (Shortcuts)
- How to Delete All Hidden Rows and Columns in Excel
- How to Quickly Unhide COLUMNS in Excel (A Simple Guide)