WEBVTT Kind: captions Language: en 00:00:11.219 --> 00:00:15.769 Grouping is one of the best ways to make your data more manageable. You can use it to show 00:00:15.769 --> 00:00:20.199 or hide certain parts of your worksheet, so you can focus on the information you need. 00:00:20.199 --> 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:34.010 peoples' names and payment information right now. So with my three columns selected, I'm 00:00:34.010 --> 00:00:40.649 going to go to the Data tab… and then click the 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:07.939 The Subtotal command is similar to groups. You can use it to create groups automatically, 00:01:07.939 --> 00:01:13.360 and also add functions like SUM, COUNT, and AVERAGE to help you summarize your data. 00:01:13.360 --> 00:01:19.760 In this example, I'd like to tally the number of orders for each t-shirt size, including 00:01:19.760 --> 00:01:24.240 small, medium, large, and x-large. 00:01:24.680 --> 00:01:30.840 Before you can add a subtotal, your data has to be sorted by the column you plan to summarize, 00:01:30.880 --> 00:01:39.160 so I've already taken care of that. As you can see, the t-shirts have been put into a custom sort order, going from smallest to largest. 00:01:39.160 --> 00:01:45.500 All we have to do now is click the Subtotal command… and a dialog box will appear. 00:01:45.500 --> 00:01:49.740 Here we'll select the column that's going to be the basis for the subtotal… in this 00:01:49.760 --> 00:01:52.160 case, T-Shirt Size. 00:01:52.160 --> 00:01:56.000 Next we'll pick the function that we're going to be using. 00:01:56.000 --> 00:02:01.740 There are several common functions to choose from, like Sum, Average, and Product. But 00:02:01.740 --> 00:02:06.960 we're going to go with Count, which will just count the number of cells that contain each size. 00:02:07.960 --> 00:02:12.520 (If you had numerical data, you could choose Sum, and it would actually add the 00:02:12.540 --> 00:02:15.920 values of the cells to create a subtotal.) 00:02:15.920 --> 00:02:22.320 Finally, check the column where you want the subtotal to appear. Then click OK. 00:02:22.320 --> 00:02:27.080 Now a couple of things have happened here. Each t-shirt size is now in its own group 00:02:28.320 --> 00:02:34.200 (indicated by the brackets here), and all of those groups are inside a larger group. 00:02:34.720 --> 00:02:38.480 This is also called an outline in Excel. 00:02:38.480 --> 00:02:42.880 The other thing that's happened is that each group has a subtotal under it. So we can see 00:02:42.880 --> 00:02:48.080 the number of shirts that have been ordered in each size… and at the very bottom, we 00:02:48.080 --> 00:02:52.070 can also see the grand total. 00:02:52.080 --> 00:02:56.400 To make the worksheet easier to read, you can show or hide each group just like I showed 00:02:56.400 --> 00:02:57.040 you before. 00:02:57.040 --> 00:03:03.040 But when you have groups inside of groups, like we do in this example, sometimes it's 00:03:03.040 --> 00:03:08.320 easier to view them by level. Just look for the buttons to the left of your worksheet. 00:03:08.320 --> 00:03:13.280 The highest level, which is 3 in this case, will show all of your data… while level 00:03:13.280 --> 00:03:20.000 2 will hide the details of each size, leaving just the individual subtotals. Finally, level 00:03:20.000 --> 00:03:24.931 1 will hide everything, so we can only see the grand total. 00:03:24.960 --> 00:03:28.480 Let's click 3 to show the entire worksheet again. 00:03:28.480 --> 00:03:33.200 OK, there's one more thing we'd like to point out. 00:03:33.200 --> 00:03:38.480 When you use subtotals, you'll notice these extra rows have been inserted into our spreadsheet. 00:03:38.480 --> 00:03:40.740 And while they're helpful for viewing the subtotal information 00:03:40.740 --> 00:03:44.640 If you need to change the formatting or arrangment of your worksheet, 00:03:44.640 --> 00:03:54.960 You might find that these rows just get in the way. In a case like this, it's usually best just to remove the subtotals altogther. 00:03:54.960 --> 00:04:01.040 To remove subtotals from your worksheet, all you have to do is click the Subtotal command… 00:04:01.280 --> 00:04:05.980 then click the button that says Remove All. 00:04:06.000 --> 00:04:12.080 Grouping and subtotaling can make your worksheet less cluttered, more organized, and easier 00:04:12.080 --> 00:04:17.580 to view. So keep these features in mind the next time you need help summarizing your data.