How to Create Charts and Graphs Using Excel 2010

Excel 2010 allows users to create charts and graphs through various commands and tools, specifically through multiple keyboard shortcuts available in Excel as well as the Insert Charts Dialogue. The dialogue is available in the Insert Tab’s Charts Group, which also contains various charts arranged categorically.

The multiple chart types and presentations available in the dialogue allow several different approaches to presenting a given data set. A Change Chart Type dialogue is also available in Excel, functionally similar to the Insert Charts dialogue.

Charts in Excel can be moved through drag functionality as well as the Zoom Slider.

Watch the free video here, transcripts for the entire video follow:

Video transcripts:

Hello and welcome back. In the previous section, we looked at a very simple example of a chart in Excel 2010. In this section, we’re going to go through pretty much the same procedure, but this time we’re going to go through it in much more detail and look at some of the additional features that you should be aware of when you’re starting to create graphs and charts in Excel 2010. So, let’s get started.

Now, as before, we’re going to choose an adjacent or contiguous set of cells. We’re actually going to plot a chart for this group of cells here. The particular worksheet I’m looking at corresponds to some monthly sales figures for some employees for 3 months of the year. Now, in the body of this data we have the actual sales figures themselves, the numbers themselves. But apart from that we have in the top left hand corner an empty cell and that will quite often be the case that we have an empty cell in the top left hand corner. Along the top we have what we will refer to as the Categories. In this case three months of the year: January, February, and March. And down the left hand column we will have what we call the Series Values. Each of these employees has a Series of monthly sales figures corresponding to the three categories: January, February and March. So, the names Anne Carnegie, Scott Denvers, and so on are our Series Values. The months Jan., Feb., Mar. are our Category Values.

Now, with the required data selected, I can go through to Insert a Chart in exactly the same way that I did before, but there’s actually a shortcut way. In earlier versions of Excel, there has always been a shortcut whereby with the F11 key you can create a Default Chart from selected data. That’s actually still available in Excel 2010. But Excel 2010, in addition, offers the option of using Alt plus F1. And the Alt plus F1 keystroke is a time saver, which will enable you to create a chart that corresponds to a default that you can change. First of all, let me press Alt and F1 now and straightaway you can see the Default Chart is created basically with one click.

Now, there we have each of our Series Values, that’s each of our employees. We have their sales by Category, the three months of the year that we’ve selected. Let’s see how to change this Default Chart. Let me use the undo command to remove the chart that’s there now. And let me go to Insert tab and if I click on the button in the bottom right of the Charts group. The Insert Chart dialog opens and you can see at the bottom of this dialog, there’s a button Set as Default Charts and in fact, that Column chart, what’s called the Clustered Columns Chart is actually selected at the moment and that’s set as my Default Chart. Let me look at the 3D version of that, 3D Clustered Column, and click on Set as Default Chart for that and then click on OK. Now you can see that my chart has been inserted. You can see what it looks like: the five employees and the Categories, the months. Let me undo the addition of that chart. But let me now do Alt plus F1 and see what happens. And there you can see that with Alt plus F1, my Default Chart type is now seen to have been changed.

Okay, so let me undo that chart as well, the one that I’ve done with the shortcuts and let’s look at the conventional way of inserting a chart of our choice. Click on Insert and as I pointed out previously, in the Charts group we have a choice of basically seven options here: Column, Line, Pie, Bar, Area, Scatter and Other Charts and there are several in each category. There are, in fact, 19 types of Column chart and under Other Charts, if I click on the dropdown next to Other Charts we can see that we have Stock Charts, Surface Charts, Doughnuts, Bubble Charts, Radar and most of these we’re going to look at in detail later on. If you know in advance which of the Categories you want, so for instance if you know you want to do a Pie Chart, you can click on the dropdown next to Pie and you can see the Pie options that are available. All of these dropdowns as well have at the bottom All Chart Types and if we click on All Chart Types, we get basically the same Insert Chart Dialog that we saw before with the full list of all of the available chart types. Let me just Cancel that.

As an alternative to going via one of these specific icons, we can use the dialogue box launcher in the bottom right of the Charts group. Click on that. Note the tip there, Create Charts and we can straightaway bring up the Insert Charts Dialog, which has the full list of available graphs and charts in it. Now there are so many graphs and charts here that it may seem really quite confusing, but it’s probably something you shouldn’t worry about too much to begin with. For many of the types of chart we’re going to look at there are only a small number of options, although they may need quite a lot of customization. It’s mainly for the more common types, such as Column charts where we do have a lot of entries. But in fact, the differences between them are quite slight and in many cases with a bit of practice you will be able to hone in on exactly which one you want pretty quickly.

So, let’s just look at these options for Column charts. If we take, for instance, the Cylinder options here, we have a Clustered Cylinder option and next to it are three other cylinder options. Now they’re all basically all going to show the same information, but with a different arrangement of the cylinders. The Clustered Cylinder shows the sales figures side-by-side. The Stacked Cylinder shows the sales figures stacked. The 100% Stacked shows the sales figures in each case proportionally divided up to 100% of sales for each of my Series, i.e. my employees. And then here we have the 3-D Cylinder. Let’s just do the first two of those to see what they look like. Let’s start with the Clustered Cylinder. If I do Clustered Cylinder you can see there are my Series, my employees. Categories are obviously the months, the sales figures there. Let me undo that one. Now let me do Insert, Dialog Box Launcher and this time I’m going to do the Stacked Cylinder. Basically it’s the same information arranged in different ways. And it’s very common amongst the Column options on the charts there to have these options grouped together, the straight forward Cylinder, Stacked, 100% and so on. So, they’re not quite as confusing as they might look at first.

Now, as we saw in the previous section once you’ve inserted a chart, you enable the Chart Tools group of tabs, Design, Layout, and Format. And on the Design tab at the extreme left there is Change Chart Type. If I click on the Change Chart Type button, I bring up the Change Chart Type dialog, which looks exactly the same as the Insert Chart dialog. The chart type I have at the moment is selected and if I wanted to change the type, which I’m going to do, I’m going to change it to a 3-D Cylinder now. I select 3-D Cylinder, click on OK, and my chart type is changed.

Now, I mentioned right at the beginning of this section that the data we chart doesn’t need to be contiguous on the spreadsheet. So, let’s just look at how we deal with noncontiguous data in general terms now. Let me first of all remove the chart that I inserted before, just use the Undo button. And here, what I’m going to do is, I am going to select the same amount of data that I did before. So that’s my five employees, Jan., Feb., Mar. and then all I need to do to include the next three months is to hold the Control key down and select the next three months worth of data. Note that I’m avoiding the Quarter totals that I’d included here. Now, this is generally how to select noncontiguous data in Excel so there’s nothing surprising or new there. And, of course, if I wanted to say try this out with my Default Chart type I could just do Alt-F1 and now you can see that I have a rather crowded looking chart. But it now has for my five employees six months worth of data for each employee.

One of the other basic things that you need to know about when you’re working with graphs and charts in Excel 2010 is how to move them around. Now, I’ve created here a straight forward chart with a small amount of data and it’s quite often the case that when the chart appears, it’s not in the right place; you need to move it to see what’s behind it or you want to move it into a report. We’re going to look at actually moving the chart around between worksheets and into other documents later on. But in terms of moving it around on the sheet itself, there’s a few things to be aware of. First of all, the area of the chart where you can actually grab it and move it is a selected part of the chart. For instance, if I click here in this sort of white area, I get the cross-hair which basically tells me that I can move the chart around. I can drag it with the mouse. But if I click say here, within the Legend, I would not be able to move it. I’d be selecting either individual Series names or the whole Legend. And I could actually use that to resize it. I could resize the Legend like that. Or I could edit one of the individual Series names. But I couldn’t move the chart like that. So, it’s important to get the hang of how to actually drag the chart without doing what I did just then, which is to actually drag the chart itself within the whole chart area. Don’t forget if you do accidentally move a part of a chart just use the Undo to put it back where it was.

Now, one or two other things to be aware of in terms of moving a chart around on a worksheet, if you do find a part of the chart area where you can drag it, which I’ve got here. Once you get towards the edge of the sheet, dragging can continue but you might have a lot of trouble seeing in relation to the rest of your data, the rest of the content of the sheet where the chart is. Again, let’s just do an Undo. One option, when you’re dealing with a very large amount of data and you want to move a chart into position so that you can either look at the data or position the chart for reporting purposes, is to use the Zoom Slider which is often quite useful with charts. If I Zoom out here, for instance, although I’ve only got a small amount of data here at the moment. If you imagine that with a lot more data, we’ll see more data later on, you can see how much easier it is to position the chart when I’m zoomed out like that. So, take advantage of the Zoom facility.

And finally in this section, let’s just look at a bigger move of a chart with the chart selected so that we have it Highlighted. We can see the Highlight box around the outside. Cut it, either using the Cut button there on the Ribbon or by using the Control-X key sequence. Then we can click on any cell and Paste. And there the chart appears based on the cell that we’ve selected. And similarly, if I go to another worksheet in my workbook, select the cell there, C3, do Paste again, and my chart now appears on that new worksheet. So, that’s the basic way of moving a chart between worksheets.

So, we’ve looked at quite a few of the most important basics of graphing and charting in Excel 2010 in this section and we’re going to continue on the same theme in the next one, so I’ll see you then.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!