WEBVTT Kind: captions Language: en 00:00:09.530 --> 00:00:13.780 One of the best things about Excel is the fact that a single worksheet can hold so much 00:00:13.780 --> 00:00:19.170 information. In fact, you could fill billions of cells if you needed that much room. 00:00:19.170 --> 00:00:23.070 A file with that much data could get pretty overwhelming, but there's a feature called 00:00:23.070 --> 00:00:28.599 PivotTables that can help. PivotTables let you summarize and manipulate your data, without 00:00:28.599 --> 00:00:32.759 actually changing anything in the worksheet. Let's take a look at how they work. 00:00:32.759 --> 00:00:39.559 In this example, I have some company sales data, with columns for the salesperson, region, 00:00:39.559 --> 00:00:48.149 account, order amount, and month. And I need to find the answer to a very specific question: 00:00:48.149 --> 00:00:54.010 What is the amount sold by each salesperson? 00:00:54.010 --> 00:00:58.350 Because each salesperson has data on more than one row, I'd normally have to add these 00:00:58.350 --> 00:01:03.559 up in order to find the answer. But a PivotTable can do this for us automatically, which will 00:01:03.559 --> 00:01:07.170 make the task much, much easier. 00:01:07.170 --> 00:01:11.900 First you need to select your data. You can just click any cell if it's formatted as a 00:01:11.900 --> 00:01:16.330 table; otherwise you need to select all the cells yourself (and don't forget your column 00:01:16.330 --> 00:01:18.049 headers). 00:01:18.049 --> 00:01:25.189 When you're ready, go to the Insert tab… then click the PivotTable command. 00:01:25.189 --> 00:01:29.229 You'll usually want to place your PivotTable on a new worksheet, so make sure this option 00:01:29.229 --> 00:01:36.310 is selected. Then click OK… and a new sheet will appear. 00:01:36.310 --> 00:01:41.180 Over on the left is our blank PivotTable (it's ok that there's nothing there yet). To the 00:01:41.180 --> 00:01:45.729 right is a list of fields that have actually been pulled from our column headers. 00:01:45.729 --> 00:01:50.590 This is where we control what is or isn't displayed in the PivotTable. But before we 00:01:50.590 --> 00:01:54.119 continue, let's take another look at that question: 00:01:54.119 --> 00:01:58.369 What is the amount sold by each salesperson? 00:01:58.369 --> 00:02:06.189 This question tells us exactly which fields to use to find out the answer: Salesperson 00:02:06.189 --> 00:02:08.959 and Order Amount. 00:02:08.959 --> 00:02:13.450 All you have to do is check the box next to the field… and it'll be added to one of 00:02:13.450 --> 00:02:19.870 the areas below—in this case, the Rows category. Another method is to drag and drop the field 00:02:19.870 --> 00:02:25.650 into place; we'll put this one under Values. 00:02:25.650 --> 00:02:30.170 If we go back to the PivotTable… now we can see the information we were looking for; 00:02:30.170 --> 00:02:35.879 in other words, the amount sold by each salesperson. So our question has been answered just by 00:02:35.879 --> 00:02:39.000 choosing what fields to include. 00:02:39.000 --> 00:02:43.489 One of the best things about PivotTables is how easy they are to modify, so you can view 00:02:43.489 --> 00:02:48.500 and analyze different things. Let's say, for example, that you wanted to know each person's 00:02:48.500 --> 00:02:51.769 monthly sales, not just their total sales. 00:02:51.769 --> 00:02:58.290 Just drag the Month field… down here to Columns… and this will add a column for 00:02:58.290 --> 00:03:01.030 each month in the worksheet. 00:03:01.030 --> 00:03:05.209 If we wanted to know what each region sold, we could take out the Month and Salesperson 00:03:05.209 --> 00:03:14.670 fields… and then drag Region… down to Rows. 00:03:14.670 --> 00:03:19.319 So each time we change the fields, the PivotTable is just combining the source data in a different 00:03:19.319 --> 00:03:24.220 way, so we can view the information we need. And we're doing all this without adding or 00:03:24.220 --> 00:03:27.549 changing anything in the original worksheet. 00:03:27.549 --> 00:03:32.269 You can even experiment by dragging the fields into different areas, but some combinations 00:03:32.269 --> 00:03:38.409 work better than others. For instance, if I move Order Amount over to Columns or Rows… 00:03:38.409 --> 00:03:43.790 you can see that it doesn't really give us any useful data. Since Order Amount contains 00:03:43.790 --> 00:03:47.370 numerical values, it really does work best in the Values area. 00:03:47.370 --> 00:03:56.560 Finally, if we click the arrow next to Order Amount… and go to Value Field Settings… 00:03:56.560 --> 00:04:01.680 we can change the function to something other than SUM. Instead we could count the number 00:04:01.680 --> 00:04:08.220 of cells; calculate the average; or use any of the other options here. Some of these functions 00:04:08.220 --> 00:04:11.890 will be more useful than others depending on your data, so that's just something to 00:04:11.890 --> 00:04:15.250 keep in mind. 00:04:15.250 --> 00:04:20.389 In our next video, "Doing More with PivotTables," we're going to talk about Report Filters. 00:04:20.389 --> 00:04:24.960 We'll also take a look at Slicers and PivotCharts, which can give you even more ways to work 00:04:24.960 --> 00:04:26.139 with your PivotTable.