Excel Tip of the Week #62 - Group, ungroup, subtotal

Hello and Happy New Year!  Welcome back to the Excel Tip of the Week.  This week we have a General User post looking at some of the functionality that Excel offers inside the Data tab, and how you can use this to make your spreadsheets more user-friendly. So let’s get started. 

If we select the Data tab at the top, under the “Outline” section we can see three options: Group, Ungroup and Subtotal.  These functions are really useful if you are working with large data sets, and let you slim down your spreadsheets so that they can be more useful when used at a glance.  Let’s start with Group.

Group is a fairly manual command, and is used to reduce the visible size of the worksheet.  To use Group, users simply select the set of data they want to condense, click “Group” and select whether you want the data grouped by column or row. Once this is done, you will see an extra set of lines appear above your column/row labels:

These lines show where a section of data has been grouped.  If you click on the “–“ signs, this data will collapse, and the rows or columns will be hidden:

See how the column/row headers now jump?

You also have two numbers added (see the little 1 and 2 in boxes) – these are group “levels”. In an excel workbook you can have up to 8, meaning that you can create groups within groups. Clicking on these numbers will automatically open or close all the groups at that level.

Try opening a workbook and creating some groups, so that you can play with using levels and expanding/collapsing.  You’ll quickly see how clever use of groups will make your workbooks more clear visually. 

Grouping allows you to effectively hide columns, but makes it more visually obvious that there is data underneath the visible parts of the workbook.

If you want to clear away the groups you have in your workbook, you simply use the “Ungroup” function. Select the group you want to clear (or if you want to clear all groups, select the whole worksheet) and click “Ungroup” – you will then have the option to choose whether you want to ungroup the rows or columns. Note that unless you select the whole workbook or ALL the rows/columns included in the group, ungrouping will not work. Selecting only some of the data will remove that from the group, but will not clear it completely.

Finally, we will look at a function very similar to Group, but with an added feature. Subtotal.  Subtotal sits in the same ribbon as group, and works in a similar way. However, it is more intuitive than group, and offers you greater built-in functionality.   

Unlike grouping, subtotal will do a lot of the work for you. To fully subtotal a set of data, select everything you want including, don’t worry about picking out the separate sub-groups yourself. Then, when you click subtotal, this menu will appear:

You can see that there are a number of options available to you at this point.  Basically, it is asking you two things – what element of the data you want the subtotal for, and what numbers should be added up to give this subtotal.  So in this example, we want the subtotal for each person, and we want to know the total cost per sick day.  Using the check boxes you can add more than one subtotal to this.  Excel will do the work for you and add a new line under each subtotaled group, along with the + buttons we saw in group.

However, in this example there is a problem. See, although Excel knows you want a subtotal by name, it won’t realise that every “Emma” on the list is the same “Emma”; instead it will subtotal every time the name changes. So, in order to get a useful, accurate subtotal, you need to sort your data first.

Try playing about with some of your own data, seeing how you can make a worksheet look more clear and user friendly by using these functions.

Previous post - TODAY, NOW 
TOTW index

Thanks to Emma Horton for her input on this Tip.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.