1
00:00:00,090 --> 00:00:07,320
Let's take a look at how we can improve our pivot table reports by adding slicers to them.

2
00:00:07,350 --> 00:00:11,090
Slicers give us the ability to filter our data using buttons.

3
00:00:11,100 --> 00:00:17,970
They're basically a more user friendly version of using the pivot filters feature here.

4
00:00:17,970 --> 00:00:24,230
We're also going to take a look at adding a timeline to our report and also how we can work with dates.

5
00:00:24,390 --> 00:00:30,780
I've already created a sample report here that gives us the list of our articles or products and their

6
00:00:30,780 --> 00:00:31,740
sales value.

7
00:00:31,740 --> 00:00:40,380
I'm just going to label this as products and instead of grand total, let's go with total. The sales

8
00:00:40,380 --> 00:00:48,120
value that's associated to each product here is for all companies, all regions, and all dates.

9
00:00:48,150 --> 00:00:55,440
Let's improve this report by adding the ability to drill down to each region and also to drill down

10
00:00:55,440 --> 00:00:56,940
to the months.

11
00:00:56,940 --> 00:00:59,130
Let's start with region first.

12
00:00:59,610 --> 00:01:04,599
Well, one way is to add the region to the filter field right here.

13
00:01:04,620 --> 00:01:07,560
This gives us the ability to select our region.

14
00:01:07,560 --> 00:01:12,600
If you have many regions and you want to select more than one, just put a check mark beside select multiple

15
00:01:12,600 --> 00:01:15,780
items and you get the ability to select them.

16
00:01:15,840 --> 00:01:18,670
You get the ability to just look at one region.

17
00:01:18,670 --> 00:01:21,480
So this gives us a list for America

18
00:01:21,480 --> 00:01:24,060
or to look at all regions.

19
00:01:24,060 --> 00:01:28,190
Another way of doing this is to use a pivot slicer.

20
00:01:28,190 --> 00:01:32,830
I'm just gonna kick region out and introduce a slicer for this.

21
00:01:32,830 --> 00:01:37,380
Go to the analyze tab and click on insert slicer.

22
00:01:37,380 --> 00:01:44,160
We get the list of all our categories and we get to decide for which category we'd like to add a slicer.

23
00:01:44,160 --> 00:01:49,140
In these case it's for region so just place a check mark beside it and click on OK.

24
00:01:49,440 --> 00:01:52,760
This gives us a unique list of all our regions.

25
00:01:52,900 --> 00:01:56,400
I'm just going to drag this and bring it closer to our data set.

26
00:01:56,400 --> 00:02:02,640
Now if I click on Europe, the data is filtered just for Europe. America, just America.

27
00:02:02,640 --> 00:02:06,300
I want to include everything, I just have to clear this filter.

28
00:02:06,300 --> 00:02:12,290
If I had many different regions here and I want to multiple select, I can activate multiple select.

29
00:02:12,580 --> 00:02:16,080
And this gives me the ability to select more than one item.

30
00:02:17,130 --> 00:02:23,110
Then notice that the moment I added a slicer, I got a new tab called slicer up here.

31
00:02:23,210 --> 00:02:29,300
I can make additional adjustments to the slicer. So I can change the style by clicking a style that fits

32
00:02:29,300 --> 00:02:30,740
my pivot table.

33
00:02:30,740 --> 00:02:34,690
I also have the ability to create a new slicer style.

34
00:02:34,820 --> 00:02:39,250
I can also decide the number of columns I want my buttons to be in.

35
00:02:39,310 --> 00:02:42,960
If I want them to be beside one another, I can increase this to two.

36
00:02:43,400 --> 00:02:49,210
I can decide on the button height, the width, and the height of the slicer.

37
00:02:49,220 --> 00:02:54,730
I can also just adjust it by using the mouse here. To change the header here.

38
00:02:54,770 --> 00:03:00,700
I'm going to go to slicer settings. For caption, add select region, click on ok.

39
00:03:00,720 --> 00:03:09,400
I'm just going to bring it and put it up here and just add some space to this.

40
00:03:09,410 --> 00:03:17,880
So now with this, we have the ability to select a specific region or to select all regions.

41
00:03:17,920 --> 00:03:25,180
Now let's take a look at how we can analyze the sales information by dates or by months. First off, which category here

42
00:03:25,300 --> 00:03:27,570
carries our date information?

43
00:03:27,670 --> 00:03:29,340
It's the document date.

44
00:03:29,380 --> 00:03:33,940
Let's go quickly to the raw data table and see how to date is captured.

45
00:03:33,940 --> 00:03:35,050
It's right here.

46
00:03:35,050 --> 00:03:40,730
It's recognized as a date and we have the month, day, and year information.

47
00:03:40,730 --> 00:03:48,580
So now let's go back to our report and see what happens if we drag the document date to our

48
00:03:48,610 --> 00:03:50,310
rows right here.

49
00:03:50,320 --> 00:03:52,110
Notice, we got months here.

50
00:03:52,120 --> 00:03:56,720
Let's just expand this and we have the document date below it.

51
00:03:57,100 --> 00:04:03,820
What Excel did automatically. The moment I dragged and dropped the date here is it created a new category

52
00:04:03,820 --> 00:04:09,770
called months. And we can see it if I scroll down, we see months right here. It didn't create one for

53
00:04:09,770 --> 00:04:13,740
year because I only have information for one year here.

54
00:04:13,990 --> 00:04:19,420
If your data set includes information for many years, you're also going to see a category for years

55
00:04:19,750 --> 00:04:23,100
automatically created and available for you to use.

56
00:04:23,110 --> 00:04:29,980
This gives me the ability to also drill down into each month to see the exact document date. If you wanted

57
00:04:29,980 --> 00:04:31,800
to create a detailed report.

58
00:04:31,810 --> 00:04:38,010
You can set it up this way. But in my example I want to create a slicer for the months.

59
00:04:38,020 --> 00:04:41,190
I'm not interested to drill down to the exact date.

60
00:04:41,410 --> 00:04:45,710
I'm just going to kick out the document date from this.

61
00:04:45,730 --> 00:04:49,110
This just gives me the report of the products by month.

62
00:04:49,150 --> 00:04:55,220
I'm also going to kick out months and instead add a slicer for it. So I'll go back to pivot table analyze

63
00:04:55,640 --> 00:05:01,670
and click on insert slicer. This time I'm going to put a checkmark beside months, click on ok.

64
00:05:01,740 --> 00:05:11,710
Now I get my list of months. Again, I get the ability to set the style. To decide on the number of columns

65
00:05:11,890 --> 00:05:16,230
I want months to be and to adjust the size of this

66
00:05:16,270 --> 00:05:22,210
so if it's my report. And notice that some months here are darker than the other ones.

67
00:05:22,210 --> 00:05:26,020
This means that these ones carry no data.

68
00:05:26,020 --> 00:05:28,920
You can hide this from view if you want.

69
00:05:28,960 --> 00:05:32,660
You can do that under slicer settings.

70
00:05:32,740 --> 00:05:39,550
Just put a check mark beside hide items with no data and then click on OK and they disappear from your

71
00:05:39,550 --> 00:05:40,740
list.

72
00:05:40,750 --> 00:05:48,340
Now I have the ability to drill down into the month and into a specific region. Or, I can view all

73
00:05:48,340 --> 00:05:50,700
regions and all months.

74
00:05:51,100 --> 00:05:58,120
If you're working with dates, you might also be interested to add a timeline instead of using a slicer.

75
00:05:58,190 --> 00:06:04,300
I'm just going to remove the slicer by selecting it and pressing delete. Now instead I'm going to add

76
00:06:04,330 --> 00:06:12,190
a timeline for my months. So go back to analyze, click on insert timeline. Excel notices that the only

77
00:06:12,190 --> 00:06:18,610
category here that includes dates is the document date so just put a check mark beside it and click

78
00:06:18,610 --> 00:06:18,900
.

79
00:06:18,910 --> 00:06:19,900
on OK.

80
00:06:20,110 --> 00:06:28,990
Immediately I get a timeline with a scrolling bar and I can select a specific month or more months if

81
00:06:28,990 --> 00:06:30,790
I hold down the shift key.

82
00:06:30,880 --> 00:06:41,340
I also get the ability to switch my view from months to quarters and also to years. And to days if I want

83
00:06:41,340 --> 00:06:45,890
to go in detail. For this report let's go with quarters.

84
00:06:45,990 --> 00:06:52,320
Notice also that for the timeline I got a new tab called timeline and I also can make adjustments

85
00:06:52,320 --> 00:06:59,340
to this. I can hide the header if I want. I can hide the scrolling bar down here.

86
00:06:59,340 --> 00:07:00,540
If we don't need it.

87
00:07:00,690 --> 00:07:02,580
In this case I can hide it.

88
00:07:02,610 --> 00:07:06,320
Let's just expand this and make this smaller.

89
00:07:06,330 --> 00:07:10,650
You can hide the selection label and the time level if you want as well.

90
00:07:12,230 --> 00:07:14,160
And to make this fit your report.

91
00:07:14,190 --> 00:07:19,170
You can select another style or you can create your own style.

92
00:07:19,200 --> 00:07:26,570
Just go to new timeline style, put a name for your timeline and adjust it as you see fit.

93
00:07:26,580 --> 00:07:32,550
You get the ability to decide on the formatting of each timeline element. You can change the header,

94
00:07:32,880 --> 00:07:37,110
the whole time line, the selected time block, the unselected time block.

95
00:07:37,450 --> 00:07:42,680
Let's just change the whole timeline to not have a border around it.

96
00:07:42,720 --> 00:07:47,300
I'm going to go with none, click on OK. For the selected time block,

97
00:07:47,310 --> 00:07:53,850
I'm going to go to format and change it to a yellow color and click on OK and let's just give this a

98
00:07:53,850 --> 00:07:59,860
name and say OK. Now to activate it, I actually have to select it.

99
00:07:59,880 --> 00:08:01,700
Let's go back to timeline styles.

100
00:08:01,770 --> 00:08:03,750
That's the new timeline I created.

101
00:08:03,750 --> 00:08:04,950
Click on it.

102
00:08:04,950 --> 00:08:12,860
And we have our new timeline without the border and our own specific formatting. Now I get the ability

103
00:08:12,860 --> 00:08:19,790
to select a specific region and take a look at the sales data by quarter. So that's how you can add

104
00:08:19,790 --> 00:08:26,660
pivot slicers and timeline to your pivot table reports to be able to filter the data easier.