How to Create Charts and Graphs in Microsoft Excel 2016
Watch about Microsoft Excel 2016 video tutorial by Simon Sez IT. Learn how to create different types of charts and graphs, such as a clustered column. We will also show you how to resize the chart, change its design, and move it to another worksheet or to a new chartsheet.
Watch the free video here. Transcripts for the entire video follow:
Video transcripts:
In this section, we’re going to start to look at what many people consider to be one of the greatest strengths of Excel and that is the ability to create really good Graphs and Charts.
Why do people use Graphs and Charts in Excel? Well, usually it’s to strengthen a message or sometimes to actually give the message itself.
As the saying goes, a picture can paint a thousand words. And very often, particularly with complex data. A picture can help people to understand the data without getting too baffled by all of the numbers.
Now, I should warn you that the area of Graphs and Charts in Excel is a pretty vast area. So, we’re going to start with the basics as usual.
I’m going to use some very straightforward data here about the lengths of a number of rivers in Africa and the drainage basins for some of the rivers.
What I’m going to do first of all is to create a straightforward Graph or Chart to illustrate the lengths of those eight rivers.
Need to Learn Excel? Get a Free Excel 2016 Course – Click Here
Let’s just ignore the drainage basins for a moment. What I’m going to do, first of all, is to select the data that I want to draw the Graph or Chart of.
I select from A1 down to B9. I go to the Insert Tab. One of the groups on the Insert Tab is Charts. I’m going to click first on Recommended Charts because once you’ve selected some data, what Excel 2016 can do for you is to recommend a suitable type of chart.
The recommendations it makes are on the left here. There are four of them. First of all, it says Clustered Column. Now, I’m going to assume you’re not really familiar with types of Graph and Chart (although I know many of you will be).
I’ll briefly explain each as we go along. A Clustered Column basically means we have vertical columns and each column corresponds to a category.
Excel assumes in this case that the categories are in column A, so A2 down to A9. So, we have a category, Okavango, and a category Orange and so on.
On the vertical scale, on the vertical axis, it plots what it calls the value. Values, in this case, are the lengths of the rivers. As you can see, its taken each of our rivers and plotted a column.
Now, note the little summary underneath the chart. The Clustered Column Chart is used to compare values across a few categories. Use it when the order of categories is not important.
The second option it offers is really similar but it’s flipped over. It’s what’s called a Clustered Bar Chart. This time the categories are on the vertical axis, the values are horizontal. So you’re looking at the lengths of the rivers as horizontal bars.
The third category, it offers is a Pie Chart. The Pie Charts most people are familiar with. We slice a pie up in proportion. Here we come to a very important point.
When Excel is looking at the data that you’ve selected, it has no idea what it means. And in this case, if you’re used to Charts or Statistics and so on, you probably realize that a Pie Chart would be an extremely bad choice of Chart.
Because a Pie Chart implies that you’re dividing something. You’re dividing the pie into parts. Now, these eight rivers, the lengths of them do not. When added up, add to a whole something. It’s not sort of the length of all the rivers in the world or something like that. It’s divided between these eight.
They happen to have those eight lengths but those eight lengths added together. It doesn’t form a total of something. So, a Pie Chart would not be suitable. However, Excel doesn’t know that.
You understand this, Excel doesn’t. So, it offers you a Pie Chart. You need to be aware of the meaning of Pie Charts to realize that this is not a suitable Chart in this case.
The fourth option is one of the new types of Chart in Excel 2016. It’s what’s called a Pareto Chart. I’m not going to talk about Pareto Charts at the moment. It’s associated with something you may have heard of called Pareto or Pareto Analysis. Again, this would not be a suitable Chart in this case. I’ll come back to Pareto a little bit later on in the course.
The best choices here would be either the Column Chart or the Bar Chart. Either of them is suitable. Some people avoid Column Charts in this kind of situation. Because quite often, people associate the columns as in some way progressing with time.
In this case, there is no progression with time. Anybody looking at this Clustered Column Chart, should we choose to draw it? Certainly should not imply any change over time. But because sometimes people make this time inference many. People would go for the Clustered Bar in this case. But in effect, either of them is totally suitable.
Before I actually draw the Chart in this case. What I want to do is to take a look at the All Charts Tab in the Insert Chart Dialogue. Because listed there, you will effectively see all of the available Chart types.
In addition at the very top, you’ve got a tab that says Recent which shows you the recently created types of Chart. It gives you quick access to the ones you’ve used most recently.
You also have access to any Chart Templates that you may have created or acquired. Then, you have a list of all the different Chart types.
The fact that you have selected the data of the type that you have. Will to a large extent determine which types of Chart you can actually draw from that data. In fact, with a very straightforward selection like the selection here. Most of these Charts you could draw. But, some of them you couldn’t.
For instance, you couldn’t draw a Stock Chart because for a Stock Chart you need high, low, and close stock market figures to be selected. Excel can tell that what you have here is not high, low and close figures.
Often, and particularly when you’ve got used to the different types of Chart in Excel; you may go to this All Charts Tab. Go straight to the sort of Chart that you want to select. Now in addition, let me now Cancel that.
I will come back to it in a moment. If you go back to the Charts Group, there are a number of icons within the group which represent the most popular types of Chart.
You’ve got things like the Hierarchy Chart, Waterfall or Stock Chart. You’ve got Statistical Charts and most of these have dropdowns. On the dropdown, you have the main Charts of that particular type. Then, usually access to some more.
In the bottom right hand right-hand of the Charts Group, there’s a little Dialogue Box Launcher and that gets you back into the Insert Chart Dialogue.
As you can see, there’s a very wide range of Charts. And to some extent, if you haven’t done this sort of thing before, you really need to rely on a bit of experience. You’ll soon find that you start to know which Charts to use in each situation.
I’m going to create now a Clustered Column Chart. I’ll click on OK and there is my Chart. To the right of it, you will see three buttons. There’s one with a plus, one with a pen, one with a filter on it.
I’m going to talk about those later on in the course. So, don’t worry about those at the moment. Let’s concentrate for now on the chart area itself.
Within the chart area, there are a number of things. There’s the Chart itself of course. You can see the vertical columns. You have a horizontal axis with the categories on it.
In this case, the categories are the names of the rivers. On the left hand side, you can see the scale for the vertical axis. The Vertical Axis, in this case, contains the values and the values are the lengths in kilometers.
There are many other things that you can add to Charts, as we’re going to see. To some extent, Excel does its best job to make your chart complete as quickly as possible.
Bu,t sometimes what it does is not really up to the job. So for instance, if I hover over Length kilometers here; what Excel has done is to make length (kilometers) the Chart title. It didn’t actually have anything else to use so it used that.
I definitely want to give this chart a better title than that. I’m going to do that in the next section but you can see here, the basics of a straightforward chart and some of the main features.
Now, as we’ll see in the next section, there are many ways that we can customize this, as I’ve already said. When you have a Chart selected. Like this one.
You get two additional tabs on the Ribbons. These two tabs, there’s a Design Tab and a Format Tab, are described together as the Chart Tools.
With those Chart Tools, we can do all sorts of things in terms of changing the design. So for instance, we can use this gallery of Chart Styles here and completely change the style of the chart. And then, with the Format Tab, we can do a lot in terms of individually formatting the way the text appears and so on. But more of that later.
One very straightforward thing you can do is to resize a chart just by using the Resizing handle. So for instance, if I wanted to make this chart bigger, I could hover the top left hand corner, get the appropriate cursor, the cross-haired cursor, release the cursor, and my chart is much bigger.
Another thing that you can do with a Chart and this is in fact a very important feature, is that you can move a Chart. By default, it’s created on the same sheet as the data which it is representing.
But sometimes you may want the Chart to be created on a completely different sheet or indeed you may want to create it on a special type of sheet called a Chart sheet.
A Chart sheet is unlike a regular worksheet. It’s if you like the other type of worksheet in that it doesn’t contain cells where you use formulae and functions and so on. It’s there really to hold a Chart.
The Chart sheets can be very helpful, particularly, if, for example, you want to do a presentation with a Chart in it. And you don’t want the presentation to be sort of confused by having the data in the background. As you have here or even by having lots of cells and rows and columns and things. Basically, you can have the Chart sitting on its own.
I should point out, of course, that when you’ve got a Chart you can very easily Copy it into a completely different type of Office document such as a PowerPoint presentation or a Word document.
Let me just show you how to move this sheet. Let’s go back to the Chart Tools Design Tab. At the right hand end, in the Location Group. There is a Move Chart button.
Basically, I’m given a choice and the choice is to either keep this as an object in the Rivers Sheet. In fact, I could put in any other available worksheet or I can move it to a Chart sheet.
In this case, I’m going to move it to a new Chart sheet. I’m going to call the Chart sheet River Lengths Chart. I’ll click on Ok and there is the Chart on its own Chart sheet. As you can see the Chart sheet doesn’t have the background with the cells and rows and columns. Particularly, if you were to hide the Ribbon and the Formula Bar that you could make this a very straightforward presentation of the lengths of the rivers without any sort of distractions.
So that’s it! That’s how to create a basic Chart. In the next section, we’re going to look at customization. So, please join me for that.
Need to Learn Excel? Get a Free Excel 2016 Course – Click Here