Using the Layout Tab for Excel 2010 Charts and Graphs – Part 2

In Excel 2010 Legends are a useful functionality related to charts and graph data, and thus Excel includes various commands for positioning and formatting them. Methods for positioning Legends in Excel include the Overlay Legend command and the Legend button’s dropdown menu; these options provide the user with manual and automatic methods for determining the position of a Legend.

Formatting of the Legend is enabled through numerous functions and options, including the Format Legend option and Sizing Handles. Additionally, Excel also allows users to create and format Data Labels, through the Data Labels button, Format Data Labels dialogue and Data Table options.

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

Do you need to learn Microsoft Excel? Get 35 hours of Microsoft Excel training – click here.

Video transcripts:

Hello and welcome back. In the previous section, we looked at adding a Chart Title, Adding Axis Titles and formatting these. In this section we’re going to look at the Legend. Now, the Legend here lists the sales people whose sales are represented in the chart, and there are various ways that we can both position the Legend and format it.

As with the Chart Title, there are various options for positioning of the Legend. And if we look under Chart Tools on the Layout tab, the third button in the Labels group is Legend. Click on the dropdown arrow there and we see there are a number of these Default options. Now, this group of four will enable us to position the Legend right, top, left, or bottom. And as with Chart Title, when we move the Legend around by choosing one of these, Excel 2010 will automatically reposition the Legend, of course, but also reposition the Plot Area within the Chart Area to accommodate that position. So, for instance, if I select Show Legend at the left, you can see what happens to the Legend and the Plot Area. Now, as with Chart Title, it isn’t necessarily the case that Excel 2010 will resolve all of the positioning issues when you make a change to the choice for Legend position. So, if I now choose Legend bottom, note what happens. It’s quite nicely positioned, but not only does the Legend have a clear space at the bottom of the chart, but the orientation of the entries in the Legend is changed to make use of the width of the Chart Area. So instead of the sales people’s names being one above the other, they’re side-by-side. But watch what happens if I put the Legend at the top. Now, in this case, it actually clashes with the Chart Title. There is a limit to how automated Excel 2010 can be with these things. And sometimes it is necessary to manually intervene to overcome this sort of problem.

So, now we’ll look at this question of overcoming some of these positioning issues by making a different choice. Still with the Layout tab selected and Legend, click on Overlay Legend at right and see what happens. Now we now finish out with the Legend as it was originally with the sales people’s names one above the other, but the whole Legend now overlays the chart. And there are two main ways that we can overcome these overlay types of problem. Now the first way is to change the positioning manually. You have to do this a little bit carefully, but once you know how to do it, it’s pretty straightforward. If you click once within the area while the crosshair cursor is selected, if you look at it now you can see I’ve got the crosshair selected. That means the Legend is selected and when I click I have the sizing handles and so on. And now, I can drag the Legend to any position I like within the Chart Area. So I’m going to actually drag it up there and you can see I’ve placed it away from any of the blocks or any other text.

The other thing I can do to help to make the Legend readable in any position is also pretty straightforward once you know what to do. If you move the mouse cursor somewhere within the Legend Area and right click, you actually come up with a Context Menu and within that you have a Format Legend Option. If I click on Format Legend I can actually click on Fill, choose a Solid Fill, and then choose a Solid Fill color. Now I’m going to choose a Solid Fill color of white at the moment, just to show what happens. Click on Close again and you can see that I now have the Legend with a white fill and again if I go in there, get the crosshair cursor, I could actually drag that anywhere on the chart. I’ll put it somewhere particularly unsuitable and, of course, the white fill means that I can now read all the entries in the Legend and whatever is behind the Legend is obscured by the white fill.

Now, of course, with that Format Legend dialogue I have all the full range of formatting options. So, for instance, if I wanted to put a Border around the Legend I could choose Border Color, say, Solid Line, and then choose a color for the line. Let’s stick with that red color; perhaps change the width the line a little, say, put it up to 1.5 pts. Click on Close and I’ve got all of the normal kind of formatting that I would need.

Now, we saw just now that we can choose a position manually for the Legend. There’s one other tool, one other facility that Excel 2010 offers us with Legends, which is particularly useful and particularly useful when you’re a bit tight for space. If I select this Legend again, note all the sizing handles, if I say choose this sizing handle and I’m going to drag this Legend to be wider, but not quite so tall, so I’m going to drag that up in that direction, so I’ve got a wider Legend, but not as tall and watch what happens now when I release the mouse button. Excel 2010 intelligently changes the orientation of the entries in the Legend. And as you probably guess, if I were to drag this right up to here I eventually finish out with the side-by-side arrangement, which then means that I can in fact, if I wait until I get the crosshair, I can move the whole Legend and choose a space where it either doesn’t obscure the chart or it doesn’t obscure it in a way that causes a problem.

There are one or two other features of Legends that we’ll look at later on, but we’ve covered really all of the basic things that we’re going to need, so let’s move on now to the actual data in the chart. One thing you may have noticed if you’ve been following along with this on your own chart, with your own data, is that if you hover the mouse over a particular entry, say this one, the tip that appears actually tells you about the data for that particular bar in the chart. Series “Pawolski, Dav” Point “Jan” Value: 518. So we get the Category Value, Series Value, and the actual Data Value. And that’s pretty useful. It’s not there, of course, if you’ve got a printed version of this chart. So, somebody perhaps looking at something in the middle of this chart might actually find it quite difficult to know exactly, say, what the value, the heights of that particular bar is. Now, there is a way of showing those values and of showing them quite selectively. And that’s what we’re going to look at next.

Now, with the chart selected, on the Layout tab, in the Labels group, go to the Data Labels button and there are two main options for Data Labels. None, which is the current situation and Show and if we click on show, what basically happens is that Excel 2010 adds a Label to each bar showing the value in dollars of the sales per month corresponding to each bar. Now, one of the things that’s immediately apparent in the example here is that there are quite a lot of numbers now on this chart and generally speaking, Data Labels don’t work particularly well when you’ve got a very crowded chart like this one. And it’s always worth asking yourself whether you really need those Data Labels or whether you would expect users to be able to read these values off from the axis scale on the left. You can actually add the Labels selectively as we’ll see in a little while, but just be careful that you don’t put too much information in the body of this sort of chart.

Now, to make this a little bit easier to follow, what I’m going to do is I’m going to switch the Data Labels off again; so switch to None and I’m going to select just one of my sales people that, Petra Henderson, and if I right click on one of the bars of Petra Henderson I get a Context Menu and I have an Add Data Labels option there. If I click Add Data Labels what happens now, we just have the Labels for that one person, now I’m doing that this way here just because it will be a bit easier to see what I’m doing rather than having all of those numbers. Once I’ve added Data Labels for that one person, if I right click again, bring up the Context Menu again, I get Format Data Labels and I can go into that dialogue to do a little bit more work on the Labels for this chart. I’m going to just pull the dialogue over to the side so that I can point out a couple of things as we go.

Now, within the Format Data Labels dialogue I have a number of options to choose between. For instance on Number it currently at set Number with zero decimal places. If I actually wanted to show this as currency, my Default currency is the UK Pound, so that’s the Default symbol that appears. But if I wanted to say, put it in Euros, I could choose, for instance, Irish Euros and the Euro symbol appears the way it is formatted in that country, with the Euro symbol in front. Obviously I could change it to U.S. dollars or any other currency that’s available. And I can also choose from Label Options, I can add Series Name or Category Name and so on. And if there are more values to show, for instance, if I wanted to include, let’s say I wanted to include the Series Name, then the name appears and I can choose to separate it between the name and the currency amount here. So, plenty of options to experiment with in terms of giving the right amount of information on each of the blocks in my chart.

And finally, one other very useful option for Formatting Data Labels is this one down at the bottom, Alignment. We can change the Vertical Alignment of the Labels, but we can also change the text direction. So in the case of this one, where the text is basically crossing over all of the bars, we can make things a little bit easier to work with by choosing, for instance, Rotate All Text 270 degrees and then you note how the Labels appear aligned with each of the bars or with the selected bar.

So, I’ve removed those Labels again now and I’m back to my chart as it was before. We’ll quickly look at other very useful option here, which can overcome some of the issues we get with Labels and how crowded they can make a chart look. And that is to use the last option in the Labels group on the Layout tab and that is the Data Table Option. Now, the Data Table Option currently set at None which is the Default, offers us two options. One of them is Show Data Table and one of them is Show Data Table with Legend Keys. Let’s start with Show Data Table. Show Data Table actually includes a copy of the spreadsheet data that has been used to draw the particular chart underneath the Plot Area and within the Chart Area. So you can actually see all of the number directly as they were in the spreadsheet and in addition let me change now to the other option on Data Table, which is Show Data Table with Legend Keys. Using the Legend Keys, so basically the colors for the blocks, in this case, you can directly relate these numbers to the content of the chart. So, for instance, if I look at this month and I look at that colored chart I can see that’s Petra Henderson. I can see it’s 364, so I don’t actually need 364 drawn on the block. One of the advantages of using the Data Table with the Keys is you can pretty much eliminate the need to have a Legend all together, which can save you a bit of space. So, the use of a Data Table is a very useful option when you’re looking at how to present a chart or graph.

Well, that’s it on this section on Layouts. In the final section on Layout, we’re going to look at Axes and Gridlines. 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!