How to Create Charts and Graphs in Excel 2010 Using Design Tab

Excel 2010 possesses numerous options and tools which augment a user’s ability to create and edit charts and graphs. The Context Menu enables various chart options, and the Switch Row and Column command allows users to manually switch rows and columns in an Excel chart.

Additionally, the Select Data button enables switching of the rows and columns while also allowing for moving and editing text, by prompting a Select Data dialogue.

Additional chart styles and formats are available through the Design Tab’s Type Group, the More button in the Chart Style’s section, and the Page Layout Tab’s Themes option.

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

Video transcripts:

In the last section we looked at a straightforward way of creating a chart from the data you can see in front of you again here. Let me just do a quick recap. If I select that data, I can either do Insert chart or I can do the shortcut Alt-F1 to get a Default chart and there we are. And just to recap, we have Series of Jan, Feb, and Mar and Categories corresponding to our five sales people.

It’s worth noting that when you’ve created the chart, if you right click in the chart area you bring up a Context Menu which offers a number of options; not just Cut and Copy options but also a Move Chart option which gives an alternative way of moving the chart so you can actually specify that it needs to be moved to a selected sheet or you can create a new sheet and say move it to that one. Also on this Context Menu we have options to do 3-D Rotation, Format the Chart Area, change the Font that’s used and so on. And we’ll be looking at some of those Formatting options later on.

Now let’s look at the chart itself again. We have these three Series: Jan, Feb, Mar and five Categories. Let me just move the chart slightly out of the way. Let’s do what we did before. I’m going to select the same data area, Jan, Feb, Mar, hold the Control key down and select the next three months, and I’m again now going to do an Alt-F1 to create a Default chart. Now, note what happens because now my Series are no longer Series. My Series are now the five sales people and the Categories are actually the six months of the year.

Now, I mentioned earlier on that it’s quite easy in Excel 2010, or in fact earlier versions of Excel, to get the Series and Categories the wrong way around. And one of the reasons for this is that Excel doesn’t actually always use columns for one and rows for the other. It actually works on the principle of which is most numerous when it’s actually taking a stab at creating a chart. And so it’s actually quite common to need to be able to switch rows and columns. Now, in this case, the reason it’s chosen six Categories and five Series is because, generally speaking, whichever is more numerous it treats as its Categories. And if we need to change them we need to change them manually.

So, with this particular chart selected on the Chart Tools Design tab, if I go to Switch Row and Column, if you watch what happens to the chart as I do it we finish up with the six months as the Series and once again the sales people as the Categories. I could, of course, choose the other chart and do the reverse to that chart.

So, let’s revert back to the chart with the six months worth of data in it and let’s look at one of the other commands on the Design tab, because this is also a command button we can use to switch columns and rows, but we can also use it to do quite a few other things as well. It’s the Select Data button. Now with the Select Data button I’m just going to move its dialogue up here so we can see a little bit of what’s going on. This actually enables us to switch rows and columns, but for each of the Series, which are currently on this chart, the months, and each of the Category labels, it lets us move them around, change the text and so on. So, let me show you one or two examples of the sort of things you can do with that.

First of all, within the Select Data source dialogue, at the top we have Chart Data Range and this actually lists the range that the chart refers to. Now in this case our range is actually made up of two noncontiguous ranges of cells, both on sheet one. The first range is A1 to D6. The second range is F1 to H6. I could, if I wanted to, actually type the range of cells I wanted to use in here. It’s not something I do very often, but there’s no reason that I shouldn’t and this is obviously a way that I could change the range if I wanted to. So, for instance, if I wanted to lose Junes figures, I could change the H in H6 to G6, click on OK and see what happens. And there we are, the June figures have gone.

Let’s look at one or two of the other things we can do usefully with the Select Data dialogue. We have on the left, the Legend entries for the Series. As you’ve all ready seen, we can switch around row and column, but we can actually go in and Edit these entries. So, for instance, we could Edit this, change the Series name, and also change the Series values. The actual range that’s specified for Jan is basically from B2 to B6, so it’s this part of the column, you just about see it there. And the Series name is taken as the Content of B1, which is Jan. So if I wanted to put in Jan 2011 as my Series name, click on OK. You can see that Jan 2011 now appears there in the Legend.

And there are in fact other things that I can do here such as if I say didn’t want to include the figures for May, I could just click on Remove and I can also change the sequence if I click here for March I could move March up, if you note at the moment. Look at the Legend; March is the green column in the chart. I can use the up and down arrows here, move March ahead of February and you’ll now see that the green column is second in the list. Obviously it will be a bit nonsensical with month dates, but if in fact my Series values were different from this, then it might be appropriate to change the sequence. I’m just going to move that one back down again and there we are. Click on OK and I’ve made quite a few substantial changes to my chart.

So, having used the Select Data dialogue to make some changes, let’s now look at the chart type that we have selected and see in a little bit more detail the choices we have for changing the chart type. Before I do that I’m going to do one other thing, I’m going to select the chart itself and then I’m going to use one of the sizing handles in the corner to make the chart a little bit bigger and as it gets bigger you’ll see that Excel 2010 uses its own intelligence to realign things like the Categories, the Series labels, and so on. I’m going to leave that one saying Jan 2011, that really doesn’t matter. Now, with the chart type that we’ve got, which is a Column chart type, with the chart selected within the Chart Tools group of three tabs, on the Design tab we have an option to choose between the layouts. Now we have scroll buttons here that let us go down the available chart layouts for the Column chart type and, in fact, the More button here we can click to give us access to the whole lot in extended gallery view.

Now, in the extended gallery view it’s possible to see how the main elements of the Column chart type are arranged for each of the available options. So, for instance, with option 1 we have a Title at the top, Legend on the right, and otherwise the chart appears pretty much as it does here. Here’s an alternative where the Title is at the top, the Legend is below the Title, and then the chart appears and so on. So, let’s go for option 1. And really all that’s changed there is that we have a Chart Title in place and we’ll see in a little while how we can actually add a Chart Title. It’s very easy then to try any of the other options that we want to try. Some of them involve having Titles on the individual Axes without a Chart Title, some of them involve having these grid lines which make it easier to read heights, depths, etcetera of the chart columns.

Now don’t forget, and as we saw before, we could, of course, completely change the Chart Type if we wanted to. We have the button over here on the left in the Type Group on the Design tab where we can basically select from the whole range of available charts, but I’m not going to do that at this stage. Let’s go back to the Layouts gallery again and on the More I’m going to choose this type, Layout 9. This has Title, Axis Titles on both, Legend, and so on. And in the next section we’re going to look at how to set up the Chart Title, Axis Titles, and so on.

So, finally in this section, let’s take a look again at the Chart Styles. We saw this before; we know that with this whole gallery of Chart Styles here on the Design tab, we can very easily change the color scheme for our chart. Again, there is a More button we can press and in fact as you move your way through the gallery options here for style they get more and more extremely different from what we started with. So, for instance, if you choose the last option, the chart really looks quite spectacularly different from the way that it looked at the beginning. Now, apart from this choice of style and bear in mind you can actually create your own styles as well, the basis of the Color Scheme, Fonts, and so on is the theme that’s selected at the moment. And if I go to the Page Layout tab and then click on Themes, I can see the available Themes and I can actually change the Theme for this particular chart. So, for instance, the Aspect theme, and note that it’s a live preview, would make the chart look like that. The Austin theme makes it look like that. Black Tie theme makes it look like that and so on. Now, built into each of those themes is a set of Fonts, Font sizes, colors, and so on. And depending on the theme that we choose, it can have quite a dramatic impact on the look and feel of the final chart. So, there’s the Office theme, here’s the Adjacency theme. I’m going to choose Angles.

So, as you can see, choosing the Theme can also have a dramatic affect on the appearance of a chart and I’m really quite pleased with the way this one is starting to look now. And in the next section, I’m going to look at putting a Title on, putting Axis Titles on, and some other more detailed Formatting that I’ll normally need to be able to do. Before I end this section though, just one word of warning when you choose a Theme, that Theme will apply to all of the graphic elements in a Workbook. So, the Theme will also apply, not only to other charts and graphs, but also to Smart Art graphics and so on. So, be a little bit careful when you choose a Theme. You might get some surprises. A chart perhaps you’d thought you’d finished, changes when you’re working on another one. So, that’s just a word of caution really. I’ll see you in the next section.

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!