1 00:00:00,000 --> 00:00:03,200 [MUSIC] 2 00:00:03,200 --> 00:00:08,515 Let's talk about one of my favorite 3 00:00:08,515 --> 00:00:13,550 tool in Excel the Pivot Table. 4 00:00:13,550 --> 00:00:17,120 A pivot table is a dynamic report that can summarize and 5 00:00:17,120 --> 00:00:19,470 display data in various ways. 6 00:00:19,470 --> 00:00:22,320 I love how interactive pivot tables are. 7 00:00:22,320 --> 00:00:24,890 They allow you to rearrange data quickly so 8 00:00:24,890 --> 00:00:28,520 that you can analyze interrelationships between the data. 9 00:00:28,520 --> 00:00:31,410 A few people here at Treehouse have told me they're excited for 10 00:00:31,410 --> 00:00:33,790 me to explain how pivot tables work. 11 00:00:33,790 --> 00:00:37,750 Pivot tables are an awesome tool because of how they expose relationships 12 00:00:37,750 --> 00:00:39,140 between data. 13 00:00:39,140 --> 00:00:41,160 Let's talk about how they work. 14 00:00:41,160 --> 00:00:45,340 Here we have a spreadsheet that shows sales for a fictional store. 15 00:00:45,340 --> 00:00:48,720 And if you'll like to follow along we can find a link to this spreadsheet in 16 00:00:48,720 --> 00:00:50,260 the project files. 17 00:00:50,260 --> 00:00:52,250 Before we create the pivot table, 18 00:00:52,250 --> 00:00:55,300 we need to make sure that the data is formatted correctly. 19 00:00:55,300 --> 00:00:57,440 For the pivot table to work properly, 20 00:00:57,440 --> 00:01:01,180 we need to make sure that each column represents a category. 21 00:01:01,180 --> 00:01:05,290 And that each column has a header, telling us what that category is. 22 00:01:05,290 --> 00:01:10,560 In addition to properly formatted columns, you'll also need properly formatted rows. 23 00:01:10,560 --> 00:01:15,760 Each row should contain an entity, or instance of a particular record. 24 00:01:15,760 --> 00:01:22,120 Also, make sure there are no blank columns or rows, or summary columns or rows. 25 00:01:22,120 --> 00:01:26,140 Let's look at an example of a spreadsheet that is not formatted properly. 26 00:01:26,140 --> 00:01:30,070 On the Store Data2 tab you'll notice we have a blank column for 27 00:01:30,070 --> 00:01:33,440 column D and we have blank rows and subtotals. 28 00:01:33,440 --> 00:01:38,070 Also, there are no category headers for the first three columns. 29 00:01:38,070 --> 00:01:42,500 We would not be able to use this data for a pivot table until we cleaned it up. 30 00:01:42,500 --> 00:01:45,380 Now let's look at one that is formatted properly. 31 00:01:45,380 --> 00:01:51,070 You'll notice there are no blank rows or columns and no summary or sub total rows. 32 00:01:51,070 --> 00:01:54,490 You'll also notice we have headers for each category. 33 00:01:54,490 --> 00:01:57,680 Now that we have conferred our data is formatted properly, 34 00:01:57,680 --> 00:02:01,540 let's move on to the next video where I'll show how to build a pivot table