Two Business Uses for Excel’s New Chart Feature

Beginning in Excel 2016 in Office 365, your formulas can set gaps in your line and scatter charts. Here are two ways to use the feature in your reports.


Beginning in Excel 2016 in Office 365, your formulas can set gaps in your line and scatter charts. Here are two ways to use the feature in your reports.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.

Many Excel users now have the option to chart #N/A as empty cells.

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:

Excel dialog that supports gaps in line charts.

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.

The gray bars are plotted by one XY plot with calculated gaps in the line.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.

The gray fish bones are plotted by one XY plot with gaps in its lines.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.