1 00:00:01,990 --> 00:00:08,380 In the previous lecture we took a look at method one where we use one chart to compare two different 2 00:00:08,380 --> 00:00:10,600 categories with one another. 3 00:00:10,600 --> 00:00:16,690 In this lecture we're going to take a look at using two charts. But the two charts are not going to be 4 00:00:16,690 --> 00:00:19,320 showing the full values that we see here. 5 00:00:19,330 --> 00:00:22,440 The first chart is going to show the actual sales 6 00:00:22,480 --> 00:00:29,080 just like we see here. The second chart is going to show the deviation to previous year. And it's going 7 00:00:29,080 --> 00:00:31,730 to show it in absolute values. 8 00:00:31,780 --> 00:00:36,480 That's something we need to calculate first before we insert the chart 9 00:00:36,580 --> 00:00:39,150 but we already have the information and the first one. 10 00:00:39,220 --> 00:00:47,650 So let's just highlight this and insert a bar chart for the actual data. So we can quickly do some basic 11 00:00:47,650 --> 00:00:48,970 formatting to this. 12 00:00:49,150 --> 00:00:52,660 Let's reduce the chart size, make it thinner. 13 00:00:52,660 --> 00:01:00,540 We can remove the axis labels, remove the grid lines, add data labels to these and make them a bit smaller 14 00:01:01,240 --> 00:01:05,140 and also decrease the gap width. To bring up the properties, 15 00:01:05,140 --> 00:01:08,700 I can double click on this element where I can use the shortcut key 16 00:01:08,700 --> 00:01:10,260 Control+1. 17 00:01:10,270 --> 00:01:16,600 Now I'm just going to drag the properties and bring it right beside the chart and reduce the gap width 18 00:01:16,960 --> 00:01:19,150 to 40 percent. 19 00:01:19,630 --> 00:01:24,760 Okay so now the second chart is supposed to show the variance to previous year. 20 00:01:24,790 --> 00:01:28,550 That's something that I'm going to calculate right here. 21 00:01:28,570 --> 00:01:30,300 The formula is very simple. 22 00:01:30,490 --> 00:01:33,610 Actual minus previous year 23 00:01:33,610 --> 00:01:36,190 and we just send the formula down. 24 00:01:36,400 --> 00:01:39,810 Now I want to insert a second chart for this. 25 00:01:39,910 --> 00:01:46,960 So if you have values that are not stuck together, you have to hold down the control key to select the 26 00:01:46,960 --> 00:01:48,190 data separately. 27 00:01:48,190 --> 00:01:54,730 So in this case I want app and I want the variance data but I don't want actual or previous year sales 28 00:01:54,730 --> 00:01:56,290 data to be plotted. 29 00:01:56,290 --> 00:01:58,390 Don't forget hold down the Control key. 30 00:01:58,390 --> 00:02:05,560 Select the data then go to insert and insert the chart that you want. But I just want to show you something 31 00:02:05,560 --> 00:02:06,950 else here. 32 00:02:07,120 --> 00:02:12,370 If we want some of the formatting to remain so for example I change the gap with here. 33 00:02:12,370 --> 00:02:18,880 If I want to keep that I also have the option to copy and paste the chart and change its source data. 34 00:02:19,060 --> 00:02:24,940 I'm just going to click on the chart, Control+C, click on the side and Control+V. 35 00:02:24,940 --> 00:02:30,040 Now I'm going to put the second chart beside the first one and update the source data. 36 00:02:30,070 --> 00:02:38,160 Just right mouse click, go to select data, this time I want to edit the series, click on edit. Change 37 00:02:38,170 --> 00:02:46,210 the series name to D28 and the series values to the variance numbers right here and click on ok. 38 00:02:46,210 --> 00:02:53,410 The other thing I want to remove from this one are the labels here because I already have them 39 00:02:53,410 --> 00:03:00,850 on this side, I don't need them here. If I just press delete, I delete the labels but I also delete my line. 40 00:03:00,850 --> 00:03:08,200 You might be fine with that. In case you want to delete the labels but keep the line 41 00:03:08,200 --> 00:03:16,210 there is a separate option for that. First activate your axis, go to the options right here. Under labels 42 00:03:16,420 --> 00:03:21,170 for label position instead of next to axis, we want none. 43 00:03:21,250 --> 00:03:24,850 So that keeps the line but hides the labels. 44 00:03:24,910 --> 00:03:31,000 Now for the second chart I'm just gonna make it even thinner than the first one and I'm going to add 45 00:03:31,090 --> 00:03:32,840 the data labels to it. 46 00:03:33,250 --> 00:03:36,900 I'm also going to make the data labels smaller. 47 00:03:36,970 --> 00:03:41,580 There is one more adjustment I want to make to this and that's the color of the series. 48 00:03:41,680 --> 00:03:48,850 I would like to have the positive values formatted in a light green color and the negative values in 49 00:03:49,120 --> 00:03:50,620 light red color. 50 00:03:50,640 --> 00:03:58,690 The great thing about column charts and bar charts is that there is an inbuilt option for this. And that 51 00:03:58,750 --> 00:04:02,370 option is in the color options right here. 52 00:04:02,410 --> 00:04:08,910 First activate your series then go to the fill options, select "Invert 53 00:04:08,950 --> 00:04:16,029 if negative". That allows you to select two different colors, one for a positive, one for negative. 54 00:04:16,089 --> 00:04:21,670 The first option we see here is how we want the positive series to be formatted. I'm going to select 55 00:04:21,700 --> 00:04:23,680 a light green color here. 56 00:04:23,680 --> 00:04:28,540 Immediately I get a second option, that's for the negative series. 57 00:04:28,540 --> 00:04:33,000 I'm going to go with a light red and that's pretty much it. 58 00:04:33,050 --> 00:04:37,060 Now to make this look a bit better, make it look like it's one chart. 59 00:04:37,060 --> 00:04:43,310 I'm going to take away that chart border. So activate the chart, go to format and under shape outline, 60 00:04:43,600 --> 00:04:47,130 select no outline. That removes the border. 61 00:04:47,140 --> 00:04:52,690 I'm going to do the same thing for the second chart. To make this even easier to read 62 00:04:52,780 --> 00:04:59,260 I can activate the grid lines. But not the automatic default vertical grid lines. 63 00:04:59,260 --> 00:05:00,590 We don't want those. 64 00:05:00,670 --> 00:05:05,090 Instead we want the primary major horizontal grid lines. 65 00:05:05,100 --> 00:05:08,260 I'm going to do the same thing for the variance side 66 00:05:10,310 --> 00:05:12,980 and also make them less visible. 67 00:05:12,980 --> 00:05:16,000 I'll just go with a very light gray color in both cases. 68 00:05:16,040 --> 00:05:24,710 And to make them look like they are together let's just expand the plot area, so that's 69 00:05:24,710 --> 00:05:26,290 the plot area in there. 70 00:05:26,300 --> 00:05:30,090 This one is the chart area and I can control these separately. 71 00:05:30,120 --> 00:05:36,990 I'm just going to expand the plot area a little bit to make the line look like it's going through. 72 00:05:37,000 --> 00:05:39,980 As a last step, I'll group these together. 73 00:05:39,980 --> 00:05:41,610 Click on the first chart, 74 00:05:41,630 --> 00:05:44,570 Hold down Control, click on the second one. 75 00:05:44,570 --> 00:05:52,730 You can right mouse click in group. Or, go to shape format and group the different objects together as 76 00:05:52,820 --> 00:05:54,220 one object. 77 00:05:54,230 --> 00:06:00,740 Now whenever you're moving this, just make sure you move the grouped object and not the individual objects 78 00:06:00,740 --> 00:06:04,010 that are inside. All of this is also dynamic. 79 00:06:04,010 --> 00:06:08,990 Let's test on WenCaL, actual is less than previous year. 80 00:06:09,050 --> 00:06:13,460 Let's change that to 24,600. 81 00:06:13,730 --> 00:06:16,280 Keep your eye on the variance here. 82 00:06:16,310 --> 00:06:18,860 Keep your eye also on the value here. 83 00:06:18,860 --> 00:06:25,260 Let's press enter, the value change to 400 and the series color changed to green. 84 00:06:25,280 --> 00:06:25,470 Okay. 85 00:06:25,490 --> 00:06:32,510 That's another method you can compare two separate charts that are grouped as one.