Grouping Columns in Excel (Table of Content)
Grouping Columns in Excel
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 it 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 Datatab in the Ribbon, you can find 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 column.
Example #1 – Grouping of Columns in Excel
Grouping of columns in Excel works out well for a 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 which you want to group it (i.e. B, C, D, E columns). Go to the Data tab then click on 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 which represents different levels of data organization. Grouping also introduces 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.)
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 worksheet i.e. it is called a state, 1st option is called 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, 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.
In the final output, 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, it will open a toolbar below the ribbon then click on group option under 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 above-mentioned monthly columns is done, you can observe in a dataset, the columns are grouped perfectly and the four outline bars you can observe at the top which represents different levels of data organization.
Collapsing & Expanding Columns After Column Grouping
You can press the “–” symbol or buttons in 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 worksheet i.e. it is called a state, 1st option is called 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 a well-organized and well-structured financial sales data.
It is a better & superior alternative for hiding & unhiding cells, sometimes, if you use hide option, it is not clear to the other user of the excel spreadsheet. 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 dataset in different groups with the help of 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 downloadable excel template. You may also look at these useful functions in excel –
