98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
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.
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsThe Royal Society
Press Officer Katie Osbourne Excel Intermediate There's a lot of content to digest and process but Jens gave us some hints to help so will try that Quadient
Business Development Director Jason Howard Finance for Non-Financial Managers Enjoyed it. Was worried it might get too complicated, but simple enough to make me a little dangerous Mediterranean Bank plc
Head Of Corporate Credit Tyler Wallace Excel VBA Introduction Really nice course. Venue directions could be a bit better as was wandering around Limehouse to start but otherwise great class and trainer. |
PUBLICATION GUIDELINES