Updated June 12, 2023
Part -16 – Excel Charts and Graphs
Download Excel Templates – Excel Charts and Graphs
Excel Charts and Graphs (without solution)
Excel Charts and Graphs (with solution)
Transcript For The Video – Excel Charts and Graphs
Video on Excel Charts and Graphs: In this video tutorial, we are going to learn functions regarding Microsoft Excel, charts, and graphs. We will also try to understand why pictures speak more than thousand words using these features, and one of the ways of doing that is going back to the case study; where we had lot of data, and as we could see that, in this list of employees, these were coming from different departments, and were from different genders of course and what we found earlier was that the bifurcation in terms of departments was something like this- marketing- 7, IT- 4, Finance-4 and support as 4.
So for this, let us move to sheet number seven, and let’s try to kind of populate this data. So we are going to make charts in the sheet seven, and one thing which is first and foremost is to have data on sheet seven. For this, we can do two things, we can either manually populate this one by one, or a better approach would be to link sheet seven to sheet one where your original data lies. So, that’s what we are going to do. So, in order to do this kind of references, what you need to do here is press equal to and go to sheet one, select C30 in sheet one because this is the one which we really want to populate and press enter. So the moment you do that, you will find that the cell corresponding to sheet one C30 gets populated or linked in sheet seven. Likewise, we can go back to sheet one and link IT, and press enter. So that’s how you know we can link. We can also make use of copy and share function in this case. Since I have linked, let’s say, marketing, I can just copy this link and press this across by right-clicking and pasting it. So in this way, I have all the data with me in terms of the departments. Can I press this data and pull that across in the next row? Yes, we can because that’s what we have learnt earlier in the video references linkages. So this is how you know it works. One caution which I really want to make all of you understand is that in this set of cross-references, what happens is, let’s say if I try to link this equal to sheet one, and let’s say I want to link this to XYZ company, ok. So I need to press enter in order to display what is written in sheet one B2. Now let’s say if I don’t press enter, and I will redo it in this cell, and let’s say I select this cell B2, and I don’t press enter, and before I do that, I go back to sheet seven, and then I press enter, see what happens. The problem is that instead of selecting sheet one when I click on sheet seven again, it goes back to sheet seven and references from the original one. Now let us plot this graph on the data which we have just referenced, so select this data, which is the department data as well as the number of employees data, and go to insert, and inside the insert tab, you will find there is a box which contains details of charts. Now, in this details of chart, you will find that there are different varieties which are mentioned in terms of what kind of charts you want. When we click on column, you will find that there are two-dimensional, three-dimensional, cylindrical charts, conic charts, pyramid charts; under the line, we have two-dimensional charts, and then you know, charts with markers. We have pie charts; we have bar charts; we have area charts, scattered charts, you know, other charts as well. So let us plot the first one, the basic one, which is two-dimensional charts, which we call that as a clustered column chart. So I can just press enter, and moment I do that, you know, I see that you know the chart has been prepared. Now on the right-hand side, you will find this is the series one, the marketing, IT, finance, support. This comes under the your horizontal bar, and the number of employees are actually on the vertical bars. There is no heading as such on this chart, so let’s make this chart bit more intuitive. So first thing first, you know I don’t want this series one on the right-hand side, so what I do is I can select this series one and click right-click and delete it. So this is what I have done at the first place. I want to make this chart more self-intuitive. So for doing that, let us label this chart. Go to layouts, and we need to select the chart title, so you can have different types of chart titles here. You can have above the chart, centered overlay charts, or maybe you know as above the chart, you can also have that. So let’s have it above the chart. And we will call that as department wise employees. This is the title of the chart, now this title of the chart may look too bit as of now, so if you want to reduce that font, we can do that at once. So select this title, and you can go here and reduce the font as well. So let me choose font size as twelve as of now. The other thing is, what I need to do is, let’s say, these are the vertical titles which I need to add on the left-hand side. So, again I can go to the layouts; these are the axis titles, this is the primary axis. I can have different kinds of titles; it can have rotated title. Let’s say rotated title would be the one which looks a bit rotated, employee count. This is the rotated one. Let’s say if I am not too keen on the rotated one, we can choose a vertical employee count, or you may choose a horizontal title as such. Let me for the sake of convenience here, let me use a rotated title as of now. So, on this side, we have the department, here we have the employee count, and the other things that we can do in this chart is, let’s say we can change the color of the bars, let’s say I am not satisfied with blue color option which we used in the chart. So if I want to change this, I can go to shape fill from the format menu, and I can change the colors. So let’s say if you want to have green or grey, so let’s choose grey for the time being. So this is what let’s say, you know, we want it in terms of the color. The other things which are available here is the data labels, you know the data labels are basically the ones which, if I press enter, you will understand, data labels are the count in terms of the departments, so against this data, marketing there were seven employees, so data labels when it is used as centered it show in between, in the middle. Likewise, you can have inside end; you can play with this set of options, inside base, or let’s say outside end. So let me keep it in outside end of the bar because this more, looks intuitive as such. The other thing which is available in the menu bar is the, under the layout section, is the data table, so do you want to show the data table? if you press ok, you will find that, under each department, you will find that there is a data table associated with it. So this is also another twist to the table which you want to create. Let me go back to the one where we came from. The horizontal lines here are basically known as the grid lines, ok? so these are the vertical grid lines, so, which are already visible. However, if you want to show the horizontal grid lines, you can also do that.
CHARTS PART 2
Script-
So the other thing which is very important, it should be the data tables. If you want to actually add a table to your chart, you can use data tables, the table on which the chart was made. If you want to have it inside chart itself, you can actually use show data tables from the data table menu, and as you can see, the table which is here gets populated in the chart itself. So which becomes a part of the chart, and you can directly show that on your presentation or your report. Likewise, other things which are important is from the point of view of some kind of layout. You may not like a design that, let’s say, employee count is on the left-hand side; you may want it on the right-hand side. Likewise, you may not want horizontal axis to be visible at all. So all these things can be managed at the axis, and let’s say, you know, under the primary axis if I want to show the axis in thousands or millions or billions or logscales, you know that can be done. Likewise, for the horizontal axis, I don’t want the horizontal axis to be shown from left to right. Let’s say I want to show it from right to left; I can do that. So as you can see, you know the axis should come right to left, so if you want to play with this and find that this is adding value, you can choose set of options for your set of graph. Likewise, other things which is again can be important for many would be the grid lines. This grid lines provides intuitive way to understand where the charts or the values are. Now the grid lines is basically this one, which I have selected. This are the horizontal grid lines; the vertical grid lines here are missing, so we can add these vertical grid lines, and you will find that these vertical grid lines are now visible. Likewise, this vertical grid lines which we have selected were the major gridlines. If you want, you can have some minor gridlines as well, which can show a unit between the larger set of units. So these grid lines are also important way to probably make your chart look more intuitive. On one side, you may want to include this. On the other side, you may want to really delete this. So how do you go about deleting this grid lines? So it’s very easy; you just need to select this grid lines; just go on any grid line and just click it and just right-click and press delete. So as you can see, this grid line which were horizontal grid lines that were selected, got deleted. Likewise, let me select this horizontal axis minor grid lines and right-click and delete, so the moment I delete it, my graph looks even cleaner in this case. Do this is another thing which you can kind of play with. The other aspect I kind of forgot telling you initially was with respect to the chart area, so this was the chart area. This is the default chart area which is visible. You can play with this chart area; you can increase it from the bottom then decrease the size. You can increase the size as per the requirement of your presentation or the report that you are preparing, so you can have that as well. Likewise, the other thing which is important is the plot area. The outside one is the chart area which we were kind of manually adjusting, the inside one is the plot area. You can also adjust the plot areas by selecting different options or different sizes so you can resize the plot areas as per your convenience. So that’s again one of the important things that you want to kind of showcase. Another aspect of formatting the chart could be thing like, let’s say, if you want to decrease the font size of axis here, you can just select these and go to home menu, and you can decrease the sizes. let’s say I want to decrease the size of this font. I can select those set of axis and decrease or increase the sizes accordingly and likewise, let’s say I want to highlight a particular bar and not all sets of bars. let’s say I want to color marketing with, let’s say, blue color, and the remaining I just want them to be kept in grey, so how can I do that? I can actually click once if I click once I find that all the bars are selected at once. If I click on the same bar twice, you would find that only this bar gets selected, which is the chosen one, and you can go to formats and, let’s say, color this in a different one, so there you go, so if you want to change only the color of a single bar, you can do that by double clicking on that respective bar. So this is how you know a column chart can be made, a very useful chart as such. You can play with the set of designs in order to find you know how useful this chart utilities could be and how simplified they are. Now let us look at the other set of charts which we can make on this kind of a data. so let’s go back to sheet one, and this time we will make the chart on, let’s say, male and female employees in the organizations. So let me transfer this data from sheet one to sheet seven, and as we discussed last time, I will quickly link it, so this is only one link which I will have to copy. Let’s right-click and paste it across the range, so this is what I do for taking the references from the sheet one. Now on this set of a data let’s go back to the column chart and see what are the other set of operations which are available. There is something called 3D chart, let me click here, and the moment we do that, as you can see, you know this chart is actually made on a 3D axis. Let us go back and play with other sets of options, cylinders, charts; only the bars change, essentially a cone chart, this is a pyramid chart. So as you can see, there are different kinds of options depending on what kind of visual appeal you want from this kind of chart. So ultimately, if you look at the basic philosophy remains the same, you can delete the series on the right-hand side, you can increase the font, this is known as the plot area, this is the chart area, you can do lot of things in terms of being including the grid lines using the chart titles, etc. So let us go back and look at other set of options. This is a line chart, a two-dimensional line chart, and what it says is, let’s say when we hover around this, it says line charts are used to display trends over time, so when you have a time series, let’s say on day one you have done a X amount of business and on day two you have done Y amount of business, and that is over period of time, you can actually show that using line charts. So let me try to plot a line chart here, in this case, male and female, and though it is line it is not kind of much useful here. There is other options of lines which is available. So at the end, so you can see, there is no pointers here. If you want some pointers, you also have that. If you choose line with markers. The third one is basically the pie charts; this may prove useful in our case. So pie chart always displays the contribution of each value to the total. So basically, the total is assumed, and the contribution of each category is shown using pie charts, so let’s try and make a pie chart here, so here is the pie chart, so as you can see, the blue, which is the larger portion is the male, and the red ones here are the females. So it gives you clear visualization of what kind of chart you are making or what is the contribution as such here. So this is very easy to interpret as well. So make pie charts in which case you have different categories and contributions which you can really make. Now let us format this pie chart like the way we did for the column charts, and we will do it fairly quickly here. We can click on the chart area, and let’s say we want to resize the chart area to a bit smaller one, so this is what the smaller size pie chart could look like. Let’s go to layout and change the legend. The legend, as of now is on the right-hand side. Let me put that at the bottom. So legend at the bottom is the one which you need to choose, so here the legends are at the bottom. Now this pie chart may not be giving me exact number in terms of how many are the number of males or what percentage is the number of males until and unless I keep my mouse pointer on that respective pie. Let me kind of add data labels here, and you can have it on the outside end. So, eleven are the number of males, and eight are the number of females here. so again, if you want to add chart titles, you can also do that above the chart, chart titles, male and female in XYZ company. So as you can see, this chart titles looks a bit large. We can change this chart title and reduce the font size to a reasonable one. We have also learnt about plot area, so this chart is now looking very small as compared to the overall size of the chart. So we can actually play with the chart area. We can increase the size of the chart a bit by clicking on the plot area and reshuffling the sizes. This is how you know you can format pie chart in a very basic way. Now let’s look at the other set of charts which were available, things like bar charts. So if I select bar charts and click on this one, you will notice that this pie chart gets converted very quickly to bar chart. There are different options of bar chart, like what we saw in the column charts, like the cylinder chart or the cone chart, so all these options also you can explore whichever is really very useful for your presentation or your report. Interesting thing in the other charts is also there is some things like the doughnut chart or a bubble chart. Let me actually open a doughnut chart for you. it is very simple, you know; you just need to go ahead and choose the chart, and that’s how you know a doughnut will kind of open up. So if you think that these kinds of charts are making sense for you in set of your case, you can kind of select these charts. So principles of formatting remains the same, and all the very best for your charts.
