How to Group Columns in Excel

In our previous post we provided a very detailed guide on grouping rows in excel. This is a continuation of outlining / grouping in excel however we would be touching on grouping columns in this post. Let’s begin

Grouping Columns in Excel

There are two methods of grouping columns in excel, the first method is by the auto outline option in excel and the second method is by manual grouping. Don’t worry, we would take you through both methods before the end of this post.

Automatic Grouping through Auto Outline

Auto outline is not limited to only grouping for rows, it can also be used for grouping columns in which there is at least one parent columns and two or more subordinating columns connected in some kind of way. This connection may be as simple as one column summing up the value of two other columns. In this case, the column containing the sum of the two other columns would be the parent column whilst the two columns would be the subordinating columns.

Still confused? Don’t worry, just follow the steps below and you should get a clearer picture

Step 1: Arrange your columns from left to right with your subordinating columns appearing before parent column.

Auto Outline Columns

Step 2: Select a cell within the columns you want to group and navigate to the data tab > Outline, click the arrow under Group and select Auto Outline.

Auto Outline Columns step2

Result: If you arranged your data well and followed all the steps, you should have a result similar to the image below.

Auto Outline Columns step3

You would notice that excel as added outline numbers at the top left of your worksheet. You can control the amount of information to show by clicking on them. Let’s breakdown what is displayed in each level.

Level 1 displays the Grand total only, collapsing all other columns

Level 2 displays the Grand Total and Europe 1 and Europe 2 Totals

Level 3 displays all columns

Let’s see how our excel sheet would look when we click on level 2.

Auto Outline Columns step4.jpg

Just as we said above, level 2 displays the Grand Total, Europe 1 and Europe 2 Totals only. Aside from clicking on the outline numbers, you can also expand and collapse grouped columns by clicking on the + and – buttons at the top of the column Letters.

For example, if I want to expand Europe 1 total in the image above, all I need do is click on the plus (+) button above column D and voila, we have this:

Auto Outline Columns step5 1

Manual Grouping of Columns

For data sets without a necessary indicator of the relationship between levels (for example one level being the sum of the other 2 levels), auto outline would not function correctly. The relief is that you can manually choose the columns to group. Let’s go through the steps using the same example as the previous section.

Creating an Outer Group

An outer group is the largest data set in a grouped dataset with more than one levels. In our example, our outer group would be the Europe Grand total. To group the outer group in a column grouping, just follow these steps.

Step 1: Select all the intermediate summary columns and detail columns. In our example we have already established that Europe Grand total is our outer group. All we need to do is to select from column B to G and follow the remaining steps below.

Column Manual Grouping Step 1 2

Step 2: Navigate to the Data tab > Outline > Group and click on Group

Column Manual Grouping Step 2 2

Step 3: On selecting groups, you would see the selection box below, select columns and click on ok

Column Manual Grouping Step 2 1

Result: Excel adds a bar at the top of the worksheet that spans the selected columns

Column Manual Grouping Step 4 1

Creating an Inner group

The process of grouping inner columns is the same as discussed under creating an outer group. Let’s walk you through the steps.

Step 1: Select the detail columns to the left. For example, let’s group Europe Total 1, we need to select from column B to Column C.

Create Inner Group Part 1

Step 2: Navigate to the Data tab > Outline > Group and click on Group

Create Inner Group Part 2 2

Step 3: Select columns in the selection box and click on Ok

Create Inner Group Part 3

Result: You should have a similar result with the image below

Create Inner Group Part 5

Ungrouping Columns

You can ungroup any of the outlines by following these steps:

Step 1: Let’s ungroup the Europe 1 total we grouped in the preceding section. To do this, select column B to C.

Ungroup Inner Group Part 1

Step 2: Navigate to Data tab > Outline > Ungroup and click on Ungroup. You can also use the shortcut below.

Shift +ALT +Left Arrow
 
Ungroup Inner Group Part 2

Step 3: Select columns in the selection box and click on OK

Ungroup Inner Group Part 4

Result: The outline number is down to 2

Ungroup Inner Group Part 5

Avoid Hiding Columns

The major pitfall of hiding columns in excel is that excel doesn’t notify its users about columns that are hidden. This means that in the case of another person using or reviewing your model, they may miss out on some important columns you hide just for presentation purposes. The user is only able catch that columns are hidden if he/she notices that the significant rise / fall in column alphabets. Example is column D to column M.

Leave a Comment