For decades, I’ve been whining about the need for Excel to have some way for our formulas to specify a gap in line and XY (scatter) plots.
Microsoft finally has introduced that feature! It’s now available in Excel 2016 with Office 365.
Normally, when charts plot an #N/A value, they bridge the gap between cells with actual data. Only empty cells create gaps in lines. But unfortunately, we don’t have a symbol that formulas can return to fool charts into thinking that a cell with a formula is empty.
But we now can use a chart setting that tells Excel to treat #N/A values as empty cells, as shown in this simple example.
To see if you have version that supports gaps, select a line or XY chart and in the Chart Tools, Design, Data group, choose Select Data, Hidden and Empty Cells. You have the correct version of Excel if you see this dialog with two checkboxes:
If you’re not impressed by this new feature, take a look at the following two examples and then let your imagination run wild.
A Pointlink Chart
I first saw a chart like this in Ahead of the Curve, a book written by Joeseph Ellis in 2005. He used it to connect the turning points of two different economic series that weren’t necessarily in the same month. That is, his highlights could be at a slight angle.
I, on the other hand, decided to keep it simple for my first test.
This chart of random data automatically plots those gray links whenever the two series differ by a value greater than 4, and it automatically displays the amount of those highlighted differences within the shaded areas.
Those gray areas are merely a single XY plot that uses a VERY wide line. Of course, I couldn’t have plotted line segments like this without the ability to allow formulas to choose when to return values to plot and when to return an #N/A that causes the gap to be generated.
A Fishbone Chart
This is another chart I’ve been wanting to create for years, ever since I saw something similar in Augustine’s Laws, a book written by Norman Augustine in 1983.
He used a chart like this to illustrate the tendency for most predictions to be overly optimistic.
As a consequence of this tendency, his Law Number 2 is…
If today were half as good as tomorrow is supposed to be, it probably would be twice as good as yesterday was.
From an Excel perspective, I plotted all the gray line segments using just one XY chart series. My formulas just returned #N/A each time the data indicated that it was time to start a new line segment.
I’m not offering download files yet for these examples, because very few people have a version of Excel that could use them. But when I write about this topic again in a few months, I’ll offer some improved examples.
You can learn more about this feature at a Microsoft page for Office Insiders, What’s new in Excel 2016 for Windows.
And to join Office Insiders just follow the link.
