1 00:00:02,260 --> 00:00:06,310 Let's take a look at some chart basics for a quick start. 2 00:00:06,310 --> 00:00:12,550 Here I have information on the different apps and their sales value for 2019. 3 00:00:12,550 --> 00:00:15,430 I want to show this with a chart. 4 00:00:15,640 --> 00:00:24,120 The fastest way to do this is to click anywhere inside the data set and use the shortcut key Alt+F1. 5 00:00:24,220 --> 00:00:27,040 This inserts a column chart. 6 00:00:27,040 --> 00:00:33,310 The reason it inserts a column chart and not another chart type is because when you install Excel first 7 00:00:33,640 --> 00:00:36,440 the default chart is a column chart. 8 00:00:36,490 --> 00:00:40,640 You can change that and I'm going to show you in a second how you can do that. 9 00:00:40,660 --> 00:00:43,110 So that was Alt+F1. 10 00:00:43,110 --> 00:00:49,140 It inserts a chart in the current sheet. If you want to insert a chart as a separate sheet, 11 00:00:49,170 --> 00:00:56,520 in a separate chart sheet, you have to press F11 instead. That created a separate sheet. 12 00:00:56,530 --> 00:00:58,340 It's not a normal worksheet. 13 00:00:58,510 --> 00:01:00,470 It's a chart sheet. 14 00:01:00,470 --> 00:01:06,240 I personally prefer to insert my charts in the same worksheet where my dataset is. 15 00:01:06,240 --> 00:01:12,610 And then once I'm done with the chart, I move it to another worksheet where I create my report. But I 16 00:01:12,610 --> 00:01:15,490 generally don't work with chart sheets. 17 00:01:15,490 --> 00:01:19,930 I don't want to have one entire chart taking up the entire sheet. 18 00:01:20,140 --> 00:01:24,650 If you want to remove that tab, if you don't want it just right mouse click 19 00:01:24,670 --> 00:01:29,100 and select delete and that's gone. 20 00:01:29,310 --> 00:01:33,360 Now let me show you how you can get to this from the ribbon. 21 00:01:33,390 --> 00:01:40,830 So again just click anywhere inside the data set, go to insert and right here you see the different chart 22 00:01:40,830 --> 00:01:41,600 types. 23 00:01:41,670 --> 00:01:44,690 The first one up here that's the column chart. 24 00:01:44,700 --> 00:01:52,020 All I have to do is click on this dropdown and then select the type of column chart I want. And immediately 25 00:01:52,020 --> 00:01:59,490 I see the preview down here so when my mouse rolls over this I can see the different charts. So if I come 26 00:01:59,490 --> 00:02:00,750 down to the 2D bar. 27 00:02:00,780 --> 00:02:03,120 My preview shows the 2D bar. 28 00:02:03,210 --> 00:02:06,690 I can also go to recommended charts here. 29 00:02:06,690 --> 00:02:14,080 I get some recommendations from Excel showing me the different chart types that might fit this dataset 30 00:02:14,970 --> 00:02:16,170 If I like one of them 31 00:02:16,170 --> 00:02:17,690 I just have to click on them. 32 00:02:17,750 --> 00:02:23,350 It is going to insert it in my worksheet. 33 00:02:23,390 --> 00:02:26,130 Let's go back to recommended charts. 34 00:02:26,170 --> 00:02:29,560 So that was under insert, recommended charts. 35 00:02:29,600 --> 00:02:38,270 I can also click on all charts right here and view all the different chart types that Excel has to offer. 36 00:02:38,300 --> 00:02:45,740 Now whenever I select one of these, it kind of shows me a preview of how my end chart is going to look 37 00:02:46,070 --> 00:02:53,470 based on the data set that I have. Now remember I said that the default chart type was a column chart. 38 00:02:53,840 --> 00:02:59,390 Column is right here. On the first clustered column option, right mouse click. 39 00:02:59,600 --> 00:03:04,430 I see set as default chart and there's a tick mark in front of it. 40 00:03:04,460 --> 00:03:10,670 If my default chart is let's say a line chart because that's the type of chart I use often in my 41 00:03:10,670 --> 00:03:11,720 reports. 42 00:03:11,720 --> 00:03:17,610 All I have to do is to right mouse click here and select set as default chart. 43 00:03:17,630 --> 00:03:20,960 So now it gets the tick mark right here. 44 00:03:20,960 --> 00:03:28,630 This means that the next time I use the shortcut key Alt+F1, it's going to insert a line chart. 45 00:03:28,630 --> 00:03:35,480 Right now I'm going to do cancel. And now use the shortcut key alt+F1 and I get a line chart as 46 00:03:35,480 --> 00:03:38,760 my default. 47 00:03:38,790 --> 00:03:45,230 Let's go back to our column chart and take a look at some additional chart options that we have. 48 00:03:45,230 --> 00:03:52,950 I'm just going to delete the line chart here. To bring up the chart options, we just have to first 49 00:03:53,040 --> 00:03:54,090 activate the chart. 50 00:03:54,090 --> 00:03:57,540 So when I click on it we get two tabs up here. 51 00:03:57,570 --> 00:04:01,750 One is called "Chart Design" and the other is called "Format". 52 00:04:01,760 --> 00:04:08,400 A lot of these options are also available when you right mouse click on the different elements in 53 00:04:08,400 --> 00:04:09,470 the chart. 54 00:04:09,510 --> 00:04:16,410 If I come down here and right mouse click. I see select data, major grid lines, minor grid lines and some 55 00:04:16,410 --> 00:04:19,529 of them are available right here. 56 00:04:19,529 --> 00:04:26,700 So we see select data is here and we can take a look at charts elements and we have gridlines, primary 57 00:04:26,700 --> 00:04:33,990 major vertical, minor vertical and so on. Now these two ways are not the only ways you can get to chart 58 00:04:34,020 --> 00:04:34,890 options. 59 00:04:34,890 --> 00:04:37,770 You also have these options right here. 60 00:04:37,860 --> 00:04:45,660 You can go to grid lines and you get the same options that we saw up here. Under color, you can change 61 00:04:45,660 --> 00:04:54,330 the style or the color scheme of your charts. Under filter, you can choose to filter out some elements 62 00:04:54,660 --> 00:04:57,840 if you don't want to see them in your chart. 63 00:04:57,840 --> 00:05:06,060 I'm just going to set this back to select all and apply. Now in addition to this, you can double click 64 00:05:06,060 --> 00:05:10,950 on any element here and you get more options on the side here. 65 00:05:10,950 --> 00:05:18,270 Let me just drag this and put it close to our chart and take a look at some of the options we have 66 00:05:18,270 --> 00:05:18,900 here. 67 00:05:18,900 --> 00:05:26,790 Up here we have some series options we can control the fill and the border of the series and we have 68 00:05:26,790 --> 00:05:33,150 some more properties available here that we can adjust. Now the different elements have different types of 69 00:05:33,180 --> 00:05:34,410 options available. 70 00:05:34,410 --> 00:05:42,350 If I click on the axis instead, I get this time axis options and tick marks, labels, numbers. 71 00:05:42,360 --> 00:05:48,000 These weren't available when I was in the series view. 72 00:05:48,000 --> 00:05:55,260 In this view I didn't have that many options. So these options are very specific to the type of chart element that 73 00:05:55,260 --> 00:05:56,410 we're dealing with. 74 00:05:56,550 --> 00:06:04,980 Now you can click on them here and you see the options here. Or, you can click on this dropdown and see 75 00:06:05,010 --> 00:06:08,140 the different elements that make up the charts. 76 00:06:08,160 --> 00:06:13,050 So plot area that's this area inside the chart. 77 00:06:13,170 --> 00:06:15,060 The chart area 78 00:06:15,060 --> 00:06:21,560 that's the entire chart object. My series, that was series sales 2019. 79 00:06:21,660 --> 00:06:23,910 That's this series right here. 80 00:06:26,110 --> 00:06:30,310 Now let's take a look at some basic formatting for the series. 81 00:06:30,460 --> 00:06:34,090 The main series options are right here. 82 00:06:34,180 --> 00:06:41,430 I could decide to reduce the gap width. So let's say these bars are too thin and they're too far apart. 83 00:06:41,500 --> 00:06:49,180 I can reduce the width between them by reducing this number, let's say to 100 percent. 84 00:06:49,180 --> 00:06:54,670 The bars get thicker and the distance between the bars becomes smaller. 85 00:06:54,730 --> 00:07:01,070 I could change the color of the series by changing the fill options here. 86 00:07:01,160 --> 00:07:06,680 Let's select solid fill and change the color to another color. 87 00:07:06,680 --> 00:07:14,770 Now you also have the option to change the color by going to format and selecting the different fill 88 00:07:14,770 --> 00:07:16,700 option from here as well. 89 00:07:16,900 --> 00:07:21,200 It's just different ways of getting to the option that you need. 90 00:07:22,200 --> 00:07:27,070 If you want to remove any element of the chart you just have to click on it. 91 00:07:27,090 --> 00:07:33,690 Let's say I want to remove the grid lines, I just activate them and then I press the delete key. 92 00:07:33,810 --> 00:07:41,370 If I wanted to add data labels to these, I just have to click on the entire series so just click once 93 00:07:41,370 --> 00:07:47,360 to select the whole series, right mouse click and add data labels. 94 00:07:47,400 --> 00:07:55,000 This looks a bit crowded so I'm just going to expand my chart here and delete the y axis. 95 00:07:55,020 --> 00:08:01,470 Just activate it and delete it here to give these numbers a bit of breathing space. 96 00:08:01,470 --> 00:08:05,190 Now what about formatting of the numbers here. 97 00:08:05,190 --> 00:08:12,180 Let's say I want to add the thousand separator here. By default, the formatting of data labels is derived 98 00:08:12,330 --> 00:08:15,240 from the formatting of the data in the cells. 99 00:08:15,390 --> 00:08:20,880 So to change that, all I have to do is change the formatting of these. 100 00:08:20,880 --> 00:08:22,290 I'm going to use the shortcut key. 101 00:08:22,290 --> 00:08:30,090 Control+1, go to number, use a thousand separator and 0 decimal places. 102 00:08:30,090 --> 00:08:36,240 If I'm still not so happy with the labels looking like this that they're too much stuck together, I can 103 00:08:36,240 --> 00:08:38,130 position them differently. 104 00:08:38,190 --> 00:08:44,250 To do that I first have to activate the labels so just click on one of them to select the whole thing. 105 00:08:44,250 --> 00:08:49,770 Make sure you don't double click because the moment you double click, you're only selecting that one 106 00:08:49,860 --> 00:08:50,490 label. 107 00:08:50,490 --> 00:08:58,560 I'm just going to click away, click once to select everything, go to label options here and this time 108 00:08:58,650 --> 00:09:05,500 I'm going to select "inside end". Now I also want to turn this around. 109 00:09:05,500 --> 00:09:14,260 Click on the size and properties options here. Under alignment, change the text direction to 110 00:09:14,260 --> 00:09:16,220 270 degrees. 111 00:09:16,240 --> 00:09:21,500 I'm just going to change the color of my series so that I can read the numbers better. 112 00:09:21,580 --> 00:09:22,720 I'll just make these lighter. 113 00:09:22,720 --> 00:09:25,720 You can also make the labels lighter. 114 00:09:25,720 --> 00:09:28,010 Just make sure you don't make it white. 115 00:09:28,030 --> 00:09:33,450 If you have columns that are too small because then you can't read the whole number. 116 00:09:33,460 --> 00:09:34,690 So just go here. 117 00:09:34,690 --> 00:09:43,060 In this case with a lighter grey. Ok so it takes some time to find where the options are. 118 00:09:43,060 --> 00:09:50,320 What I suggest you do after this lecture is to take some time, follow what I just did. But then also 119 00:09:50,320 --> 00:09:55,100 go through in a bit more detail what these different options do. 120 00:09:55,180 --> 00:09:58,940 For example, what options are available for the axis. 121 00:09:58,960 --> 00:10:01,520 Click on axis options and check them out. 122 00:10:01,660 --> 00:10:04,000 What options are available for data labels. 123 00:10:04,000 --> 00:10:04,810 Click on them. 124 00:10:04,810 --> 00:10:07,480 Check these out. For the series as well. 125 00:10:09,710 --> 00:10:14,830 The title of your chart is automatically derived from the series. 126 00:10:14,840 --> 00:10:20,360 If you just have one series. You can type it in manually as well. 127 00:10:20,360 --> 00:10:24,290 You just have to highlight it first and then just start typing. 128 00:10:24,290 --> 00:10:25,250 So just sales, let's say. 129 00:10:25,250 --> 00:10:31,190 What you can also do though is to connect it to a cell. 130 00:10:31,270 --> 00:10:39,210 First activate the chart title then immediately go to the formula bar and type in an equal 131 00:10:39,410 --> 00:10:47,840 and then use a cell reference. In charts you can not type a formula as the title. I can't go and say 132 00:10:47,840 --> 00:10:52,280 equals this cell and this cell. 133 00:10:52,460 --> 00:10:56,510 If I press enter, I'm gonna get an error. 134 00:10:56,510 --> 00:10:59,870 It just accepts one cell reference. 135 00:10:59,870 --> 00:11:07,010 So if you do want to connect different cells together, you have to do that in a cell using any type of 136 00:11:07,010 --> 00:11:08,600 formula that you like. 137 00:11:08,600 --> 00:11:09,950 So let's say here. 138 00:11:10,060 --> 00:11:13,870 I would type in the formula equals productivity division. 139 00:11:13,970 --> 00:11:23,390 I'm going to add a colon, space, quotation mark then "&" and combine it with this and press enter. 140 00:11:23,420 --> 00:11:26,030 Now this is going to update automatically. 141 00:11:26,030 --> 00:11:36,640 So once I get 2020 data, the chart title is going to be updated automatically. 142 00:11:36,650 --> 00:11:42,560 Now let's say I wanted to quickly change the chart type and decide I don't want a column chart. 143 00:11:42,740 --> 00:11:45,020 I want to have a bar chart. 144 00:11:45,020 --> 00:11:51,440 All I have to do is right mouse click on the chart and select change chart type. 145 00:11:51,440 --> 00:11:58,620 You can also do that by going to chart design and selecting change chart type from here. 146 00:11:58,640 --> 00:12:01,630 Now let's go and take a look at a bar chart. 147 00:12:01,640 --> 00:12:06,060 Let's say I want this one. I just click on it and then click on OK. 148 00:12:06,290 --> 00:12:07,850 And I have my bar chart. 149 00:12:07,870 --> 00:12:13,580 But this time I don't want to have the data labels like this so I'm going to activate the data labels, 150 00:12:13,910 --> 00:12:21,800 go to label options and click on "outside end". And I'm going to turn them around to the way they originally 151 00:12:21,800 --> 00:12:24,080 were which was horizontal. 152 00:12:24,080 --> 00:12:26,590 Again, if I want to make these bars thicker. 153 00:12:27,230 --> 00:12:33,860 Basically if I want to reduce the gap width between them, all I have to do is reduce the gap width further. 154 00:12:34,070 --> 00:12:40,410 Notice that I'm always clicking on the element first that I want to format. 155 00:12:40,460 --> 00:12:49,530 Otherwise, I'm not going to have the correct options. Type in 40, press enter and my bars are thicker. 156 00:12:49,550 --> 00:12:52,160 To move a chart to another sheet. 157 00:12:52,160 --> 00:13:02,310 You can either use Control+X to cut the chart, go to your new sheet and press Control+V. Another way to 158 00:13:02,310 --> 00:13:09,900 move your chart is to right mouse click on the chart area and select move chart. You have the option 159 00:13:09,930 --> 00:13:16,980 of creating a new chart sheet for it or you can move it as an object inside another sheet. 160 00:13:16,980 --> 00:13:23,790 Let's say I want to move it to sheet 1, I select sheet 1 from the dropdown, click on ok and it 161 00:13:23,790 --> 00:13:25,530 moves it right there. 162 00:13:25,530 --> 00:13:30,270 That's the basics of working with Excel charts. In the next lecture, 163 00:13:30,270 --> 00:13:37,350 let's take a look at creating a chart that allows us to compare two different series with one another.