Grouping Columns in Excel (Table of Content)
Excel Grouping Columns
Sometimes the worksheet contains complex data, which is very difficult to read & analyze, to Access & read these types of data in an easier way, the grouping of cells will help you out. Grouping of columns or rows is used if you want to visually group of items or to monitor them in a concise & organized manner under one heading or if you want to hide or show data for better display & presentation. Grouping is very useful & most commonly used in accounting & finance spreadsheets. Under the Data tab in the Ribbon, you can find the Group option in the outline section. In this topic, we are going to learn about Grouping Columns in Excel.
Shortcut Key to Group Columns or Rows
Shift+Alt+Right Arrow is the shortcut key to group columns or rows, whereas
Shift+Alt+Left Arrow is the shortcut key to ungroup columns or rows.
Definition Grouping of Columns in Excel
It’s a process where you visually group the column items or datasets for a better display.
How to Enable Grouping of Columns in Excel?
Let’s check out how to group columns & How to collapse & expand columns after grouping columns.
Example #1 – Grouping of Columns in Excel
Grouping of columns in Excel works out well for structured data where it should contain column headings, and it should not have a blank column or row data.
Initially, you need to select the column in which you want to group it (i.e. B, C, D, E columns). Go to the Data tab, then click on the group option under the outline section.
Click on the columns and then press OK.
Now you can observe in data, the columns are grouped perfectly, and the outline bars you can observe at the top represent different levels of data organization. Grouping also introduces a toggle option, or it will create a hierarchy of groups, which is known as an outline, to help your worksheet appear in an organized manner, where each bar represents a level of organization (Grouping is also referred to as Outlines.)
4.9 (9,107 ratings)
View Course
How to Collapse & Expand Columns After Column Grouping
you can press the “-” buttons in the margin to collapse the columns (B, C, D, E Columns completely disappears) or in case If you want to expand them again, press the “+” buttons in the margin (B, C, D, E Columns appears)
Another way to access data is the use of 1 or 2 options on the left side of the worksheet, i.e. it is called a state, 1st option is called a hidden state (if you click on it, it will hide B, C, D, E Columns) whereas 2nd option is called unhidden state, it will expand those hidden columns, I.E. B, C, D, E Columns appears
To Ungroup Columns in Excel
Select the columns you wish to ungroup (i.e. the columns which you have previously grouped). On the Data tab, in the Outline group, click on Ungroup command.
Click on the columns and then press OK.
Now, you can observe data bars & “+” buttons and “-” buttons disappear in the excel sheet once the ungroup option is selected.
Example #2 – Multiple Grouping of Columns for Sales Data in Excel
In the below-mentioned example, the Table contains product monthly sales data from Jan to Dec month, and it is also represented in quarterly & yearly sales.
Here the data is structured and does not contain any blank cells, hidden rows, or columns.
I don’t want all the monthly sales data to be displayed; I want only quarterly & yearly sales data to be displayed; it can be done through multiple grouping of column options.
Initially, I need to select the column which I want to group it; now, let’s select the months (i.e. Jan, Feb, Mar columns). Go to the Data tab in the Home ribbon, and it will open a toolbar below the ribbon, then click on the group option under the outline section; now you can observe in a data, the columns are grouped perfectly.
Click on the columns and then press OK.
A similar procedure is applied or followed for the month of Apr, May, Jun & Jul, Aug, Sep & Oct, Nov, Dec columns.
Once the grouping of the above-mentioned monthly columns is done, you can observe that the columns are grouped perfectly in a dataset. The four outline bars you can observe at the top represent different data organisation levels.
Collapsing & Expanding Columns After Column Grouping
You can press the “–” symbol or buttons in the outline bar to collapse all the month columns; once you are done, you can observe all the month Columns completely disappears, and Positive or “+” buttons in the outline bar appear.
Now, the sales data looks in a concise & compact form, and it looks well-organized & structured financial data. In case if you want to check any specific monthly sales data, you can expand them again by pressing the “+” buttons in in outline bar so that again, all the monthly sales data will appear.
Another way to access or hide monthly data is the use of 1 or 2 options on the left side of the worksheet, i.e. it is called a state, 1st option is called a hidden state (On a single click on it, it will hide all the month Columns) whereas 2nd option is called unhidden state, it will expand those hidden columns, I.E. all the month Columns appears.
Things to Remember
Grouping columns or rows in Excel is useful to create and maintain well-organized and well-structured financial sales data.
It is a better & superior alternative for hiding & unhiding cells; sometimes, it is not clear to the other user of the excel spreadsheet if you use the hide option. He needs to track which columns or rows you have hidden & where you have hidden.
Prior to applying grouping of columns or rows in excel, You have to ensure that your structured data should not contain any hidden or blank rows & columns; otherwise, your data will be grouped incorrectly.
Apart from grouping, you can also do summarization of datasets in different groups with the help of the Subtotal command.
Recommended Articles
This has been a guide to Grouping Columns in Excel. Here we discussed How to Enable Grouping of Columns in Excel along with Examples and a downloadable excel template. You may also look at these useful functions in excel –
