Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop
      • Python
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > Using the Layout Tab for Excel 2010 Charts and Graphs – Part 2

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:


Facebook Linkedin Twitter

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.

 

Excel 2013 training

 

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.

 

Microsoft Office 2013 training online

 

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.

 

Excel 2013 Pivot table training

 

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!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

START LEARNING Using the Layout Tab for Excel 2010 Charts and Graphs - Part 2 ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

Cropping Pictures in PowerPoint 2016

Tips and Tricks for Microsoft Word 2010 – Part 3

How to Insert a Page Break in Excel? (3 Simple Steps)

Getting Excited About Outlook 2013 New Features

How to Use Logical Functions in Microsoft Excel 2013

How to Use Nested VLOOKUP in Excel? A Step-by-Step Guide

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)