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

Sun 23rd October 2011

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 https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2013-how-create-excel-2010-chart-show-both-number-and-percent-data-series.html