1 00:00:00,420 --> 00:00:05,790 While Power Pivot is a part of Excel, you don't need to know much about Excel in order to take advantage 2 00:00:05,790 --> 00:00:06,100 of it. 3 00:00:06,570 --> 00:00:12,210 In fact, everything you need to know can be covered in about 10 minutes as the three things that are 4 00:00:12,210 --> 00:00:18,870 necessary are data tables, pivot tables and to a certain extent, understanding how lookup functions 5 00:00:18,870 --> 00:00:19,260 work. 6 00:00:19,950 --> 00:00:21,510 Let's start with data tables. 7 00:00:22,050 --> 00:00:27,750 You might think that all data in Excel is organized and tables, but data tables are a special construct 8 00:00:27,870 --> 00:00:31,600 that contain data and have some special properties that are important to us. 9 00:00:32,130 --> 00:00:34,770 Let's create one and learn about what that means. 10 00:00:36,570 --> 00:00:41,790 So looking at this data set, you might think that it's a data table, it's got Columb names, rows 11 00:00:41,790 --> 00:00:47,250 of data, and it's definitely organized in a table like structure, but it's not actually a data table 12 00:00:47,640 --> 00:00:49,080 to turn it into a table. 13 00:00:49,290 --> 00:00:54,840 We select all of the data that we have and we can either go to the insert tab and choose table or press 14 00:00:54,840 --> 00:00:57,540 control t on the keyboard, which is my preference. 15 00:00:58,780 --> 00:01:04,150 Upon doing this, a small window will pop up and ask for the data range, which will be populated with 16 00:01:04,150 --> 00:01:07,260 our current selection and whether the columns have headers or not. 17 00:01:07,900 --> 00:01:12,730 If your data doesn't have headers, it's good practice to give them headers prior to doing this. 18 00:01:13,300 --> 00:01:17,200 Once you've done that, you can make sure that the box is checked and press OK. 19 00:01:17,920 --> 00:01:22,270 The default formatting for a table will appear with alternating banded blue rose. 20 00:01:22,720 --> 00:01:26,930 Additionally, the table will function a little bit differently in terms of behavior. 21 00:01:27,520 --> 00:01:31,990 I won't go into all of the details regarding that as it's not important with respect to power. 22 00:01:32,200 --> 00:01:37,480 It what is very, very important is that tables have their own names. 23 00:01:37,900 --> 00:01:42,880 Whenever you have a cell within a table selected the tables dynamic ribbon tab will appear. 24 00:01:43,390 --> 00:01:50,230 Within this ribbon tab is a box to the far left that contains the table name to use power, pivot well 25 00:01:50,470 --> 00:01:52,390 and make it as effective as it can be. 26 00:01:52,660 --> 00:01:55,450 It's imperative to give your tables informative names. 27 00:01:56,110 --> 00:02:00,040 In this case, I'll name this table sales, since that's what it contains. 28 00:02:01,850 --> 00:02:07,340 The real strength in tables is that they can be referenced as a combination of table and column names 29 00:02:07,610 --> 00:02:11,790 rather than strictly by row and column references for this table. 30 00:02:12,020 --> 00:02:18,410 I can get the sum of sales by taking equal sum writing sales bracket sales. 31 00:02:18,980 --> 00:02:25,010 And as I enter the formula, you can even see that Excel gives me autocomplete suggestions based upon 32 00:02:25,010 --> 00:02:25,610 the table. 33 00:02:25,610 --> 00:02:31,160 Due to the structure that we've imposed, I could spend a whole ten minutes or more on data tables and 34 00:02:31,160 --> 00:02:32,390 the benefits that they bring. 35 00:02:32,600 --> 00:02:37,790 But for this course we just need to have our data and tables and have them named in order to take advantage 36 00:02:37,790 --> 00:02:38,090 of them. 37 00:02:39,440 --> 00:02:41,660 Next up, let's discuss pivot tables. 38 00:02:42,410 --> 00:02:47,630 Pivot tables are one of those features in Excel that offers an incredible amount of functionality with 39 00:02:47,630 --> 00:02:50,690 very little understanding, necessary pivot tables. 40 00:02:50,690 --> 00:02:56,990 Take a dataset like our sales table here and crunches, aggregates and groups data quickly and easily. 41 00:02:57,680 --> 00:03:00,590 It's hard to exactly describe what a pivot table does. 42 00:03:00,800 --> 00:03:03,650 So let's go make one and play with it using our sales data. 43 00:03:04,700 --> 00:03:10,530 To create a pivot table, it's as easy as selecting our table, going to insert and choosing pivot table. 44 00:03:11,360 --> 00:03:15,590 This will bring up the create pivot table window, which asks for a table or range. 45 00:03:16,100 --> 00:03:21,890 In this case, we can see our sales table is selected as well as where we want our pivot table to be 46 00:03:21,890 --> 00:03:22,550 replaced. 47 00:03:24,110 --> 00:03:30,610 Also light on an existing worksheet and then I'll specify a location next to our existing data, and 48 00:03:30,740 --> 00:03:35,120 once I've done this, I can press OK to create a blank pivot table for us to play with. 49 00:03:37,690 --> 00:03:43,510 Unlike our data table, our pivot table is empty and requires user intervention in order to become useful 50 00:03:44,020 --> 00:03:44,980 to the far right. 51 00:03:45,160 --> 00:03:50,550 We have a task pane that has appeared that's titled Pivot Table Fields, and I'll phase it in here for 52 00:03:50,560 --> 00:03:51,010 viewing. 53 00:03:51,850 --> 00:03:57,970 As you can see, it has four fields, one for each of our data table columns, it also has four different 54 00:03:57,970 --> 00:03:59,260 categories for placement. 55 00:03:59,920 --> 00:04:02,390 The first section to look at is the value section. 56 00:04:02,800 --> 00:04:07,960 This is where you'll generally place numeric information to calculate totals, averages, maximums, 57 00:04:07,990 --> 00:04:11,920 minimums, really anything that you can think of in terms of an aggregation. 58 00:04:12,670 --> 00:04:16,330 The default will usually be either some or count based upon what you put there. 59 00:04:17,050 --> 00:04:21,610 Clicking on the down arrow and choosing value field settings will bring up another window that will 60 00:04:21,610 --> 00:04:23,560 let you customize that computation. 61 00:04:24,310 --> 00:04:29,950 Here, for example, I'll change my sum to an average to get the average daily sales computation. 62 00:04:31,420 --> 00:04:36,130 These aggregations will be context sensitive to the other specifications in a pivot table. 63 00:04:36,490 --> 00:04:39,310 So I'll switch this back to some to explain what I mean. 64 00:04:40,030 --> 00:04:43,060 I'm going to drag the department field to the rows section. 65 00:04:44,410 --> 00:04:50,830 This will populate all of the departments from our data in our rows, and then the sum of sales by department 66 00:04:50,830 --> 00:04:52,060 will appear to the right of them. 67 00:04:52,600 --> 00:04:56,260 This is what context is for each cell for sales. 68 00:04:56,380 --> 00:04:59,790 The context is the department for which the cell belongs to. 69 00:05:00,610 --> 00:05:06,370 We could make the context more complex by moving our date to our column section for a two dimensional 70 00:05:06,370 --> 00:05:06,800 table. 71 00:05:07,530 --> 00:05:13,930 Now each cell has a specific department and month in which it belongs to, so the sum is calculated 72 00:05:13,930 --> 00:05:15,790 based upon the month and department. 73 00:05:16,660 --> 00:05:21,220 We can complicate this somewhat further by dragging store into the filter section. 74 00:05:21,670 --> 00:05:27,330 Here I can click on the dropdown and specify a specific store or some combination of stores. 75 00:05:27,790 --> 00:05:34,840 Now, each cell on our table has a context that refers to the specific department month and store combination 76 00:05:34,840 --> 00:05:36,430 before it calculates the sum. 77 00:05:37,690 --> 00:05:43,330 As you can see, pivot tables are very handy for quickly creating computations across data sets. 78 00:05:45,100 --> 00:05:50,860 Another important feature we'll use in testing our pivot tables in this course is to drill down command. 79 00:05:52,380 --> 00:05:58,440 To drill down to a pivot tables data is to look at the specific data that makes up each cell and a pivot 80 00:05:58,440 --> 00:05:58,810 table. 81 00:05:59,220 --> 00:06:02,930 This can be done by double clicking on a specific cell in the pivot table. 82 00:06:03,540 --> 00:06:09,990 So if I double click on the Dairy March combination cell, I can see all of the data that makes up that 83 00:06:09,990 --> 00:06:11,250 cell's computation. 84 00:06:11,880 --> 00:06:17,460 This action created a new table with a list of data entries, and we can see that they're all dairy, 85 00:06:17,760 --> 00:06:20,450 they're all Kroger and they're all from the month of March. 86 00:06:20,820 --> 00:06:26,220 That's the combination of context that our self contained for standard pivot tables. 87 00:06:26,230 --> 00:06:31,170 This isn't hugely impressive, but it will be very useful when we're working with power pivot and we 88 00:06:31,170 --> 00:06:33,210 need to understand what data has been pulled. 89 00:06:34,230 --> 00:06:37,170 The last bit to know as some basics about data lookups. 90 00:06:37,560 --> 00:06:44,100 A data lookup basically means how do you find a given piece of data in one table, given some kind of 91 00:06:44,100 --> 00:06:45,480 descriptive information? 92 00:06:46,110 --> 00:06:51,480 For example, let's say I wanted to get value for produce for Tom Thumb from this table. 93 00:06:52,110 --> 00:06:58,350 I could do this with a V lookup that takes Tom Thumb as the lock up value the range B2 through efore 94 00:06:58,350 --> 00:07:04,110 as my lookup table and then retrieve the fourth item from that table with a false parameter to make 95 00:07:04,110 --> 00:07:05,580 sure I get a perfect match. 96 00:07:06,300 --> 00:07:10,740 Understanding of the function itself isn't particularly important for this course, though. 97 00:07:10,740 --> 00:07:16,080 If you work with Excel much, you'll want to learn about this and look up at your earliest convenience. 98 00:07:16,470 --> 00:07:18,360 But the concept is what's important. 99 00:07:19,090 --> 00:07:24,300 The concept here is that you have one list of items that you need to filter through and find a specific 100 00:07:24,300 --> 00:07:24,720 item. 101 00:07:25,140 --> 00:07:28,260 In this case, the list we're searching through is our list of stores. 102 00:07:28,800 --> 00:07:33,260 Then you need to capture a specific piece of information associated with that column. 103 00:07:33,720 --> 00:07:35,760 In this case, we want to produce value. 104 00:07:36,840 --> 00:07:42,120 We look up function, doesn't use field names, but instead use a strict integer relationship values 105 00:07:42,810 --> 00:07:44,170 part of the wonder of power. 106 00:07:44,170 --> 00:07:50,160 A pivot is that we will be defining many relationships such as this, where we need to relate the store 107 00:07:50,160 --> 00:07:54,500 list and sells B to be three and before to a separate store list. 108 00:07:54,510 --> 00:07:58,320 And in this case, the single store item listed in Celsi eight. 109 00:07:59,430 --> 00:08:05,370 At this point you should be able to convert data into a data table, create and manipulate a pivot table 110 00:08:05,670 --> 00:08:10,650 and at least conceptually understand what I mean when I say that we need to look up the produce value 111 00:08:10,650 --> 00:08:12,870 for Tom-Tom from a given target table. 112 00:08:13,740 --> 00:08:19,240 And as for power pivot, that's really all you need to know in order to continue with this course. 113 00:08:19,800 --> 00:08:24,660 So now that we're done reviewing the supporting Excel concepts, let's go dive into power. 114 00:08:24,660 --> 00:08:25,050 Pivot.