How To Create An Excel 2010 Chart To Show Both A Number And A Percent Data Series
Sun 23rd October 2011
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.
Original article appears here:
Excel courses in London and UK wide.
London & UK
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Medical Research Council
Very much enjoyed the course and will be very useful to put what I've learnt into action at work.
Excel VBA Advanced
Happy with the course, I have no improvements.
However, we use quarterly reports which use data to generate charts - could have been useful to work through (although we are self taught on how to do these)
Anadarko Petroleum Corp
Project Intro Intermediate
Good general course.