If you need to create a chart where each series contains both small and large numbers, it will be difficult to see the detail for the smaller numbers. Allow the chart to show detail of both the small and large numbers by using a logarithmic scale. Episode 374 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:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
In yesterday's netcast we had a situation where we're trying to chart two series of data, and one series was completely a different order of magnitude than the other series.
Today I have two series of data, and within each series we go from very small product lines to very large product lines.
In this case, we have a couple of data points that are up around 12000, which make it nearly impossible to see the detail of the data points down around 300 or so.
Now, there's a great solution for this, and it involves some high school math that you've probably forgotten about a long time ago.
If we have the chart and right-click on the numbers in the value axis, choose Format Axis.
We want to go to the Scale tab, and there's a setting in the lower-left hand corner of the Scale tab called a Logarithmic scale, choose that and click OK.
In a Logarithmic scale, the distance between one and ten along the Y-axis is the same as the distance from a 1000 and 10000.
And now, some people say this distorts the numbers.
But my argument is that it actually allows us to really see the detail at numbers that are both a large order of magnitude and a small order of magnitude.
So here we're comparing forecast, the blue line, to actual numbers in the pink line.
In this case, the forecast was just a little bit higher than the actual.
Because we're on a Logarithmic scale, it actually sort of explodes these smaller values, so we can really see the detail that we could never see in the original chart.
So that's using a logarithmic scale, and again, to get to that, we right-click on the value axis, Format Axis, and use the Scale tab, choosing Logarithmic scale.
Now I find that whenever I do this, I kind of have to explain to people what's going on, and show that I'm not trying to distort the numbers, but it allows us to actually see the detail of those smaller numbers on the same chart.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
In yesterday's netcast we had a situation where we're trying to chart two series of data, and one series was completely a different order of magnitude than the other series.
Today I have two series of data, and within each series we go from very small product lines to very large product lines.
In this case, we have a couple of data points that are up around 12000, which make it nearly impossible to see the detail of the data points down around 300 or so.
Now, there's a great solution for this, and it involves some high school math that you've probably forgotten about a long time ago.
If we have the chart and right-click on the numbers in the value axis, choose Format Axis.
We want to go to the Scale tab, and there's a setting in the lower-left hand corner of the Scale tab called a Logarithmic scale, choose that and click OK.
In a Logarithmic scale, the distance between one and ten along the Y-axis is the same as the distance from a 1000 and 10000.
And now, some people say this distorts the numbers.
But my argument is that it actually allows us to really see the detail at numbers that are both a large order of magnitude and a small order of magnitude.
So here we're comparing forecast, the blue line, to actual numbers in the pink line.
In this case, the forecast was just a little bit higher than the actual.
Because we're on a Logarithmic scale, it actually sort of explodes these smaller values, so we can really see the detail that we could never see in the original chart.
So that's using a logarithmic scale, and again, to get to that, we right-click on the value axis, Format Axis, and use the Scale tab, choosing Logarithmic scale.
Now I find that whenever I do this, I kind of have to explain to people what's going on, and show that I'm not trying to distort the numbers, but it allows us to actually see the detail of those smaller numbers on the same chart.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
