WEBVTT
Kind: captions
Language: en

00:00:11.219 --> 00:00:14.610
Grouping is one of the best ways to make your
data more manageable.

00:00:14.610 --> 00:00:18.490
You can use it to show or hide certain parts
of your worksheet, so you can focus on the

00:00:18.490 --> 00:00:20.200
information you need.

00:00:20.200 --> 00:00:24.980
You can even add subtotals to summarize (or
outline) your data.

00:00:24.980 --> 00:00:28.730
In this example, I have a list of t-shirt
orders, but I don't really need to see the

00:00:28.730 --> 00:00:32.140
peoples' names and payment information right
now.

00:00:32.140 --> 00:00:37.800
So with my three columns selected, I'm going
to go to the Data tab… and then click the

00:00:37.800 --> 00:00:40.649
Group command.

00:00:40.649 --> 00:00:46.869
Now we can show or hide the group using the
button that's attached to the bracket here.

00:00:46.869 --> 00:00:53.920
Click the minus sign to hide (or collapse)
the data… and the plus sign to show it again.

00:00:53.920 --> 00:01:01.199
To ungroup any data, just make sure it's selected…
then click the Ungroup command… and you're

00:01:01.199 --> 00:01:02.850
all set.

00:01:02.850 --> 00:01:05.640
The Subtotal command is similar to groups.

00:01:05.640 --> 00:01:10.310
You can use it to create groups automatically,
and also add functions like SUM, COUNT, and

00:01:10.310 --> 00:01:13.360
AVERAGE to help you summarize your data.

00:01:13.360 --> 00:01:17.940
In this example, I'd like to tally the number
of orders for each t-shirt size, including

00:01:17.940 --> 00:01:21.790
small, medium, large, and x-large.

00:01:21.790 --> 00:01:26.340
Before you can add a subtotal, your data has
to be sorted by the column you plan to summarize,

00:01:26.340 --> 00:01:28.730
so I've already taken care of that.

00:01:28.730 --> 00:01:35.310
All we have to do now is click the Subtotal
command… and a dialog box will appear.

00:01:35.310 --> 00:01:39.200
Here we'll select the column that's going
to be the basis for the subtotal… in this

00:01:39.200 --> 00:01:42.060
case, T-Shirt Size.

00:01:42.060 --> 00:01:45.310
Next we'll pick the function that we're going
to be using.

00:01:45.310 --> 00:01:50.190
There are several common functions to choose
from, like Sum, Average, and Product.

00:01:50.190 --> 00:01:53.740
But we're going to go with Count, which will
just count the number of cells that contain

00:01:53.740 --> 00:01:54.840
each size.

00:01:54.840 --> 00:01:59.240
(If you had numerical data, you could choose
Sum, and it would actually add the values

00:01:59.240 --> 00:02:02.020
of the cells to create a subtotal.)

00:02:02.020 --> 00:02:06.930
Finally, check the column where you want the
subtotal to appear.

00:02:06.930 --> 00:02:10.700
Then click OK.

00:02:10.700 --> 00:02:13.400
Now a couple of things have happened here.

00:02:13.400 --> 00:02:18.320
Each t-shirt size is now in its own group
(indicated by the brackets here), and all

00:02:18.320 --> 00:02:21.420
of those groups are inside a larger group.

00:02:21.420 --> 00:02:25.340
Another word for this is an outline.

00:02:25.340 --> 00:02:29.380
The other thing that's happened is that each
group has a subtotal under it.

00:02:29.380 --> 00:02:36.040
So we can see the number of shirts that have
been ordered in each size… and at the very

00:02:36.040 --> 00:02:40.550
bottom, we can also see the grand total.

00:02:40.550 --> 00:02:47.260
To make the worksheet easier to read, you
can show or hide each group just like I showed

00:02:47.260 --> 00:02:50.840
you before.

00:02:50.840 --> 00:02:55.000
But when you have groups inside of groups,
like we do in this example, sometimes it's

00:02:55.000 --> 00:02:57.230
easier to view them by level.

00:02:57.230 --> 00:03:00.330
Just look for the buttons to the left of your
worksheet.

00:03:00.330 --> 00:03:07.110
The highest level, which is 3 in this case,
will show all of your data… while level

00:03:07.110 --> 00:03:12.180
2 will hide the details of each size, leaving
just the individual subtotals.

00:03:12.180 --> 00:03:18.810
Finally, level 1 will hide everything, so
we can only see the grand total.

00:03:18.810 --> 00:03:23.800
Let's click 3 to show the entire worksheet
again.

00:03:23.800 --> 00:03:29.900
To remove subtotals from your worksheet, all
you have to do is click the Subtotal command…

00:03:29.900 --> 00:03:34.640
then click the button that says Remove All.

00:03:34.640 --> 00:03:39.050
Grouping and subtotaling can make your worksheet
less cluttered, more organized, and easier

00:03:39.050 --> 00:03:40.069
to view.

00:03:40.069 --> 00:03:43.569
So keep these features in mind the next time
you need help summarizing your data.