Use Excel Pivot Tables to Get Top and Bottom Views
Use Histograms to Track Frequency in Excel Dashboards
How to Format the X and Y Axis Values on Charts in Excel 2013

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.

image0.jpg

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.

image1.jpg

To change the vertical axis of a chart to logarithmic scaling, follow these steps:

  1. Right-click the vertical axis and choose Format Axis.

    The Format Axis dialog box appears.

  2. Expand the Axis Options section and select the Logarithmic Scale check box as shown in this figure.

    image2.jpg

Logarithmic scales work only with positive numbers.

blog comments powered by Disqus
Create a Histogram with a Pivot Table for Excel Dashboards
Create a Bullet Graph for Your Excel Dashboards
How to Save a Customized Chart as an Excel 2013 Template
How to Create Side-by-Side Time Comparisons in Excel Reports
How to Create Charts via the Quick Analysis Tool in Excel 2013
Advertisement

Inside Dummies.com