Share [Social9_Share]

Charts with very large and small values

Very Large and Small values in a single chart -1

For the first post of 2018, I wish you may have a ton of extreme positive outliers in your life!

Speaking of outliers, one of the very common problem is dealing with charts with very large and small values

Let me share my little hack with you!

 

Consider this data!

Very Large and Small values in a single chart -2

Some unusual (good) happened in April. But it is not good for a Chart!

 

Let’s Make a few Dummies

Very Large and Small values in a single chart -3

A few things to note here

  1. The reason why we are making dummies is because it will separate out the regular (small) values from the outliers (large values)
  2. The benchmark is set as 1000 to decide whether the value is small or large
  3. Rest is a simple IF formula, I am sure you know how to write an IF

 

Now let’s make a Chart (with dummies)Very Large and Small values in a single chart -4

Add the 2 dummies to the chart

  1. Small / Regular values to go on the primary axis
  2. Large / Outliers to go on the secondary axis
  3. Also notice the colors. Outliers are standing out (contrasting blue) and regular values are subdued (grey)

 

This is good but..

Let’s get that Kink (adding a new dummy)

Very Large and Small values in a single chart -5

What are we trying to do here ?

  1. Kink is nothing but explicitly letting the user know that the chart is broken
  2. Two more things to note carefully
    1. I am adding a kink at 75% of the outlier’s value (you can customize it)
    2. Instead of using a 0 (zero) I have used NA()

Now let me show you how to put this dummy to work

 

Add the Kink (dummy) to the Chart

Very Large and Small values in a single chart -6

Here are the steps

  1. First add the Kink Dummy to the chart (Ctrl+C to copy the data then select the chart and Ctrl+V)
  2. The dummy should be on the secondary axis with chart type as ‘Line with Markers’
  3. Now change the marker type to a rectangle (created using shapes). Simply Ctrl+C on the rectangle then select the marker and Ctrl+V
  4. Kink added… done!

 

A bit more finishing & the final chart looks like this..

Very Large and Small values in a single chart -7

Here is another similar article from Chandoo

 

DOWNLOAD THE FINISHED CHART

 

Other Clicky (Interesting Charting) Stuff

  1. Charting Hacks – for working a lot faster with Charts
  2. Customizing Line Chart Markers
  3. Dynamic Pie Chart
  4. Inforgraphic Chart – Type 1, Type 2
  5. Target Charts – the best ever!

 

For Video Lovers..

Previous Post
  • GraH

    I saw this one on Chandoo, and I still don’t know if I really like it. There were a lot of reactions to that post, containing nice references btw.
    It is a nice technique, but somehow you bypass the visual effect of the differences between the values. Ok, you highlight it with the marker, but still you somewhat loose the dramatic effect.
    Even in Chandoo replied in is post with “Secondary Axis: While this approach is creative, I would avoid it as it
    can be confusing. Usually secondary axis works best when you have 2
    diff. series (sales vs. profits, market share vs. profits etc.) But if
    you have been using this approach and your readers already know it, then
    you are golden.”

    Perhaps I should test it, once given the opportunity. In our services it
    may happen that an external even triggers a lot more activities (calls,
    files, …) then normal, hence this kind of data situation could occur. I think I would go with the dummy approach and the secondary axis. It seems more easy, besides the option of letting the data series go through the roof of the plot area.

    The alternative proposed by Jon Peltier is to use panel charts (https://peltiertech.com/broken-y-axis-in-excel-chart/). I kind of follow the reasoning, but I find panel charts to take too much space. So I’m still puzzled…
    Perhaps using his idea of fading out the outlier of those hyper values, and highlighting these series as “out-of-order” and put them in a comment/title is what I would do.

  • Martin Weiß

    A very cool trick that you used for this chart, never seen that before. Especially the thing with the marker is very interesting.
    Thanks for sharing this!
    Martin

  • Really nice trick. Well done and well explained. Best wishes.

    • Hi Dinesh, Great to see you here. Thanks

Chandeep Chhabra

Popular Posts

© Goodly