For business users of Microsoft Excel Free guides and templates

New Chart Feature

Two Business Uses for Excel's New Chart Feature

Your formulas soon will be able to set gaps in your line and scatter charts. Here are two ways to use the feature in your reports.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard 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 is about to introduce that feature!

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.

Simple line gap exampleBut in a limited-release version of Excel, we now can use a chart setting that tells Excel to treat #N/A values as empty cells, as shown in this simple example.

I'm told that half of Excel 365 Insiders have already received the complete version of that new feature. The other half will receive it Real Soon Now...assuming that no one discovers any major bugs.

Standard Excel 365 users will get the feature several months after all the Excel Insiders get their full copy. Excel Charting dialog for Hidden and Empty CellsTo see if you have the full copy yet, select a line or XY chart and in the Chart Tools, Design, Data group, choose Select Data, Hidden and Empty Cells. If you see this dialog with two checkboxes, you have the full version.

On the other hand, if you only have one checkbox, as I do, and if you joined the Office Insiders program, your chart shows a gap by default when it plots data with an #N/A, as in the example above.

If you're not impressed by this new feature, take a look at the following two examples and then let your imagintion 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.

An Excel Pointlink chartI, 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.

An Excel fishbone chart.He used a chart like this to illustrate the tendency for most predictions to be overly optimistic.

As a consequence of this tendancy, 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.


3 Simple Tricks to Improve Analytical Charting in Excel

Introducing the Power of Year-Over-Year Performance Charts in Excel

Beveridge-Chart Trend Analysis with Excel

Chart Your Rate of Change to Reveal Hidden Business Performance

How to Show Recessions in Excel Charts


Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.