Sometimes you want a chart to show two different kinds of data - for example, revenue, and gross profit percentage. If you create such a chart, the size of the revenue bars makes it impossible to see the detail in the profit percentage bars. The trick is to plot one of the series along a secondary axis. Episode 373 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Last week I was out doing seminars, and I got some great questions along the way, one of them involved charting.
Someone had a chart where they created a chart of both Revenue and Profit percent.
And the problem with that is that the size of the revenue bars in thousands make it so that we can't even see the Gross Profit percent at all.
The line is so small, it must just be down here at zero, we can't see it.
The great solution is to put that on a secondary axis, take the profit, and put it on a secondary axis.
But how can we even right-click on it if we can't see it?
The solution is to use the Charting toolbar!
In the Charting toolbar, on the left-hand side, there's a drop-down where we can choose the Series “Profit”, and then the second button in the Charting toolbar is called the Format button.
Once we select the Series “Profit”, we can choose Format Data Series, and then on the Axis tab change that to be a secondary axis.
Now once we've created that, we then want to change the Chart Type of the secondary axis to maybe a Line or something like that, I'll click OK.
And now you have a situation where the bars are tied to the values on the left-hand side in thousands.
And you can actually see the detail of the line chart, which is tied to the value on the right-hand side in percentages.
Great way to see data with two different orders of magnitude on the same chart.
Hey, thanks for stopping by, we’ll see you tomorrow for another netcast from MrExcel!
Last week I was out doing seminars, and I got some great questions along the way, one of them involved charting.
Someone had a chart where they created a chart of both Revenue and Profit percent.
And the problem with that is that the size of the revenue bars in thousands make it so that we can't even see the Gross Profit percent at all.
The line is so small, it must just be down here at zero, we can't see it.
The great solution is to put that on a secondary axis, take the profit, and put it on a secondary axis.
But how can we even right-click on it if we can't see it?
The solution is to use the Charting toolbar!
In the Charting toolbar, on the left-hand side, there's a drop-down where we can choose the Series “Profit”, and then the second button in the Charting toolbar is called the Format button.
Once we select the Series “Profit”, we can choose Format Data Series, and then on the Axis tab change that to be a secondary axis.
Now once we've created that, we then want to change the Chart Type of the secondary axis to maybe a Line or something like that, I'll click OK.
And now you have a situation where the bars are tied to the values on the left-hand side in thousands.
And you can actually see the detail of the line chart, which is tied to the value on the right-hand side in percentages.
Great way to see data with two different orders of magnitude on the same chart.
Hey, thanks for stopping by, we’ll see you tomorrow for another netcast from MrExcel!
