Today we would be taking you through grouping rows in excel. Grouping rows is very useful for presentation purposes especially for complex models with thousands of lines of information. It is usually used to organize complicated analysis or information in a straight forward manner so that it is easier to read and understand.
Grouping Rows in Excel
In this section we would review two methods of grouping rows in excel. The first is the auto outline option, the second is manual grouping. Without further ado, let’s delve into it:
Automatic Grouping through Auto Outline
This method of grouping is quite rigid and a few things have to be in place before it can work. One of which is that there should be a parent row for each category with a connection to the subordinating rows. You would understand better as we proceed with the steps
Step 1: Arrange your document with the parent (summary) rows either at the top or bottom of subordinate rows, then select a cell within the rows you want to group
Step 2: Navigate to the data tab > Outline, click the arrow under Group and select Auto Outline
You should have a similar result with the below
Note: There should be a relationship between the data for this to work correctly. For instance, the total rows (which represent the booking market) in our example sum up both the market size and revenue of the different trade regions in each booking market. This tells excel that those rows summed up are under the rows with the total. Without this sum, we would get the error notification below when we try to auto outline.
Manual Grouping
For data sets with two or more levels, the auto outline option may not group your data correctly. To do this, you would need to know how you want your data organized and grouped, then you can follow these simple steps to get the job done.
Let’s use our previous example but add new levels to the data.
As you can see, there are sub-levels for each booking market treated in this example. To group this data neatly we would need to create an outer group and then a nested group.
Creating an Outer Group
The outer group is the larger dataset. For example the UK total (row 14) is an outer group, so also is the North America total (row 27). To group the outer groups, follow these steps:
Step 1: Select all the intermediate summary rows and detail rows. This means that for UK we would select from row 2 to row 13.
Step 2: Navigate to the Data tab > Outline > Group and click on Group
Step 3: On selecting Group, you would see the selection box below, select rows and click on ok
Result: Excel adds a bar on the left side of the worksheet that spans the selected rows
Creating an Inner Group (Nested Group)
The process is pretty much the same as that of creating outer groups. Let’s walk you through the steps
Step 1: Select the detail row above the related summary row. For example, let’s group Europe in our example, we need to select from row 2 to 4.
Step 2: Navigate to the Data tab > Outline > Group and click on Group
S
Step 3: Select rows in the selection box and click on ok.
Result:
You should notice that excel created another bar to the right of the initial bar that was created when we did the outer group. This is to indicate that it is a sub level to the outer group. Repeat the process above for each inner level and you should have this:
Using Keyboard Shortcuts to Group Rows
To save a bit of time, we can use keyboard shortcuts instead of always navigating to Data tab > Outline > Group and clicking on Group. This shortcut should replace the just stated process.
Collapse or Expand Outlines to a specific level
Still on the same example, but now let’s add more levels to it. We would include a subtotal row for the two booking markets we are treating. Highlight the intermediate summary row and detail and go through the grouping process as discussed in the previous headings
Now we have four levels, so we can begin with this section:
When you group rows in excel, you get an outline number at the top left corner of your worksheet. These numbers are clickable buttons, and can be used to expand or collapse grouped rows. It works this way, the higher the number at the top left corner, the higher the amount of information showed when clicked and the lower the number at the top left corner, the lower the amount of information showed when clicked.
In our example, we have four outline levels, let’s quickly explain how it works.
- Level 1 displays the Grand Total only, collapsing all other rows
- Level 2 displays shows the Grand Total and Booking Market totals
- Level 3 displays the Grand Total, Booking Market totals and Trade Totals
- Level 4 displays all the rows
Let’s see how our excel sheet would look when we click on level 3
Just as we said above, you can see the Grand Total, Booking Market Totals and Trade Totals.
To expand or collapse, you can also click the side buttons on the rows. For example, clicking on the plus (+) button on row 5 would expand Europe total under the UK booking market.
To collapse intermediate rows to row 5, all we need to do is to click the minus (-) button on row 5 and all grouped information before row 5 are collapsed.
Result:
Ungrouping Rows in Excel
You can ungroup certain rows in excel and keep the remaining outline. To ungroup rows, just follow these steps.
Step 1: Select the rows you want to ungroup. Let’s select Europe total under the UK booking market (Rows 1 to 4)
Step 2: Navigate to Data tab > Outline > Ungroup and click on Ungroup. You can also use the shortcut below:
Step 3: Click on Ok in the selection box that pops out
Result:
Grouping Rows Through Copying & Pasting Formats
This method can save you a lot of time if you have uniform data that need to be grouped. For example, data for the UK and North America are pretty much the same, so when we group information for the UK booking market, we should be able to copy the format and paste for our North America Booking Market. Let’s go through the process.
Step 1: After grouping one data set (example is the UK booking market), highlight the rows and copy (Ctrl + C)
Step 2: After copying, proceed to highlight all rows for North America booking market like you did for the UK booking market.
Step 3: Then click the following combination: ALT + E + S + T
Step 4: Press enter or click on OK and you should have the result below:
Avoid Hiding Rows, Group Instead
Before we close out on this topic, I would just like to touch on something very important which is for you to avoid hiding rows. When working on a model or analysis, there are lot of times you would have a row (or two) that you do not want to show in your presentation but is vital to the other parts of the model, so you result into hiding said row. Yes, I am guilty of this but this is far from modelling best practices. Rather than hiding the rows, group and collapse them. This way, those using or reviewing your model wont miss rows that may be very important for them to understanding the model
That’s all for this post. I hope you were able to learn a thing or two from it. Cheers!
