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:
Excel Charting options when you large and small values [XLS]
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.
More Charting Principles & Techniques.
Related discussion on Chandoo.org forums – Lots of small values but few extreme values – how to present it?
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.