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?
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Excel Wedding Planner is here, Get your copy today!||Building Inputs & Assumptions Sheets – Excel Financial Modeling [Part 3 of 6] »|