When I look at the bar chart above, the short bars all look about half as tall as the long ones, so I get the mistaken impression that the smaller values are around half of the large value. In a bar chart, our precognitive sense of the values is based on the lengths of the bars, and it’s hard to overcome that. There is still a problem, especially with the bar chart. We can reduce the white space by entering a better value for axis minimum. However, this choice of scale minimum means that the bottom two-thirds of the chart is blank, all of the data occurs in the top.
Excel likes to use zero as one endpoint of an axis, and 1 corresponds to zero on a log scale. The other point about the charts above is the scale limits. But that’s secondary, because we are really only comparing extreme values with very little in between. That’s wrong, of course, because they aren’t of similar magnitude. Your first thought might be satisfaction that the data are now all shown with a similar magnitude. The data I’m using here is very similar to the data in the MyExcelOnline post cited above. You can also change the base of the log scale, but it’s usually best to keep it at 10. Check the box, and the scale is adjusted. In the same view that allows formatting of scale parameters like minimum and maximum, you should find a checkbox that says Logarithmic Scale. Select the axis, then press Ctrl+1 to open the Format Axis task pane (or dialog in Excel 2010 and earlier). It’s easy enough to apply a logarithmic scale to a numerical axis in an Excel chart. It was a short and sweet review of the technique, but it left out the thought process that should accompany any charting effort. John’s point was well taken, but it was incomplete. The My Excel Online web site is run by my colleague John Michaloudis, and it features lots of great tutorials, podcasts, free training, and paid courses. The first approach to chart a wide range of values was suggested in Logarithmic Scale In An Excel Chart, a tutorial on the MyExcelOnline Excel Blog.
In both charts, the January sales value stands proud, while the other months are hiding in the weeds along the bottom of the chart. (I’ll be illustrating the concepts in this article using both line and column charts, because each has special considerations.
One month had very high sales, while the rest of the months had low sales.
The following two charts show monthly sales data. I’m showing these in Excel charts, of course, but they apply no matter what charting package you may be using. How do you chart a wide range of values? There are numerous solutions to this, each with pros and cons.
0 kommentar(er)
