1 00:00:01,940 --> 00:00:08,860 Now let's take a look at how we can add more series to an existing chart for comparison charts. 2 00:00:08,870 --> 00:00:14,740 What I mean by comparison charts are cases where we want to compare one category with another. 3 00:00:14,750 --> 00:00:21,860 For example, actual sales to budget or actual to previous year data. We're going to be taking a look 4 00:00:21,860 --> 00:00:23,570 at two different methods. 5 00:00:23,570 --> 00:00:28,500 In this lecture, our method one is to take a look at this in one chart. 6 00:00:28,640 --> 00:00:35,510 We're going to show the two different categories in two different series in the same chart. And method 7 00:00:35,540 --> 00:00:38,360 two is to create two separate charts. 8 00:00:38,360 --> 00:00:43,520 That's something we're going to cover in the next lecture. One chart is going to show the actual sales 9 00:00:43,910 --> 00:00:50,420 and the other chart is going to show the variance to previous year. And we're going to be grouping these 10 00:00:50,420 --> 00:00:51,780 two charts together. 11 00:00:51,860 --> 00:01:00,650 So they look like they're one chart. Let's start off with a simpler method to show these two categories 12 00:01:01,010 --> 00:01:02,390 in one chart. 13 00:01:02,570 --> 00:01:07,250 First off, let's say I just have information on app and actual sales. 14 00:01:07,310 --> 00:01:13,600 I don't have this information yet so I'm just going to plot a chart based on this, highlight the data 15 00:01:13,610 --> 00:01:22,190 set, go to insert, go to the charts part and select this time a clustered bar chart. 16 00:01:22,190 --> 00:01:28,820 I'll just bring it over here and make it a bit thinner which is ultimately going to depend on the size 17 00:01:28,850 --> 00:01:31,280 I have available in my report. 18 00:01:31,700 --> 00:01:38,810 Let's just delete the vertical grid lines, right mouse click, add data labels and let's also reduce the 19 00:01:38,810 --> 00:01:46,200 gap width. Double click the bars to bring up the options or I can use the shortcut key Control+1. 20 00:01:46,280 --> 00:01:54,350 I'm just going to drag this and put it right beside the chart, for gap width reduce this to 40 percent. 21 00:01:54,920 --> 00:01:55,220 . 22 00:01:55,220 --> 00:01:58,520 Also for the labels let's make them a bit smaller. 23 00:01:58,550 --> 00:02:02,540 Just go to the Home tab and let's reduce the font size 24 00:02:02,540 --> 00:02:05,640 and I can remove my horizontal axis. 25 00:02:05,720 --> 00:02:12,070 You might notice that when you create a bar chart, the categories are kind of reversed. 26 00:02:12,230 --> 00:02:14,980 So blend here is on the top of my data. 27 00:02:15,110 --> 00:02:21,740 It ends up being on the bottom of my bar chart. WenCal is on the bottom of my data set, it ends up being 28 00:02:21,860 --> 00:02:22,950 on top. 29 00:02:22,980 --> 00:02:30,170 You might want to reverse this. To do that just activate the series. Click on it, go to the axis 30 00:02:30,200 --> 00:02:37,760 options right here and here you just have to put a checkmark for categories in reverse order. 31 00:02:37,760 --> 00:02:41,980 That switches things around and it puts blend on top. 32 00:02:42,400 --> 00:02:42,630 Okay. 33 00:02:42,650 --> 00:02:44,680 So we created this chart. 34 00:02:44,690 --> 00:02:47,060 Now we take it over to our boss and let's say 35 00:02:47,060 --> 00:02:50,810 He says, Could you add in the previous year data. 36 00:02:50,900 --> 00:02:54,210 So we get the previous year data right here. 37 00:02:54,230 --> 00:03:00,110 Now let me show you three different ways of adding this to an existing chart. 38 00:03:00,110 --> 00:03:03,300 First method, just right mouse click on your chart, 39 00:03:03,320 --> 00:03:08,690 select Data. On the left hand side you can see the series that are plotted. 40 00:03:08,720 --> 00:03:10,410 That's my actual cells. 41 00:03:10,430 --> 00:03:16,650 If I wanted to edit it, I just have to click on it, click on edit and I can see the different elements. 42 00:03:16,660 --> 00:03:24,350 So this is basically the title of my series which is called actual sales and my series values are the 43 00:03:24,350 --> 00:03:27,780 values right here. On the right hand side, 44 00:03:27,890 --> 00:03:35,360 I can see the category labels. Now I don't need to edit this or remove. I want to add a new series so 45 00:03:35,360 --> 00:03:38,270 I'm going to click on Add. For series name, 46 00:03:38,440 --> 00:03:43,380 Select the header here, previous year's sales and for series values, 47 00:03:43,520 --> 00:03:47,770 I have a default in there that I need to remove first. 48 00:03:47,780 --> 00:03:54,830 Just press backspace to remove it or directly highlight the range that you want included. 49 00:03:54,830 --> 00:03:59,300 And that's going to add it to your chart and then click on ok 50 00:03:59,410 --> 00:04:00,280 and ok 51 00:04:00,320 --> 00:04:01,710 and you'd be done. 52 00:04:01,880 --> 00:04:04,520 Now I want to show you another way of doing this. 53 00:04:04,530 --> 00:04:11,750 I'm actually going to select the series and remove it from here and click on Ok. A second way of adding 54 00:04:11,750 --> 00:04:16,430 a series to your chart is to highlight the data and copy it, 55 00:04:16,459 --> 00:04:17,950 click on your chart, 56 00:04:18,050 --> 00:04:23,450 Go to Home, paste, and paste the values in your chart. 57 00:04:27,070 --> 00:04:29,470 Now let me show you a third way of doing this. 58 00:04:29,470 --> 00:04:32,020 I'm going to press Control+z to go back. 59 00:04:32,050 --> 00:04:36,720 Let's now assume the dataset is sitting right beside the other data. 60 00:04:36,720 --> 00:04:39,010 So I'm just going to pull this over here. 61 00:04:39,070 --> 00:04:45,640 If your setup is like this, all you have to do is to activate your chart. So just click on it and then 62 00:04:45,730 --> 00:04:48,010 expand the data set 63 00:04:48,190 --> 00:04:50,950 and that adds the series to the chart 64 00:04:50,950 --> 00:04:53,050 and let's just tweak one thing. 65 00:04:53,170 --> 00:05:00,300 Let's add a little bit of distance between the two bars because now they're stuck together. To do that 66 00:05:00,400 --> 00:05:04,800 We need to change not the gap width but the series overlap. 67 00:05:05,230 --> 00:05:08,560 When the overlap is zero it means they're stuck together. 68 00:05:08,560 --> 00:05:15,490 And if we put something like minus 10, that adds a little bit of a gap between the two series. 69 00:05:15,490 --> 00:05:18,700 I'm just going to expand this to make the values easier to read. 70 00:05:18,700 --> 00:05:24,700 Some information is missing from here. We don't have a chart title so we can add elements very easy by 71 00:05:24,700 --> 00:05:27,790 just by clicking the plus Icon here. 72 00:05:27,910 --> 00:05:31,020 Let's add a chart title to this. 73 00:05:31,060 --> 00:05:33,000 We also don't have a legend. 74 00:05:33,040 --> 00:05:34,730 Let's go back here. 75 00:05:34,780 --> 00:05:37,420 Add a tick mark for the legend. 76 00:05:37,420 --> 00:05:38,890 I don't really like it here. 77 00:05:38,890 --> 00:05:41,920 I prefer the legend to always be under the title. 78 00:05:41,920 --> 00:05:45,520 Just click on it to activate the options here. 79 00:05:45,520 --> 00:05:47,900 Under the main legend options. 80 00:05:48,010 --> 00:05:50,430 Let's switch it to the top. 81 00:05:51,280 --> 00:05:51,630 Okay. 82 00:05:51,640 --> 00:06:00,300 And then the last type of formatting is to change the color of the series as you see fit. But I'm actually 83 00:06:00,300 --> 00:06:06,690 going to do one last thing and that's to sort the data based on actuals. 84 00:06:06,690 --> 00:06:13,590 The app with the highest sales is going to be first, and then the second highest and so on. 85 00:06:13,590 --> 00:06:17,110 All I have to do is to sort my data sets. 86 00:06:17,130 --> 00:06:21,660 I'm just going to right mouse click somewhere in the actual sales column. 87 00:06:21,660 --> 00:06:25,550 Select sort, sort largest to smallest. 88 00:06:25,890 --> 00:06:30,120 That sorts the data based on the actual values 89 00:06:30,270 --> 00:06:32,310 but obviously this is static. 90 00:06:32,340 --> 00:06:40,100 So every time you get a new data, you need to resort these again. If you want them to be sorted automatically 91 00:06:40,140 --> 00:06:43,000 you can do that if you use formulas. 92 00:06:43,020 --> 00:06:46,330 Something we cover in the more advanced charting courses. 93 00:06:46,850 --> 00:06:47,080 Okay. 94 00:06:47,100 --> 00:06:52,770 So that was Method 1, comparing two different categories in one chart. 95 00:06:52,770 --> 00:06:58,590 In the next lecture let's take a look at method two, where we're going to create two charts that are grouped 96 00:06:58,590 --> 00:07:00,330 together as one.