1
00:00:01,819 --> 00:00:05,300
Now let's take a look at creating combination charts in Excel.

2
00:00:05,410 --> 00:00:11,470
By combination charts, I mean charts that include different series types in one graph.

3
00:00:11,510 --> 00:00:16,430
Based on this dataset where we have information on the different apps for productivity division,

4
00:00:16,610 --> 00:00:23,900
actual and previous year sales. What we've already done is to insert a clustered column chart or a bar chart.

5
00:00:23,900 --> 00:00:30,080
And let's say we do this, we show this to our boss and our boss takes a look and says: Well, could

6
00:00:30,080 --> 00:00:37,180
you change the series type for previous year to be a line instead of a column?

7
00:00:37,190 --> 00:00:38,300
How would you do this?

8
00:00:38,600 --> 00:00:40,900
Here's something you need to be aware of.

9
00:00:41,450 --> 00:00:45,660
And that's where you right mouse click on this chart.

10
00:00:45,680 --> 00:00:53,100
If you click here in the plot area and you right mouse click, you see the option change chart type.

11
00:00:53,330 --> 00:00:57,040
If I select this, I get to this view.

12
00:00:57,140 --> 00:01:05,540
If I go to line because I want previous year to be aligned, I see both series change to a line.

13
00:01:05,570 --> 00:01:12,890
So changing the chart type changes the type of each single series in that chart.

14
00:01:12,890 --> 00:01:16,970
That's not something I want. I want to change one of them.

15
00:01:16,970 --> 00:01:24,350
The view I actually need is the Combo view I see on the bottom. And the moment I select this I

16
00:01:24,350 --> 00:01:30,740
can see that previous year change to a line because that's the standard for combo view.

17
00:01:30,740 --> 00:01:34,420
I'm going to show you something that's going to take you here directly.

18
00:01:34,910 --> 00:01:41,700
I'm going to click on cancel now. Instead of clicking here and selecting change chart type, you right

19
00:01:41,700 --> 00:01:44,430
mouse click on any of these series.

20
00:01:44,480 --> 00:01:51,340
And if you're in Excel 2016 and above, it doesn't matter which series you select. Because the moment you

21
00:01:51,340 --> 00:01:57,290
right mouse click and you say change series chart type, so notice that we have a different option now.

22
00:01:57,440 --> 00:01:58,800
It's not chain chart type.

23
00:01:58,820 --> 00:02:01,310
It's change series chart type.

24
00:02:01,310 --> 00:02:07,030
We automatically are taken to combo view and Excel doesn't try to decide what we want.

25
00:02:07,040 --> 00:02:10,039
We can actually decide the type of series

26
00:02:10,039 --> 00:02:11,180
we want to have here.

27
00:02:11,420 --> 00:02:15,550
So that's the fastest way of getting there is to select any of these series.

28
00:02:15,550 --> 00:02:22,340
Even if I select actual and I right mouse click here and go to change series chart type, I still come

29
00:02:22,340 --> 00:02:28,700
to this combo view. And now I can decide based on these drop downs what type of series I want to have.

30
00:02:28,850 --> 00:02:35,270
So for previous year, we can go with a line or in this case let's go with option "Line with Markers".

31
00:02:35,540 --> 00:02:37,130
And then click on OK.

32
00:02:37,240 --> 00:02:43,100
We can make the usual adjustments to this. Let's bring up the options box here by pressing Control+1.

33
00:02:43,100 --> 00:02:45,050
For the legend position.

34
00:02:45,050 --> 00:02:47,030
I'm going to put it to top.

35
00:02:47,030 --> 00:02:53,220
I'm just going to fade out the gray color here a bit more. Under the line and fill options

36
00:02:53,270 --> 00:02:59,630
I'm going to go with a lighter gray for those grid lines. And we can also reduce the gap width for our

37
00:02:59,630 --> 00:03:00,410
series here.

38
00:03:00,410 --> 00:03:03,230
Let's just bring it all the way down.

39
00:03:03,290 --> 00:03:08,480
Let's say your boss takes a look at this and says that doesn't look so great.

40
00:03:08,480 --> 00:03:14,690
What if we take away the line and just show the previous year values as markers?

41
00:03:14,690 --> 00:03:16,510
How would you do this?

42
00:03:16,670 --> 00:03:18,680
You have different options here.

43
00:03:18,710 --> 00:03:24,920
You could do it quickly by first activating the series. You just have to click on it then go to format

44
00:03:24,920 --> 00:03:29,480
here, under shape outline, select no outline.

45
00:03:29,480 --> 00:03:37,730
Another option is to bring up the chart options on the side here, go to fill and line options and select

46
00:03:37,820 --> 00:03:39,590
no line from here.

47
00:03:39,590 --> 00:03:46,250
Now while we're in this view, we can also change the marker. If we're not happy with this round marker

48
00:03:46,610 --> 00:03:54,500
we can click on this marker icon here. Under marker option, select another type of built in option.

49
00:03:54,500 --> 00:04:00,890
So when I click this dropdown arrow here, you can see you have a few more options.

50
00:04:00,920 --> 00:04:05,780
You can also bring in your own image or icon from a file.

51
00:04:05,800 --> 00:04:13,310
It has to be small if you want to use it as an icon here or you can just make a selection from an existing

52
00:04:13,310 --> 00:04:13,550
one.

53
00:04:13,550 --> 00:04:20,459
Let's go with this line here and increase the size so it fits in a little bit better.

54
00:04:20,480 --> 00:04:23,900
But notice that every time you increase the size it gets longer

55
00:04:23,920 --> 00:04:26,150
but it also gets thicker.

56
00:04:26,180 --> 00:04:31,930
So that's one way of showing actual and previous year as different series.

57
00:04:31,940 --> 00:04:37,820
Now I'm going to show you a trick here because you can get really creative with the type of markers

58
00:04:38,210 --> 00:04:39,410
you can use.

59
00:04:39,410 --> 00:04:42,710
You're not just restricted to what you saw there.

60
00:04:42,770 --> 00:04:50,100
You can actually create your own or import your own here and copy and paste the marker in your chart.

61
00:04:50,120 --> 00:04:54,290
For example, let's say you have a really picky boss and they tell you

62
00:04:54,410 --> 00:04:57,710
well, I want this longer but not this thick.

63
00:04:57,710 --> 00:04:59,180
Here's what you can do.

64
00:04:59,210 --> 00:05:04,540
Go to insert, go to shapes and let's just draw out this line here.

65
00:05:04,560 --> 00:05:08,460
Let's change the color and change the thickness. A bit thicker

66
00:05:08,470 --> 00:05:11,840
but not as thick as what we currently have.

67
00:05:11,980 --> 00:05:18,280
Let's just make this even a bit thinner. Once you're happy with the type of marker you want to use.

68
00:05:18,280 --> 00:05:19,480
Click on it.

69
00:05:19,480 --> 00:05:21,090
Press Control+C

70
00:05:21,130 --> 00:05:27,790
Basically copy to clipboard, click on your existing markers to activate the entire series and press

71
00:05:27,970 --> 00:05:28,970
Control+V.

72
00:05:29,080 --> 00:05:35,290
That paste that image in your chart. So that basically becomes your new marker.

73
00:05:35,290 --> 00:05:40,720
Note If this is too long and you want to change it, you can change it here so I'm just going to make

74
00:05:40,720 --> 00:05:43,210
it a little bit thinner.

75
00:05:43,210 --> 00:05:45,940
Notice it's not a live image, right?

76
00:05:45,950 --> 00:05:51,250
If I change the way this image looks, I have to press Control+C again, click on the series and

77
00:05:51,250 --> 00:05:57,680
press Control+V. Here obviously you can get creative with the type of markers you want to use.

78
00:05:57,700 --> 00:05:59,640
So let's just do that.

79
00:05:59,650 --> 00:06:03,490
Let's just have some fun and insert a smiley here.

80
00:06:03,490 --> 00:06:06,840
I'm going to change the color to green, just to show you the concept.

81
00:06:06,850 --> 00:06:12,250
Obviously we're not going to be using this in our final report. But to get this in I'm going to copy

82
00:06:12,250 --> 00:06:17,060
this image click on the markers here and press Control+V.

83
00:06:17,200 --> 00:06:23,920
Notice that the marker here in the legend also changed to a Smiley. And if you're wondering whether these

84
00:06:23,920 --> 00:06:31,860
are dynamic or not, they are. Let's change the marker for Dasring. Instead of 5,500

85
00:06:31,860 --> 00:06:34,780
let's go with 10,000.

86
00:06:37,560 --> 00:06:38,600
And that jumped.

87
00:06:39,100 --> 00:06:45,760
OK so that's one way you can get this done. I'm just going to press Control+z to go back to our more serious

88
00:06:45,760 --> 00:06:46,360
report.

89
00:06:47,200 --> 00:06:52,110
Basically that's how you can create combination charts in Excel.

90
00:06:52,150 --> 00:07:00,010
Just remember right mouse click on any series to go to change series chart type and go to combo view.