How do you make charts when you have lots of small values but few extremely large values? [Debate]
Here is an interesting charting problem we come across once in a while. We have a lot of small numbers and a few very large numbers. How do we effectively plot all of them in a chart?
That is, you have data like the numbers shown to right, with a surprising bump during Christmas time.
Now, how do you go about making a chart?
I can think of 4 options:
1. You make a chart as – is
2. Use log scale for vertical axis (axis option > check logarithmic scale)
3. Split the axis in to two: This is the most trickiest technique. I have used 2 charts and aligned them neatly. Thankfully Peltier Man comes to rescue. Read broken y-axis to know how to do this.
4. Let the bars reach sky: You can of course, set axis max to an arbitrary value, just so that all the small values are clearly displayed and let the higher values literally cut thru plot area.
Download these Examples:
I have put all these 4 chart examples in a workbook so you can see the underlying technique. Go ahead and download it here:
What would you do?
I seldom deal with data that has this kind of outliers. But I am sure you get such data once in a while. How do you go about charting then? Share your ideas and implementations using comments.
Related discussion on Chandoo.org forums – Lots of small values but few extreme values – how to present it?
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
More awesome tips for you:
Leave a Reply
|Excel Wedding Planner is here, Get your copy today!||Building Inputs & Assumptions Sheets – Excel Financial Modeling [Part 3 of 6]|