WEBVTT Kind: captions Language: en 00:00:09.710 --> 00:00:12.220 One of the best things about Excel is that it lets 00:00:12.220 --> 00:00:14.870 you deal with really large amounts of data. 00:00:14.870 --> 00:00:17.320 In fact, if you needed to, you could have billions 00:00:17.320 --> 00:00:19.290 of cells in a single spreadsheet. 00:00:19.290 --> 00:00:22.400 But no matter how much data you have, it can still 00:00:22.400 --> 00:00:24.650 be surprisingly easy to manage, 00:00:24.650 --> 00:00:28.730 using a very special feature called PivotTables. 00:00:28.730 --> 00:00:30.850 PivotTables let you summarize and manipulate the 00:00:30.850 --> 00:00:34.060 data, without changing the original spreadsheet. 00:00:34.060 --> 00:00:38.110 If you've never used PivotTables before, they might seem a little intimidating at first, 00:00:38.110 --> 00:00:41.180 but once you get the hang of it, you'll be able to easily make PivotTables 00:00:41.180 --> 00:00:44.810 using just a few mouse clicks. 00:00:44.810 --> 00:00:48.050 In this example, I have some company sales data, 00:00:48.050 --> 00:00:55.469 and there are columns for Salesperson, Region, Account, Order Amount, and Month. 00:00:55.469 --> 00:00:58.079 And I need to answer the question, "What is the 00:00:58.079 --> 00:01:02.670 amount sold by each salesperson?". 00:01:02.670 --> 00:01:05.560 Now because each salesperson appears on multiple rows, 00:01:05.560 --> 00:01:09.180 I would need to add up the rows to find the answer. 00:01:09.180 --> 00:01:13.080 But a PivotTable can do this automatically. 00:01:13.080 --> 00:01:15.610 You'll first need to select the data. 00:01:15.610 --> 00:01:17.610 If it's formatted as a table, you can just click 00:01:17.610 --> 00:01:19.070 any cell, 00:01:19.070 --> 00:01:20.659 and if it's not, you'll need to make sure you 00:01:20.659 --> 00:01:24.390 select everything, including the column headers. 00:01:24.390 --> 00:01:28.380 And then from the Insert tab, click the PivotTable command. 00:01:28.380 --> 00:01:35.920 And you'll usually want to place the PivotTable in 00:01:35.920 --> 00:01:38.940 a New Worksheet. 00:01:38.940 --> 00:01:42.940 And click OK. 00:01:42.940 --> 00:01:46.200 Over on the left is our blank PivotTable. 00:01:46.200 --> 00:01:49.550 And on the right is the Field List. 00:01:49.550 --> 00:01:51.700 All of these field names are just taken from our 00:01:51.700 --> 00:01:56.270 column headers. 00:01:56.270 --> 00:01:58.430 Below that are the four areas where we can add 00:01:58.430 --> 00:02:02.320 fields to build the PivotTable. 00:02:02.320 --> 00:02:04.520 Now before we select any of the fields, let's look 00:02:04.520 --> 00:02:06.469 at the original question again: 00:02:06.469 --> 00:02:11.840 "What is the amount sold by each salesperson?" 00:02:11.840 --> 00:02:16.180 This question tells us exactly which fields we need to use to answer it: 00:02:16.180 --> 00:02:19.910 Salesperson and Order Amount. 00:02:19.910 --> 00:02:21.610 When you click these, they will be added to the 00:02:21.610 --> 00:02:26.660 Row Labels and Values areas below. 00:02:26.660 --> 00:02:29.110 And now on the left we have a PivotTable where we 00:02:29.110 --> 00:02:34.240 can see the amount sold by each salesperson. 00:02:34.240 --> 00:02:37.170 So now our question has been answered, 00:02:37.170 --> 00:02:41.830 and if we want we can modify the PivotTable to answer more questions. 00:02:41.830 --> 00:02:44.170 If we want to know how much each person sold each 00:02:44.170 --> 00:02:45.170 month, 00:02:45.170 --> 00:02:50.890 we can drag the Month field into Column Labels, and this will add columns for each 00:02:50.890 --> 00:02:52.800 month. 00:02:52.800 --> 00:02:57.600 Or, if we wanted to know how much each region sold, 00:02:57.600 --> 00:03:05.130 we could just take the Month and Salesperson fields out, and then drag Region into Row 00:03:05.130 --> 00:03:11.300 Labels. 00:03:11.300 --> 00:03:15.520 So each time we change the fields, the PivotTable is just combining and adding the source data 00:03:15.520 --> 00:03:20.120 in a different way to give us the answer that we need. 00:03:20.120 --> 00:03:22.260 You can experiment by dragging the fields into 00:03:22.260 --> 00:03:24.210 different areas, 00:03:24.210 --> 00:03:27.530 but you may find that some combinations don't work as well. 00:03:27.530 --> 00:03:32.020 For example, if I move Order Amount to Column Labels or Row Labels, 00:03:32.020 --> 00:03:34.770 it doesn't really give me any useful data. 00:03:34.770 --> 00:03:37.781 Since Order Amount contains numerical values, it 00:03:37.781 --> 00:03:42.130 really works best in the Values field. 00:03:42.130 --> 00:03:46.560 Finally, if you click on the Order Amount field 00:03:46.560 --> 00:03:49.590 and go to Value Field Settings, 00:03:49.590 --> 00:03:53.470 you can change the function, so that instead of calculating the Sum, 00:03:53.470 --> 00:03:57.930 it can Count the cells, or find the Average. 00:03:57.930 --> 00:03:59.690 And there are many other functions that you can 00:03:59.690 --> 00:04:05.090 use. 00:04:05.090 --> 00:04:08.430 In Part 2, we're going to talk about Report Filters, 00:04:08.430 --> 00:04:12.440 and we'll also talk about how to add slicers and PivotCharts, 00:04:12.440 --> 00:04:37.760 which will give you even more ways to manipulate the PivotTable.