Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

previousPrevious article   Next articlenext back to categoryExcel articles

article How To Create An Excel 2010 Chart To Show Both A Number And A Percent Data Series

Excel 2010 charts are very versatile, and let you chart more than one set of data on the same chart. What if one of the data sets in is percent? This article explains how to create a chart with two sets of data, one number and the other of percent and allow both sets to display on the same chart.
Excel 2010 charts are very versatile, and let you chart more than one set of data on the same chart. What if one of the data sets in is percent? This article explains how to create a chart with two sets of data, one number and the other of percent and allow both sets to display on the same chart. The secret is in using a secondary axis.

As an example say you have a table showing sales revenues for a product from four shops. Suppose these sales figures are displayed across a row and the values are in hundreds of pounds. On the next row down you type in the percentage of staff attendance for each shop. So the percent figures might be something like 90%, 85%, 70% and 95%.

If you then create a chart based on this table, for example a line chart, you'll see that the chart may show only the sales figures. This is because the percent figures, which as fractions of a number are all less than one, are so small compared to the sales figures that they don't even show on the chart.

By the way, if you want to create a column or bar chart, choose a flat chart type rather than a 3D type. In the chart sub types, these are the ones in the first row of options, as only flat chart types and line charts allow secondary axis.

To show the percent values as well as the sales values, we are going to enable a secondary axis of percent in the chart. But first of all we need to see the percent figures on the chart in the first place.

To do this, as a temporary measure, in the original table change one of the percent values to something like 5000%. Just type in the 5000 and press Enter to do this. Now the data value is much nearer the sales values and the percent data will actually show in the chart.

To create a secondary axis, right click on one of the percent items you can now see in the chart, and choose "Format Data Series". In the Format Data Series panel, under "Plot Series On" choose "Secondary Axis", then click Close to complete. Now both sets of numbers will display on the chart, but the percent values are based on a new axis on the right hand side of the chart.

Then back in the original table change the value where you typed the 5000 to the original value, provided you remember what it was of course. The secondary axis changes to suit the updated value, and the percent data is neatly displayed on the chart against its percent axis. You'll see that the other data set for the sales figures have the normal axis at the left hand side of the chart.

You can change either data set on the chart to a different chart type. For example if you want to show the sales figures as a column chart, first click once on any of the sales values in the chart, so that they all become selected. Then in the Design Tab choose Change Chart Type, and choose a flat column. So as well as displaying regular numbers and percentages on the same chart, you can also show each as a different chart type. If you're interested in learning more about Excel 2010 you might like to consider attending a training course. This can be a really effective way to greatly increase your skills.

Author is a freelance copywriter. For more information on excel course london, please visit http://www.microsofttraining.net


Original article appears here:
http://www.microsofttraining.net/article-2013-how-create-excel-2010-chart-show-both-number-and-percent-data-series.html


Back to article list

Distribution notes

PUBLICATION GUIDELINES

  • You have permission to publish this article for free providing the "About the Author" box is included in its entirety.
  • Do not post/reprint this article in any site or publication that contains hate, violence, porn, warez, or supports illegal activity.
  • Do not use this article in violation of the US CAN-SPAM Act. If sent by email, this article must be delivered to opt-in subscribers only.
  • If you publish this article in a format that supports linking, please ensure that all URLs and email addresses are active links, without the rel='nofollow' tag.
  • Software Training London Ltd. owns this article. Please respect the author's copyright and above publication guidelines.
  • If you do not agree to these terms, please do not use this article.


Microsoft Office courses London
Course rating:
4.7 stars - based on 829 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Google+ · Facebook · Twitter · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom