Dashboards and Reports: Leverage Excel’s Logarithmic Scale
In some situations, your trending data for your Excel dashboard 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. For example, 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.
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.
This figure shows the same chart as the first figure, but in a logarithmic scale. Notice that the trending for both years is now clear and accurately represented.
To change the vertical axis of a chart to logarithmic scaling, follow these steps:
Right-click the vertical axis and choose Format Axis.
The Format Axis dialog box appears.
Expand the Axis Options section and select the Logarithmic Scale check box as shown in this figure.
Logarithmic scales work only with positive numbers.