1
00:00:00,05 --> 00:00:01,04
- [Instructor] A pivot table

2
00:00:01,04 --> 00:00:03,08
is a very powerful tool in Excel.

3
00:00:03,08 --> 00:00:06,06
It allows you to very quickly summarize your data.

4
00:00:06,06 --> 00:00:09,00
You can see trends, you can get data results,

5
00:00:09,00 --> 00:00:10,03
you can filter things.

6
00:00:10,03 --> 00:00:13,02
And the best part about pivot tables is that you can see

7
00:00:13,02 --> 00:00:15,03
my rows and columns of data here.

8
00:00:15,03 --> 00:00:18,03
It's going to leave all of these completely intact.

9
00:00:18,03 --> 00:00:20,07
So I can play around with the data all I want,

10
00:00:20,07 --> 00:00:24,03
and I never have to worry about ruining my actual file.

11
00:00:24,03 --> 00:00:27,07
This contains all of our order information.

12
00:00:27,07 --> 00:00:29,03
For a pivot table to work,

13
00:00:29,03 --> 00:00:32,01
the first thing that has to happen is you need to make sure

14
00:00:32,01 --> 00:00:34,09
you don't have any empty rows or columns.

15
00:00:34,09 --> 00:00:39,04
Also, your Excel file needs headers, like these here.

16
00:00:39,04 --> 00:00:42,08
To get started, change to the insert ribbon tab,

17
00:00:42,08 --> 00:00:46,05
and click pivot table on the left hand side.

18
00:00:46,05 --> 00:00:48,09
Excel will start by selecting the data,

19
00:00:48,09 --> 00:00:51,08
and this is why you can't have any empty columns or rows.

20
00:00:51,08 --> 00:00:54,02
It needs to select everything.

21
00:00:54,02 --> 00:00:56,01
And now, I can choose

22
00:00:56,01 --> 00:00:58,08
where I want the pivot table to be placed.

23
00:00:58,08 --> 00:01:01,01
Now, if you have a small amount of data,

24
00:01:01,01 --> 00:01:03,00
you can put it on this existing worksheet

25
00:01:03,00 --> 00:01:04,05
if you have the room.

26
00:01:04,05 --> 00:01:08,01
But I find it's easier just to put it on a new worksheet.

27
00:01:08,01 --> 00:01:10,00
It's not going to put it on a new file,

28
00:01:10,00 --> 00:01:12,05
remember, just in a new tab.

29
00:01:12,05 --> 00:01:16,02
We'll click OK, and as you can see,

30
00:01:16,02 --> 00:01:19,04
it's created that brand new tab for me.

31
00:01:19,04 --> 00:01:21,03
My original data is still here,

32
00:01:21,03 --> 00:01:24,06
perfectly intact on this sheet.

33
00:01:24,06 --> 00:01:27,00
But now we can start building our pivot table.

34
00:01:27,00 --> 00:01:29,09
The key data is here on the right.

35
00:01:29,09 --> 00:01:32,02
Here's all of our column headers.

36
00:01:32,02 --> 00:01:37,01
Below here we can specify our rows, our columns,

37
00:01:37,01 --> 00:01:39,04
our values that we want to summarize.

38
00:01:39,04 --> 00:01:42,08
And we can even put in a filter if we want to.

39
00:01:42,08 --> 00:01:45,05
And this is how pivot tables are really powerful.

40
00:01:45,05 --> 00:01:48,07
All we need to do is drag and drop these column headers,

41
00:01:48,07 --> 00:01:51,02
and we can rearrange them and move them around

42
00:01:51,02 --> 00:01:52,05
and move them in and out.

43
00:01:52,05 --> 00:01:55,03
Especially if something doesn't give you the desired results

44
00:01:55,03 --> 00:01:58,05
that you want to see, you can put in something else.

45
00:01:58,05 --> 00:02:01,00
For example, I want to see my product categories

46
00:02:01,00 --> 00:02:02,06
and how they sold.

47
00:02:02,06 --> 00:02:07,05
So if I scroll down, I can see my product category field,

48
00:02:07,05 --> 00:02:10,06
and I'm going to drag that into rows.

49
00:02:10,06 --> 00:02:12,07
Instantly, on the left hand side,

50
00:02:12,07 --> 00:02:15,05
here's all of the categories that I have.

51
00:02:15,05 --> 00:02:18,01
Next, I want to see the quantity of units

52
00:02:18,01 --> 00:02:20,04
that I sold of each one.

53
00:02:20,04 --> 00:02:24,08
So I'll take this quantity field and drag it into values.

54
00:02:24,08 --> 00:02:26,09
So right away, we're seeing a sum

55
00:02:26,09 --> 00:02:29,01
of all the quantities of units sold.

56
00:02:29,01 --> 00:02:34,08
And it's pulling all of that data from here.

57
00:02:34,08 --> 00:02:37,03
But maybe I want to qualify that data.

58
00:02:37,03 --> 00:02:39,09
Where's it coming from? Where's it selling?

59
00:02:39,09 --> 00:02:43,08
We can do that by dragging the data into columns.

60
00:02:43,08 --> 00:02:48,09
For example, what kind of customer type sold more units?

61
00:02:48,09 --> 00:02:51,06
If I drag the customer type in here,

62
00:02:51,06 --> 00:02:55,04
I can see them specified, business or individual.

63
00:02:55,04 --> 00:02:58,00
I can see the data, these are the units sold,

64
00:02:58,00 --> 00:02:59,09
and here's the grand total.

65
00:02:59,09 --> 00:03:02,06
Notice up here where it says column labels,

66
00:03:02,06 --> 00:03:06,06
it's not exactly telling me what it is, you can fix that.

67
00:03:06,06 --> 00:03:08,09
If you head over to the design tab,

68
00:03:08,09 --> 00:03:12,03
once we opened our pivot table, we got some new ribbon tabs,

69
00:03:12,03 --> 00:03:15,04
I'll click on report layout.

70
00:03:15,04 --> 00:03:20,01
I can choose to show it in outline form.

71
00:03:20,01 --> 00:03:22,08
And for whatever reason, this fixes it.

72
00:03:22,08 --> 00:03:25,09
So now you can see the field that you've selected here.

73
00:03:25,09 --> 00:03:30,04
Here we have our columns, rows, and values. We can add more.

74
00:03:30,04 --> 00:03:32,07
For example, let's say I don't want to see it

75
00:03:32,07 --> 00:03:36,05
by customer type, I can just drag this back up.

76
00:03:36,05 --> 00:03:40,05
Now it's going to disappear and I can put something else in.

77
00:03:40,05 --> 00:03:42,02
This is going to take up a lot of room,

78
00:03:42,02 --> 00:03:46,00
but you can even find out which state sold the most units.

79
00:03:46,00 --> 00:03:47,08
As you can see, I have a lot

80
00:03:47,08 --> 00:03:49,05
of scrolling to do, but that's okay.

81
00:03:49,05 --> 00:03:52,02
It's still pulling it from the spreadsheet.

82
00:03:52,02 --> 00:03:59,06
I'm going to bring it back to that customer type.

83
00:03:59,06 --> 00:04:02,01
We can even add multiple rows.

84
00:04:02,01 --> 00:04:04,00
It's up to you if you want to do this,

85
00:04:04,00 --> 00:04:07,05
but you can look at the order type, for example.

86
00:04:07,05 --> 00:04:09,06
I'll drop that into rows.

87
00:04:09,06 --> 00:04:12,00
Instantly, it's added a whole nother

88
00:04:12,00 --> 00:04:14,02
dimension to this report.

89
00:04:14,02 --> 00:04:17,00
And as usual, if I don't want to see it again,

90
00:04:17,00 --> 00:04:19,09
I can just drag it out of the report.

91
00:04:19,09 --> 00:04:22,08
This is how pivot reports are great.

92
00:04:22,08 --> 00:04:25,03
At any time, you can change this data.

93
00:04:25,03 --> 00:04:26,08
You can move things around.

94
00:04:26,08 --> 00:04:29,00
You can swap between columns and rows,

95
00:04:29,00 --> 00:04:31,09
anything that's going to give you the big picture

96
00:04:31,09 --> 00:04:33,09
of what's going on with your data.

97
00:04:33,09 --> 00:04:36,03
From here, you could take a screenshot

98
00:04:36,03 --> 00:04:38,01
and put it into a presentation.

99
00:04:38,01 --> 00:04:40,00
Or just insert it into a presentation

100
00:04:40,00 --> 00:04:43,02
directly from PowerPoint, from this Excel spreadsheet.

101
00:04:43,02 --> 00:04:45,01
The possibilities are endless.

102
00:04:45,01 --> 00:04:47,09
And if you decide, if you're just playing around

103
00:04:47,09 --> 00:04:50,07
learning how pivot tables work, that's great.

104
00:04:50,07 --> 00:04:53,01
At any time, you can always right click

105
00:04:53,01 --> 00:04:56,05
and just delete this worksheet.

106
00:04:56,05 --> 00:05:00,00
Don't forget, your data is still perfectly intact here.

107
00:05:00,00 --> 00:05:02,00
It hasn't been touched.