Broken line panel charts aka. bifocal charts in Excel

posted May 23, 2014, 7:57 AM by Krisztina Szabó   [ updated May 23, 2014, 8:18 AM ]

Harold: A visitor is all I ask for. A temporary companion just to help me pass a few short hours in my lonely life.

by The FrankensTeam

How to show small and large number on one chart? It is a common data visualization challenge.
We started to think about line charts with “double focus”; showing both the small and large numbers - similarly as bifocal glasses do.

We created two versions of the charts using the same logic: the bottom part represents small values on the primary axis (on the left-hand side) while the large values are on the secondary axis (on the right-hand side). We do not use broken y axis, both of the axes are scaled from 0, so the charts could be understood as if two charts (one for small numbers, another for large numbers) on top of each other.
Both the two versions are suitable for line or xy scatter charts.

Version 1 - as line chart

On a line chart we use one data series where we down-scaled the large numbers to draw the blue data line, it is plotted on the primary axis.
The visual trick to “break the line” is an additional series forming a horizontal line on the chart. We set it to be transparent, so it will partially cover the data series line. Then we add one more series set to dashed line - this will help to separate the small and large value parts.
The two axes are the key elements of the chart. You will have to determine the scaling for both of the axes regarding the fact that the major unit of the axes’ must be parallel. The upper limit of the small values (100) will be in-line with the 0 value of the secondary axis. (On the secondary axis we do not display the values below 100.)
You have to take a look at the appropriate scale to read the value, or you can add labels to present the real value instead of the series value.

Label trick

Data is represented by one single line plotted on the primary axis. The trick is to use a special column where you convert the values to text string - this will serve as label. You have to set the labels as category label of the primary horizontal axis.

You can download our example file for better understanding.

Version 2 - as xy scatter

This version is a simple solution in case you do not have very big difference between the small and large data. We added one more series with both the small and large numbers in order to make a full picture of the data too. This will be the “upper series” plotted on the secondary axis.
Both of the series use the same range: the original data values. The bottom series is on the primary axis, and it will go out of the plot area. To fade the unnecessary part away, we use gradient color. Also gradient color is used on the upper series to highlight the large values.
On the picture you can see the explanations.

Axis labels

We used an additional data series to create correct labels (date) on the horizontal axis. Error bars (with yellow on the picture) were added to help visually connect data and axis.

If you are interested, you can download our file to learn the tricks.

Leave your comment