Graphs

[Devilfish2006k]

Hi there

I have a graph with a Y axis that goes from 0 to 100 in increments of 10. I would like to hide the y axis between say 20 and 50 so effectively the y axis shows 0 to 20 and then from 50 to 100. Is this possible?

 

[Jon Peltier]

This is not possible in Excel. It's a good thing, too, because hiding part of the chart will distort the visual relationship between the other data points in the chart.

 

[thisoldman]

The least potentially misleading broken axis chart that I have seen was, I think, in Information Visualization: Perception for Design, by Colin Ware. Great book, but too expensive for me to own a copy. I'm grateful for public libraries.

It was not truly a split axis but, instead, a split chart and it looked something like this:

I first plotted this chart, then adjusted and aligned it to fit exactly within a range of worksheet cells. Next, from the Excel 2016 ribbon I selected: Insert >> Illustrations >> Shapes >> Flowchart >> Punched tape. I placed the shape on top of the graph and adjusted the size so that the punched tape shape's left and right borders were outside of the chart borders.

I edited the points of the shape to smooth a kink out of each of the top and bottom curved lines. The shape was given a solid white fill and the border colors of the the chart and the shape were both set to the same dark gray.

Using the camera tool, I took one picture of the top half of the chart, omitting the left and right edges of the punched tape symbol. I pasted that picture into a different section of the worksheet. A second, similar picture of the bottom chart section was pasted below the top picture. The second picture was sent backwards to lay beneath the first pasted picture—otherwise, extra white space covered part of the top chart graphics.

I apologize that this tutorial is so condensed. It will take me several days to find time to write it up properly.

 

[Jon Peltier]

Well, that was very clever. But I will reiterate that it is not something you should do. It distorts the data in the chart, which makes it bad practice.

You have not gained much space: you've removed about 20% of the axis, but given back 5% with the white ribbon across it. But you've essentially shortened two of the bars by around 15%.

The lengths of the bars are important, because we don't judge the values in bars by where it lines up along the axis, we judge it by the visible lengths of the bars. Trying to use the axis labels to generate comparative values is a conscious action, while simply seeing the bar lengths is precognitive, it happens automatically, and it is very difficult for our conscious mind to overcome these impressions. And despite the obvious swath cut out of the middle, we're good at judging the overall lengths of the bars and assuming that they are continuous where there is an obstruction.

So what you've done is encoded your low values as the actual values, but you've encoded the high values as about 15% less than they really are.

In my chart below, the dark blue bars are how you've encoded the values, but the actual values you should be encoding are the dark blue bars plus the light blue caps on top.

You've done your viewers a disservice by not making the chart as accurate as possible.

 

[thisoldman]

@Jon Peltier,

I agree about the distortion. The data was chosen to match the ranges Devilfish2006k gave in his post.

I've only used this type of chart once, to satisfy my office-mate and his immediate boss. They were up against a deadline and had to fit the chart into a predefined space in a heavily formatted document.

The data extremes in that chart were much greater than those of my first example and there were many more low data points to display. If I recall correctly, the chart ended up something like this:

The split reduces the vertical height by a great deal more in this second chart. But even using this different data, I see you are correct—the opportunity to misinterpret the data is great.

 

[Jon Peltier]

The more you cut out of hte middle, the more you make the large and small numbers resemble each other.