Group Columns In Excel

What Is Excel Column Grouping?

The option to group columns in Excel helps group columns at multiple levels, which we can expand to show and contract to hide the grouped columns at the click of a few buttons.

Users can utilize the Excel column grouping option to develop a well-organized financial model. And the function enables users to selectively present data relevant to other users while hiding data, such as side evaluations that others might consider extraneous.

For example, the table below lists students, their test scores in three subjects and the aggregates.

And the aim is to group columns B:D, containing the subjects’ test scores, and show only the student list and their aggregate details.

Then, we can group columns in Excel next to each other using the Group command in the Data tab.

In the above group columns in Excel next to each other example, we select the columns B:D cells containing the test scores and click on the Group option in the Data tab.

Next, we click the Group function under the Group option, which opens the Group window. The Group window shows two options, Rows and Columns. And, as we must group by columns, we choose the Columns option and OK to complete the action.

Thus, we can now view the three columns grouped, indicated by the expanded outline ending with the ‘–‘ button above column E.

Further, as we must hide the grouped columns, we can click button 1 or the ‘–‘ button to collapse the group and display the remaining ungrouped columns. On the other hand, clicking button 2 or the ‘+‘ button will expand the group, unhiding the grouped columns.

How To Use Column Grouping In Excel?

There are two methods to automatically group columns in Excel:

1. Using the Group option under the Group function
2. Using the Auto Outline option under the Group function

Method #1 – Using Group Option Under Group Function

The steps to group columns using the Group option are as follows:

1. Ensure no column in the given dataset is hidden.
2. Hover the mouse cursor over the first column’s header in the columns we aim to group. And then, while pressing the mouse’s left key, drag the mouse over the other required column headers to select them.
3. Choose the Data tab – click the Group command drop-down button – select the Group option.

On the other hand, consider we select the cell range across the columns we want to group, containing the column heading and source data. Otherwise, we hover the mouse cursor over a cell in the first column in the columns we wish to group. And then, while pressing the mouse’s left key, drag the mouse over the cells in the other required columns to select them.

Then, choosing the Group option under the Group function in the Data tab will open the Group dialog box.

And we must choose Columns and click OK in the Group window to automatically group columns in Excel.

The above steps create the Level 1 grey outline over the columns we chose to group.

And the line ends with a minus button (‘–‘) over the column after the last column in the columns we chose to group.

Further, clicking the minus button or button 1 will contract the column grouping, with the line collapsing and the minus button turning into a plus button (‘+’). And we can click the plus button or button 2 to expand the columns grouping.

Likewise, we can create multiple group columns in Excel adjacent to the existing column group by selecting the required columns in the given dataset and applying the above steps.

On the other hand, we can create multiple group columns in Excel nested inside the existing column groups. And for that, we can select the required columns from the already grouped columns and iterate the above-explained steps.

Method #2 – Using Auto Outline Option Under Group Function

The following method is useful when the columns we aim to group have a known pattern. And the dataset includes a summary column containing formulas referencing the columns we aim to group at the end or before the required columns.

And the steps are as follows:

1. Ensure that each row of data we wish to outline must have a label in the column on the immediate left or right of the first or last column among the columns we aim to group.
2. Select a cell in the dataset containing the columns required for grouping.
3. Choose the Data tab – click the Group function drop-down button – choose the Auto Outline option.

The above steps will create the Level 1 grey outline over the columns we aim to group automatically.

Likewise, we can create multiple column groups, whether adjacent to or nested within the existing columns group, using the abovementioned steps. However, all the criteria to apply the method must hold.

Examples

The following examples show the practical ways to use the group columns option in Excel.

Example #1

The table below lists sales executives and the sales they generated in the first and second quarters of a financial year.

The task is to determine the total quarterly sales in each quarter and the overall half-yearly sales figures in columns E, I, and J. Next, we must group columns in Excel by quarter one and then by 2. Finally, create nested column groups, one each for the first two months of each quarter.

Then, the steps are as follows:

Step 1: Choose cell E2, enter the Excel SUM function, and press Enter.

=SUM(B2:D2)

Next, update the formula in the remaining column E cells using the Excel fill handle.

Step 2: Choose cell I2, enter the SUM(), and press Enter.

=SUM(F2:H2)

Next, use the fill handle to update the formula in the remaining column I cells.

Step 3: Choose cell J2, enter the following formula, and press Enter.

=E2+I2

Next, use the fill handle to update the formula in the remaining column J cells.

Step 4: Select the range B1:D11. And then, choose Data – Group – Group.

The Group window will open. Choose Columns and click OK to group columns in Excel by quarter 1.

Step 5: Choose the cell range F3:H3. And then, select Data – Group – Group.

The Group window opens, where we must choose Columns and click OK to group columns by Q2.

Step 6: Choose the cell range B1:C11. And then, select Data – Group – Group.

The Group window opens, where we must choose the Columns option and click OK to create a nested or Level 2 columns group.

Step 7: Hover the mouse cursor over column header F.

And while pressing the mouse’s left key, drag the mouse over the column G header to select the two columns entirely.

Next, select Data – Group.

Excel will instantly create a Level 2 or nested columns group, with the grey outline over the two chosen columns.

Thus, the above example explains the methods to create a single group of columns and multiple column groups (adjacent and nested).

Example #2

The following table shows the top ten US states by population and their population statistics for 2015-22.

The task is to display the average population for each state in column B and then group the columns containing the population statistics.

Then, here is how to use the Excel AVERAGE function to find the average population figures and the Auto Outline option to group the required columns.

Step 1: Choose cell B2, enter the AVERAGE(), and press Enter.

=AVERAGE(C2:J2)

Next, apply the formula in cells B3:B11 using the fill handle.

Next, the average population figures are in column B, before or on the left of the columns we aim to group. Thus, we must apply the settings to ensure Excel considers the summary column to the left of the columns we wish to group.

Step 2: Select a cell in the dataset and click the Outline dialog box launcher in the Data tab.

The Settings window opens, where we must uncheck the second option, Summary columns to right of details.

And click OK.

Step 3: Choose a cell in the given dataset. And then, select Data – Group – Auto Outline.

Instantly, we will see the grey outline over the required grouped columns, with the minus button on the left end of the outline, above the summary column.

How To Hide Or Unhide The Group Column?

We can hide or unhide the group column using the minus and plus buttons. Otherwise, we can click the number buttons on the top-left corner of the workspace to complete the required actions.

For example, the following image shows a single group of columns.

And the steps to hide and unhide the grouped columns are as follows:

Step 1: Click the minus or number 1 button to hide the columns grouped in the dataset.

Also, once we click the minus button, the button changes to a plus button.

Step 2: Click the plus or number 2 button to unhide the columns grouped in the dataset.

Likewise, once we click the plus button, the button changes to the minus button.

Furthermore, if we have nested column grouping, as shown below:

Then again, we can use the corresponding minus and plus buttons or the numbered buttons on the top-left corner of the workspace to hide and unhide the column groups.

For instance, clicking the minus button in the outer outline in the first set will change the button to the plus button and hide the outer column group containing columns B:D.

And clicking the minus button in the inner outline in the second set changes the button to the plus button and hides the inner column group containing columns F:G.

Likewise, clicking the number 1 button on the top-left corner of the workspace will show only the plus buttons. And it will hide all the 1^st Level of column groups, containing columns B:D and F:H.

On the other hand, clicking the number 2 button will unhide the outer level column groups, which the minus buttons indicate. And the inner grouped columns remain hidden, indicated by the plus buttons.

And clicking the number 3 button will unhide the inner grouped columns, indicated by the minus buttons.

Shortcut Keys To Hide Or Unhide Column Grouping In Excel

Firstly, the steps to use the shortcut keys to group and ungroup columns in a worksheet are as follows:

1. Select the required columns’ cell ranges and press Shift + Alt + Right arrow to open the Group window. And then, press “C” to choose Columns and Enter to group the chosen columns.
2. Select the required columns’ cell ranges and press Shift + Alt + Left arrow to open the Group window. And then, press “C” to choose Columns and Enter to ungroup the chosen columns.

Next, the shortcut keys to hide and unhide column grouping in Excel are as follows:

1. Select a cell in any of the columns in the defined group. And then, press Alt + A, and H to hide the specific column grouping.
2. Choose a cell in the column above which the group symbol appears (plus button). And then, press Alt + A, and J to unhide the column grouping before the chosen cell.

Why You Should Use The Excel Column Grouping?

You should use the Excel column grouping option for the following reasons:

• You can quickly expand and collapse a set of adjacent columns in a worksheet with a click of a few buttons.
• The option helps to keep worksheet content, such as side calculations, hidden from external users, for who such data may be irrelevant.
• The different column grouping levels make the worksheet data appear well-structured and highly organized.
• The option is an alternative to hiding specific columns.

Why You Should Not Use The Excel Column Grouping?

You should not use the Excel column grouping due to the following reasons:

• The option does not help group non-adjacent columns in the same or across different worksheets.
• Before using the column grouping option, we must ensure the dataset does not have hidden columns.

Important Things To Note

• Ensure all the columns in the given dataset are visible before proceeding with group columns in Excel to avoid incorrect column groupings.
• The column grouping is feasible when the specific columns we want to group are consecutive, and the option works on a per-worksheet basis.
• Shift + Alt + right arrow and Shift + Alt + left arrow are the excel keyboard shortcuts to group and ungroup the required columns.
• Use the minus and plus buttons above the grouped columns or the numbered buttons on the top-left corner of the work area to hide and unhide the required column groups. Otherwise, use the keyboard shortcuts Alt + A, and H and Alt + A, and J to hide and unhide the specific column grouping.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Group Columns In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Group Columns In Excel. Here, we learn how to group columns, hide and unhide grouped columns with examples and points to remember. You can learn more from the following articles –

• Excel Group Sum
• Group in Excel
• Grouped Bar Chart