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.
