Outlining is one of the best ways of making your
data more manageable.
You can use it to automatically add subtotals, to
group certain rows or columns together,
and also to hide details that you don't need to see.
In this example, I have a T-Shirt order form, and
I'd like to know how many
of each size have been ordered, so I'll use the Subtotal command.
Before you create subtotals, you'll need to Sort
your data.
I've already sorted it by T-Shirt Size.
The Subtotal command is found on the Data tab.
It will open up a dialog box.
Here, we'll select the column that's going to be
the basis for the outline.
In this case, I'll pick T-Shirt Size because that's how my spreadsheet is sorted.
Then, you'll need to pick the function that you'll
be using.
There are some common functions like Sum, Average,
and Product, but we're going to use Count,
which will just count the number of cells that contain each size.
If you had numerical data, you could select Sum,
and it would actually add up
the values of the cells to create the subtotal.
Here, we'll select the column where we want the
subtotal to appear.
And then click OK.
Now a couple of things have happened here.
Each T-Shirt size is now in its own group, which
is shown by this bracket,
and all of those groups are inside a larger group.
This is called an Outline.
The other thing that's happened is that each group
now has a subtotal under it.
So we can see the number of shirts that there are
in each size,
and at the very bottom we can see the total.
To make the spreadsheet easier to read, you can
hide the details of each group,
so that you only see the subtotals and the grand total.
You can hide a group by clicking the minus sign,
and to show it again, click the plus sign.
You can also hide or show groups by level, by
clicking on these numbers.
The highest level, which in this case is 3, will
show all of your data, and level 2 hides the
details of each size, so we can we can still see
the individual subtotals.
And then level 1 will hide those, so we only see
the total.
I'll click on 3 again so I can see everything.
If you decide you don't want part of your spreadsheet to collapse, you can ungroup it.
I'd like to ungroup the Small T-Shirts, so I'll
select the cells,
and then click the Ungroup command.
And in this case we're ungrouping the Rows.
Now remember that all of the groups are inside a
larger group.
So we'll need to click Ungroup again to completely ungroup it.
Now, you can see that the brackets are gone from
these rows,
and they won't collapse when you change the level.
You can also ungroup everything and remove all of
the subtotals by going to the Subtotal command,
and clicking Remove All.
If you want, you can create your own groups of
either rows or columns.
In this example, I don't really need to see people's names or the Payment dates,
so I'll select those columns,
and then click the Group command.
And now I can just hide this group.
So grouping data can make your spreadsheets less
cluttered, more organized, and easier to view.