Grouping is one of the best ways to make your data more manageable.
You can use it to show or hide certain parts of your worksheet, so you can focus on the
information you need.
You can even add subtotals to summarize (or outline) your data.
In this example, I have a list of t-shirt orders, but I don't really need to see the
peoples' names and payment information right now.
So with my three columns selected, I'm going to go to the Data tab… and then click the
Group command.
Now we can show or hide the group using the button that's attached to the bracket here.
Click the minus sign to hide (or collapse) the data… and the plus sign to show it again.
To ungroup any data, just make sure it's selected… then click the Ungroup command… and you're
all set.
The Subtotal command is similar to groups.
You can use it to create groups automatically, and also add functions like SUM, COUNT, and
AVERAGE to help you summarize your data.
In this example, I'd like to tally the number of orders for each t-shirt size, including
small, medium, large, and x-large.
Before you can add a subtotal, your data has to be sorted by the column you plan to summarize,
so I've already taken care of that.
All we have to do now is click the Subtotal command… and a dialog box will appear.
Here we'll select the column that's going to be the basis for the subtotal… in this
case, T-Shirt Size.
Next we'll pick the function that we're going to be using.
There are several common functions to choose from, like Sum, Average, and Product.
But we're going to go with Count, which will just count the number of cells that contain
each size.
(If you had numerical data, you could choose Sum, and it would actually add the values
of the cells to create a subtotal.)
Finally, check the column where you want the subtotal to appear.
Then click OK.
Now a couple of things have happened here.
Each t-shirt size is now in its own group (indicated by the brackets here), and all
of those groups are inside a larger group.
Another word for this is an outline.
The other thing that's happened is that each group has a subtotal under it.
So we can see the number of shirts that have been ordered in each size… and at the very
bottom, we can also see the grand total.
To make the worksheet easier to read, you can show or hide each group just like I showed
you before.
But when you have groups inside of groups, like we do in this example, sometimes it's
easier to view them by level.
Just look for the buttons to the left of your worksheet.
The highest level, which is 3 in this case, will show all of your data… while level
2 will hide the details of each size, leaving just the individual subtotals.
Finally, level 1 will hide everything, so we can only see the grand total.
Let's click 3 to show the entire worksheet again.
To remove subtotals from your worksheet, all you have to do is click the Subtotal command…
then click the button that says Remove All.
Grouping and subtotaling can make your worksheet less cluttered, more organized, and easier
to view.
So keep these features in mind the next time you need help summarizing your data.