How to Group Columns in Excel (5 Easy Methods)

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.

Steps:

  • First, select the range of the whole columns (Columns C, D, E).

Group Excel Columns by Selecting the Whole Columns

  • 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.

Group Excel Columns by Selecting the Whole Columns

  • As a result, specified 3 columns will be grouped.

Group Excel Columns by Selecting the Whole Columns

Note:

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.


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.

Steps:

  • Firstly, select a range of cells. Here, I have selected the range C4:E8.

Select Range of Cells to Group Columns in Excel

  • Secondly, go to Data > Outline > Group > Group.

Select Range of Cells to Group Columns in Excel

  • 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.

Steps:

  • Go to the worksheet at first.

Use ‘Auto Outline’ Option to Group Excel Columns

  • Next, go to Data > Outline > Group > Auto Outline.

Use ‘Auto Outline’ Option to Group Excel Columns

  • Consequently, we will get the below result.


Similar Readings:


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.

Steps:

  • First, go to the worksheet containing the dataset.

Apply Multiple Groupings in Excel Columns

  • Next, go to Data > Outline > Group > Auto Outline.

Apply Multiple Groupings in Excel Columns

  • 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.

Steps:

  • 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.

How to Expand and Collapse Column Grouping

  • Similarly, upon clicking on the plus (+) sign, grouped columns will be expanded.

How to Expand and Collapse Column Grouping

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.

Again, similarly, by clicking on the number 2 box, you can expand the 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.

Steps:

  • First, go to the worksheet containing the grouped columns and select the grouped Columns C, D, and E.

How to Remove Group Columns in Excel

  • Then, go to Data > Outline > Ungroup > Ungroup.

How to Remove Group Columns in Excel

  • Subsequently, the grouping of the selected columns will be deleted.

How to Remove Group Columns in Excel

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.

Steps:

  • 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.

Note:

➤ 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.

Pros:

  • Column grouping is widely used to create an organized dataset.
  • You can hide/expand data depending on the situation.

Cons:

  • The column grouping method cannot group columns that are not adjacent.

Conclusion

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.


Further Readings

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo