Microsoft Office Tutorials and References
In Depth Information
Chapter 8: Components That Show Trending
Some of you would argue that the bottom chart shown in Figure 8-4 hides the
smallscale trending that may be important. That is, a seven unit difference may be very
significant in some businesses. Well, if that’s true, why use a chart at all? If each unit has such
an impact on the analysis, why use a broad-sweep representation like a chart? A table
with conditional formatting will do a better job at highlighting small-scale changes than
any chart can.
Leveraging Excel’s logarithmic scale
In some situations, your trending may start with very small numbers and end with very large
numbers. In these cases, you end up with charts that don’t accurately represent the true trend. Take
Figure 8-6, for instance. In this figure, you see the unit trending for both 2009 and 2010. As you can
see in the source data, 2009 started with a modest 50 units. As the months progressed, the monthly
unit count increased to 11,100 units through December 2010. Because the two years are on such
different scales, it’s difficult to discern a comparative trending for the two years together.
Figure 8-6: A standard linear scale doesn’t allow for accurate trending in this chart.
The solution is to use a logarithmic scale instead of a standard linear scale.
Without going into high school math, a logarithmic scale allows your axis to jump from 1 to 10, to
100 to 1,000, and so on without changing the spacing between axis points. In other words, the
distance between 1 and 10 is the same as the distance between 100 and 1,000.
Figure 8-7 shows the same chart as the one in Figure 8-6, but in a logarithmic scale. Notice that the
trending for both years is now clear and accurately represented.
Figure 8-7: Using the logarithmic scale helps bring out trending in charts that contain very small
and very large values.