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 Entries | Lowest Value | Highest Value |
|---|---|---|---|
| Population | 40 Cities | 110 | 3.47M |
| Donations | 45 Donations | $2 | $1,500 |
| Product Weights | 20 Products | 0.02 lbs | 370 lbs |
| February Walk-in Customers | 30 Salons | 5 visits | 209 visits |
| Q1 Sales Revenue | 40 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.)
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.
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
