How to Group Columns in Excel?

Sumit Bansal

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:

Method #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.

data set where columns needs to be grouped

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 columns that you 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.

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:

click the minus button to hide the grouped columns

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.

data set where columns have been 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 columns for which you want to create the inner grouping outline

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:

Inner group outline has been created for the selected columns

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.

multiple inner group outlines have been created

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.

collapsing the inner group outlines in Excel

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.

data set where columns need to be grouped

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 cells in the columns that you 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.

select the columns option in the group dialog box

Level 1 outline is created as shown below:

top level outline has been created after the columns are grouped

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.

clicking on the minus button collapses the group

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.

data set where you want to group the columns

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.

click on the auto outline option in the ribbon in Excel

Levels 1 and 2 outlines are created as shown below:

columns are automatically grouped in level one and level 2 outlines are created

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:

only one inner group columns are collapsed

To collapse all the level 2 groups, we click the level number 2 button on the left of the outline area:

collapsing all the inner groups of columns

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:

when summary columns are at the left in the Dataset

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.

click on the outline dialog box launcher

In the Settings dialog box that appears, deselect the ‘Summary columns to right of detail‘ option and click OK.

uncheck the summary columns to right of detail option

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,

send the columns that you want to on group

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:

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.