If you make a lot of charts, here’s a too often unused feature in Excel: Logarithmic Scale.
Thanks to fellow Excel Badass Blogger Michael Rempel for this tip! In the video below, I take the tip further to include a secondary axis and and area graph.

Let’s jump right in …

PROBLEM:

There’s a wide range between your data points. You need to make a graph, but the wide range makes the small numbers invisible.

The examples in the table below would make for useless visuals if you place them in an out-of-the-can column, line or area graph.

Metric# of EntriesLowest ValueHighest Value
Population40 Cities1103.47M
Donations45 Donations$2$1,500
Product Weights 20 Products0.02 lbs370 lbs
February Walk-in Customers30 Salons5 visits209 visits
Q1 Sales Revenue40 Sales Reps$0$310,480

This graph shows donations and is useless for any meaningful analysis. Under $200, we’re in the WTF? zone. (This might be fine as a snapshot or very high level overview but that’s about all.)

DonationExample

magnifying glass

 

We can’t tell that Wally donated $2 and Cat donated $71. But let’s not pull out the magnifying glass.

In the chart below, we use a logarithmic scale and the chart adjusts, making the smaller numbers visible. There is a deeper explanation about logarithms, and Wikipedia can explain more than you want to know. For our purposes, we need to know that there is a solution for crazy graphs that have nearly invisible data.

What can we see now? Wally’s donation is much less than $10; in fact, very few donors are in the  sub-$10 range, and Cat is not quite at $100.

LogGraph

SOLUTION  The video below shows how to make logarithmic charts, and we take the sanity even further with combo charts and secondary axes.

That’s it for today. Now, I urge you: keep your data clean and do not make crazy visuals.

If you got something from this, please subscribe to the blog. I post weekly with something related to helping keep this world spinning on clean, useful, trustworthy data. You’re also invited to send in questions that may turn into blogposts or videos.

And check out my Pinterest board for other Excel Badass Bloggers.

magnifying glass photo credit: Mike “Dakinewavamon” Kline via photopin cc