1
00:00:01,130 --> 00:00:07,670
Now that you learned how to insert a pivot table and had to do some basic formatting and analysis.

2
00:00:07,670 --> 00:00:14,780
Let's take this a step further and see how we can analyze multiple items and get our data sorted automatically.

3
00:00:15,350 --> 00:00:16,219
In the next lecture

4
00:00:16,219 --> 00:00:22,640
we're going to take a look at how we can add calculations and multiple reports to our Excel sheet.

5
00:00:22,700 --> 00:00:28,370
The aim of this lecture is to also answer this first question here.

6
00:00:28,520 --> 00:00:35,720
Which product generates the most sales in America and which in Europe? For this we're going to be using

7
00:00:35,780 --> 00:00:43,370
the data table we created in the previous lecture which is sitting in the raw data table tab here.

8
00:00:43,370 --> 00:00:50,250
To insert a pivot table on this sheet, I'm going to go to insert pivot table. For my source,

9
00:00:50,270 --> 00:00:56,810
I can type in the table name directly. If I forget it or if I want to select it I can go back to my raw

10
00:00:56,810 --> 00:01:04,610
data table, go to the top left hand corner and click to get the table name in here. I'm going to put the

11
00:01:04,610 --> 00:01:10,550
pivot table in my existing sheets where I was. Cell A8 is fine so I'm going to go with ok.

12
00:01:10,580 --> 00:01:18,980
I'm going to bring over the pivot table fields right here beside my table. Which fields do I need

13
00:01:18,980 --> 00:01:26,420
to be able to answer the first question here? I need the product, I need sales, and I need the category

14
00:01:26,420 --> 00:01:32,570
that includes America and Europe. Just to make sure we have the correct column headers, let's go back

15
00:01:32,570 --> 00:01:36,710
to our table here and see what the column header is called.

16
00:01:36,800 --> 00:01:42,360
America and Europe is sitting in the region column right here.

17
00:01:42,530 --> 00:01:48,830
Our products are called articles. Instead of the code we're gonna want to see the article description

18
00:01:49,340 --> 00:01:56,150
and our sales value is sitting in sales USD column. Now that we know what they are, all we have to do

19
00:01:56,240 --> 00:01:58,570
is just add them here.

20
00:01:58,630 --> 00:02:05,660
If I go down to find the article, that's the article description. It automatically added it to the

21
00:02:05,660 --> 00:02:06,710
rows.

22
00:02:06,710 --> 00:02:13,670
Now notice that immediately I get a unique list of my articles. So you can also use pivot tables to get

23
00:02:13,670 --> 00:02:16,100
a unique list.

24
00:02:16,100 --> 00:02:22,280
To add region, let's go back up and put a check mark beside it. Now it added the region below the article.

25
00:02:22,280 --> 00:02:30,590
Tthis means that for each article I get the region that exists in my dataset.

26
00:02:30,710 --> 00:02:32,630
This report doesn't look good.

27
00:02:32,630 --> 00:02:35,670
I don't want it in this way. I want it the other way round.

28
00:02:35,720 --> 00:02:39,890
I'm just going to drag region and drop it above article.

29
00:02:39,980 --> 00:02:41,480
This looks more organized.

30
00:02:42,410 --> 00:02:47,340
One thing is missing here and that's my value which is my sales in USD.

31
00:02:47,750 --> 00:02:51,800
So I'm just going to place a checkmark here and I want the sum of sales.

32
00:02:51,800 --> 00:03:00,040
So that looks good. Until now we haven't used columns or filters. Let's see what they do. Drag the region

33
00:03:00,130 --> 00:03:06,540
and drop it in the columns. This creates a new column for each of the regions I have.

34
00:03:06,580 --> 00:03:13,570
In this case it's Europe and America and automatically I also get a grand total for these

35
00:03:13,570 --> 00:03:14,230
as well.

36
00:03:14,410 --> 00:03:23,770
I get one for rows and one for columns. What about dropping it in the filter section? That creates a global filter

37
00:03:24,040 --> 00:03:30,370
from my report and I can select if I just want to see Europe. That restricts my reports to Europe data

38
00:03:30,700 --> 00:03:32,500
or just America.

39
00:03:32,500 --> 00:03:39,010
If I had a lot more items and I want to select multiple items just put a tick mark here and put a tick

40
00:03:39,010 --> 00:03:41,130
mark on the items you want.

41
00:03:41,140 --> 00:03:47,780
So let's just go with all and click on OK. Now I'm going to bring region back and drop it in here.

42
00:03:48,650 --> 00:03:55,080
The moment you deal with multiple items, notice that Excel is putting it below one another.

43
00:03:55,190 --> 00:03:58,780
The default layout is the compact format.

44
00:03:58,930 --> 00:04:04,700
I can close each main category and I can open it using these buttons.

45
00:04:04,880 --> 00:04:09,280
If I don't want to have those buttons, I want to create a more static report.

46
00:04:09,440 --> 00:04:16,980
I can take away these show plus minus button. If I click it again, it puts the buttons back. If you don't

47
00:04:16,980 --> 00:04:20,459
want the view to be this compact form, you can change that.

48
00:04:20,459 --> 00:04:27,420
Let's say I want the region to sit in its own column and my articles to sit in their own column.

49
00:04:27,420 --> 00:04:32,950
I can go to the design tab and change the report layout. So instead of compact form,

50
00:04:33,090 --> 00:04:38,760
I could go with tabular form. This puts region in its own column.

51
00:04:38,760 --> 00:04:46,390
Now also notice I have totals here. I have the Europe total, America total and a grand total.

52
00:04:46,410 --> 00:04:49,920
You can also turn these on and off as you see fit.

53
00:04:50,040 --> 00:04:55,890
If I didn't want to see them, I click on sub totals. Do not show sub totals. If I don't want to see the

54
00:04:55,890 --> 00:05:04,560
grand total, I go back to design, click on grand total and turn it off for rows and columns. To put it

55
00:05:04,560 --> 00:05:11,710
back just turn it on. To turn on the sub totals, click on show subtotal.

56
00:05:11,890 --> 00:05:18,660
The only thing left to do is to update the formatting of this. To add a thousand separator and to change

57
00:05:18,660 --> 00:05:20,440
the title of this.

58
00:05:20,460 --> 00:05:26,930
That's something that we already learned how to do in the previous section. Does this report now give

59
00:05:26,930 --> 00:05:34,190
us the answer to our question: Which product generates the most sales in America and which in Europe?

60
00:05:34,190 --> 00:05:40,910
Well, if you take a closer look at this data, we notice that this one has the most sales in Europe.

61
00:05:40,910 --> 00:05:46,330
And for America it's not that easy to see but it looks like it's this one.

62
00:05:47,570 --> 00:05:55,310
It would be great though to have these automatically sorted and show always the highest number on top.

63
00:05:55,390 --> 00:06:00,640
This way I don't have to go through the dataset to figure out which is the largest. To do that

64
00:06:00,640 --> 00:06:09,700
you just need to right mouse click, sort, sort largest to smallest. And we immediately get the largest data

65
00:06:09,760 --> 00:06:12,500
for each region on top.

66
00:06:12,570 --> 00:06:19,600
Notice something else that happened when I refreshed. The column size automatically adjusted.

67
00:06:19,630 --> 00:06:26,350
This is something you might want to avoid if you've fixed your report layout. To turn this auto adjust

68
00:06:26,440 --> 00:06:27,110
off.

69
00:06:27,220 --> 00:06:34,690
You need to go to the analyze tab, to pivot table options here and to turn off auto fit and then click

70
00:06:34,690 --> 00:06:35,870
on OK.

71
00:06:36,040 --> 00:06:41,410
This way every time you refresh, your pivot table size stays the same.

72
00:06:42,480 --> 00:06:49,420
It doesn't auto fit. Now you might be asking though: What if the source data changes?

73
00:06:49,580 --> 00:06:57,180
Does the order of the articles here change? So white for women is number two now.

74
00:06:57,380 --> 00:07:01,030
Let's go and change this in our raw data table.

75
00:07:01,070 --> 00:07:07,070
This one is white, let's put a very big number here.

76
00:07:07,300 --> 00:07:12,880
Go back to our report and refresh this to see if this switches to first place.

77
00:07:13,090 --> 00:07:18,190
Right mouse click, refresh. It switched to first place.

78
00:07:18,340 --> 00:07:22,640
You don't have to resort the entire dataset again. That's it.

79
00:07:22,650 --> 00:07:27,320
This report helps us answer our first question. In the next lecture,

80
00:07:27,330 --> 00:07:34,620
let's take a look at how we can create multiple pivot reports and also how we can add calculations to

81
00:07:34,890 --> 00:07:35,820
our pivot tables.