In this article, I will discuss how to group columns in Excel. When we work with spreadsheets containing a lot of data, multiple numbers columns create an overwhelming situation. In such cases, if we can group columns containing similar kinds of data, it would be a great help. Fortunately, Excel has some options to group columns. So, let’s explore methods of grouping columns in datasets.
Download the Practice Workbook
You can download the practice workbook that we have used to prepare this article.
5 Methods to Group Columns in Excel
1. Group Excel Columns by Selecting the Whole Columns
Let’s assume, we have a dataset containing some grocery items’ sales quantities along with the total sales quantity. Now, for ease of our work, we will group some columns containing sales quantities.
- First, select the range of the whole columns (Columns C, D, E).
- Next, go to the Data tab from the Excel Ribbon, Then, select the Outline option. Now, click on the Group option from the Group drop-down.
- As a result, specified 3 columns will be grouped.
You can apply multi-level grouping of columns in a similar way. For example, now we will apply another grouping to the previous grocery items dataset. The steps involved are:
➤ Select the whole Column B & Column C.
➤ Go to Data > Outline > Group > Group. At last, another column group is added to the previous group.
Read More: How to Add Columns in Excel (5 Quick Ways)
2. Select Range of Cells to Group Columns in Excel
You can group columns by selecting a range of cells. The selection process is slightly different from Method 1.
- Firstly, select a range of cells. Here, I have selected the range C4:E8.
- Secondly, go to Data > Outline > Group > Group.
- Next, the below window will show up because excel does not understand what you are going to group (columns or rows). The reason behind this is, you just selected cells, not whole columns. Now choose the Columns option from the window and click OK.
- Lastly, the following is our output.
3. Use ‘Auto Outline’ Option to Group Excel Columns
Excel has an amazing ability to recognize data patterns. Such as, in our dataset, Columns C, D, and E contain similar types of data. And, Column F contains the summation of the previous 3 columns. Luckily, in such cases, you do not have to select any column to group them as Excel will automatically sense which columns to group.
- Go to the worksheet at first.
- Next, go to Data > Outline > Group > Auto Outline.
- Consequently, we will get the below result.
- How to Reorder Columns in Excel (6 Easy Methods)
- Lock Columns in Excel (4 Methods)
- How to Freeze Columns in Excel (5 Methods)
- Swap Columns in Excel (5 Methods)
4. Apply Multiple Groupings in Excel Columns
Often, we need to apply multiple groupings with excel columns. For example, in our dataset, we have sales quantities in 4 columns. But, if I want to group Columns C, D, and Columns E, F separately, here is the process.
- First, go to the worksheet containing the dataset.
- Next, go to Data > Outline > Group > Auto Outline.
- In the end, multiple groups of columns have been created.
5. Shortcut Key to Group Columns in Excel
Till now, in this article, we have discussed elaborated methods to group columns in Excel. Interestingly, there are keyboard hotkeys available to group excel columns.
- Go to the dataset and select a range of Columns (described in Method 1).
- Then, from the keyboard, type SHIFT + ALT + Right Arrow (➝).
- As a consequence, the selected columns will be grouped.
- However, if you select cells to group columns (Similar to Method 2), the following window will show up after entering SHIFT + ALT + Right Arrow (➝). Choose the Columns option and get the columns grouped together.
How to Expand and Collapse Column Grouping
After the grouping has been done, we need to expand and collapse column groups when necessary. The reason behind this is, if you do not want to see a certain column group while working, you will hide that group and vice versa. So, I will mention some key ways to hide/ unhide columns.
- If you click on the minus (–) sign, grouped columns will hide.
- Similarly, upon clicking on the plus (+) sign, grouped columns will be expanded.
Likewise above steps, you can expand/collapse groups by clicking on the column levels (1,2…). For example,
- By clicking on the Number 1 box, you can collapse grouped columns.
- Now, by clicking on the Number 2 box, you can expand the grouped columns.
How to Remove Group Columns in Excel
We can ungroup column groups very easily. For your better understanding, now, I will remove the column grouping from the dataset used in Method 1.
- First, go to the worksheet containing the grouped columns and select the grouped Columns C, D, and E.
- Then, go to Data > Outline > Ungroup > Ungroup.
- Subsequently, the grouping of the selected columns will be deleted.
However, if you want to delete all the groupings in a sheet or do not want to delete groupings by selecting any columns then follow the below process.
- In the beginning, go to the worksheet containing the grouped columns.
- Next, go to Data > Outline > Clear Outline.
- As a result, column groups are gone from the dataset.
➤ Besides, you can delete column groups using the following hotkeys:
SHIFT + ALT + Left Arrow (⟵)
Pros & Cons to Group Columns in Excel
Although the column grouping technique is very easy and useful, it has limitations too.
- Column grouping is widely used to create an organized dataset.
- You can hide/expand data depending on the situation.
- The column grouping method cannot group columns that are not adjacent.
In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.