## Plot Blank Cells in Excel Charts

A common problem around web forums and blogs is how to plot blank cells in Excel charts. There is a lot of confusion about plotting of hidden and empty cells, about what constitutes a blank cell, and about various workarounds that purport to produce blank cells that will or will not be displayed in a chart.

I discuss the “plot blank cells” issue in several tutorials in this blog: Mind the Gap – Charting Empty Cells, Area Chart With Gap, Another approach to plotting gaps in Excel charts, and VBA Approaches to Plotting Gaps in Excel Charts, among others.

### tl;dr

A new feature in Excel 2016, *Show #N/A As An Empty Cell*, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.

### Excel’s Hidden and Empty Cell Settings Dialog

You can easily tell Excel how to plot empty cells in a chart. Right click on the chart and choose *Select Data*, or choose *Select Data* from the ribbon. Click on *Hidden and Empty Cells* in the bottom left of the *Select Data Source* dialog that appears. The *Hidden and Empty Cell Settings* dialog appears. There are three options for *Show Empty Cells As*: *Gaps*, *Zero*, and *Connect Data Points with Line*.

There is also an option to *Show Data in Hidden Rows and Columns*. By default Excel will not chart data in rows or columns which have been hidden, but checking this box causes hidden cells to be plotted.

I’ll explain what the Empty Cells settings mean below, including some of their idiosyncrasies.

### What’s a “Blank Cell”, Anyway?

Part of the confusion over this whole issue is what Excel considers a blank cell.

People will type a space character in a cell and wonder why that’s not blank, it looks blank. The answer is, because it is simply a transparent piece of text.

People will enter a formula like `=IF(A1>0,A1,"")`

and wonder why it’s blank, because it makes a cell look blank too. The answer is, because the cell contains a formula. But it returns “”, why isn’t that blank? Because “” is a piece of text, albeit zero characters long.

Sometimes a null string (i.e., “” without the quotes) gets imported into a cell, and people don’t see anything, so they wonder about that too. As above, it’s a piece of text.

But this is all confusing, because different Excel functions treat “” differently. For example, if cell A1 contains “”, `ISBLANK(A1)`

is FALSE, but `COUNTBLANK(A1)`

is 1. So is it a blank, or isn’t it? Well, a chart plots it as zero, so for my money, it’s not a blank at all.

The fact is, there is no magic function in Excel, no `BLANK()`

or `NULL()`

, which would cause a cell to behave consistently as a blank cell.

### Use #N/A Instead of Blanks

A partial solution to the problem is to write a formula that displays #N/A instead of “” to prevent drawing a point. For example, `=IF(A1>0,A1,NA())`

instead of `=IF(A1>0,A1,"")`

, where the function `NA()`

displays #N/A in a cell. However, this is of limited use, since it doesn’t really mimic the behavior of a blank cell, and it works differently for different chart types. People also avoid this approach because they think the #N/A value in a cell is ugly, even though it is informative. Sometimes the advice for this is to use conditional formatting to hide the error; I tend to use a light gray font to de-emphasize it without hiding it altogether.

Care must be taken with the #N/A approach. `=IF(A1>0,A1,NA())`

and `=IF(A1>0,A1,#N/A)`

both return the error value #N/A, which as an error is centered in a cell with General Horizontal Alignment (see my post Check Your Data: Is It Numeric or Text to learn why General Horizontal Alignment is important). However, `=IF(A1>0,A1,"#N/A")`

returns a text label “#N/A”, which as text is left aligned in a cell, and will be plotted like any text, as a zero value.

This is the behavior which has changed in very recent versions of Excel 2016. I also believe that when these changes were implemented, some inconsistencies crept into this behavior.

### Other Workarounds

There are numerous other ways to get around the inability to plot a formula as a blank cell.

In Another approach to plotting gaps in Excel charts (on this blog), Roberto Mensa, Kris Szabó, and Gábor Madác of the FrankensTeam showed an elegant and sometimes fragile technique that used defined range names that would substitute a blank cell for any cell containing #N/A.

In the comments to another post on my blog, which I cannot now locate, another reader suggested creating a range that linked multiple times to each value in the original range, so each point appears three times. The middle time it is actually a blank cell, which the first and third time it links to the original. If the original is #N/A, we are left with a sequence of #N/A-Blank-#N/A, and if the #N/A is next to a blank cell, it does not get an interpolated line segment. Clever but cumbersome.

Among the VBA approaches I’ve seen (including those I wrote about in VBA Approaches to Plotting Gaps in Excel Charts) are routines that step through the data and clear any cells with #N/A or text, so they plot as blanks, or step through the chart and format any points with undesired markers or line segments to show neither. Of course, if the data changes, then other routines must be run to restore any formulas which were deleted and reformat any points. Cumbersome, and most examples you’ll find were hacked together by non-programmers who just needed something, anything, to get it done.

### New in Excel 2016: Plot #N/A as Blank in Excel Charts

In a recent build of Excel 2016, the behavior of #N/A in a chart’s values has changed. It is now possible to make Excel plot #N/A values as empty cells. The new feature was announced on the Microsoft Office blog in Display empty cells, null (#N/A) values, and hidden worksheet data in a chart. I’d like to think it was partly in response to my suggestion on Excel UserVoice, Give us a proper NULL() worksheet function. I was asking for a general function that would make any Excel formulas or charts treat a given cell as a blank cell, but my main interest was in the ability to chart the result of a function as a blank cell.

When the feature was first introduced to a subset of Insiders, there was no option; #N/A was always treated like a blank cell in a chart. The *Hidden and Empty Cell Settings* dialog was unchanged from previous builds (shown earlier).

Once Microsoft was confident that the feature had no show-stopping bugs, they rolled it out more broadly. Insiders were also treated to a new *Hidden and Empty Cell Settings* dialog, which features a checkbox to *Show #N/A as an empty cell*.

Notice that there is a space in the old dialog where the new checkbox appears. This space was cleared in Excel 2013, but only utilized recently.

This new feature has been rolled out not only to Office 365 subscribers, but also to regular Excel 2016 users. On my Office 365 laptop I have the full-blown *Show #N/A As An Empty Cell* behavior. On my non-Office-365 laptop, I get the new behavior, but I still do not have the new dialog, so I can’t toggle it on and off.

Now let’s see how this plays out in Excel charts.

## Plot Blank Cells in Common Excel Chart Types

Here is how a blank cell is plotted in a line chart, for all three options. Left, for *Show empty cells as: Gap*, there is a gap in the blank cell’s position. Center, for *Show empty cells as: Zero*, there is an actual data point (a marker and a data label) with a value of zero in the blank cell’s position. Right, for *Connect data points with line*, there is a line connecting the points on either side of the blank cell. Perfect.

Here is how Excel plots a blank cell in a column chart. Left, for *Show empty cells as: Gap*, there is a gap in the blank cell’s position. Center, for *Show empty cells as: Zero*, there is an actual data point (or at least a data label) with a value of zero in the blank cell’s position. Right, since it makes no sense to connect points with a line if the points are columns or bars, the *Connect data points with line* option (also known as the *Interpolate* option) is disabled; I’ve denoted this by using *gray italic text* in the chart title. You can still have that option in a column chart, though; for example if you start with a line chart with that option selected and then change the chart type, or if you apply the option with VBA. Since the interpolate option is disabled, Excel defaults to leaving a gap.

I’ve modified the column charts by extending the Y axis minimum to -2, and setting the *Horizontal Axis Crosses* property to -2. The tick label at Y=0 is colored red to draw attention to the altered axis settings.

The bars start at the axis (at Y=-2, not at Y=0), and extend upward to the Y value. We can now also clearly see the column with a value of zero, exactly what we signed up for by selecting *Show Empty Cells as Zero*.

Here’s how blank cells are plotted in an area chart. Left, for *Show empty cells as: Gap*, there is a gap in the blank cell’s position. Center, for *Show empty cells as: Zero*, there is an actual data point (or at least a data label) with a value of zero in the blank cell’s position. Right, Excel decides it makes no sense to connect points with a line (though it might have made sense to fill in the area as if there were a line), the *Connect data points with line* option (the *Interpolate* option) is disabled. You can still have that option in an area chart, the same as you can in a column chart. The interpolate option is disabled, so Excel defaults to plotting a zero-value point, not leaving a gap as in the column chart.

As I did with the column charts above, I’ve modified the area charts by extending the Y axis minimum to -2, and setting the *Horizontal Axis Crosses* property to -2 (note the red label at Y=0). You can clearly see that there is a data point at zero corresponding to the blank cell in the Y values.

## Plot Null String in Excel Charts

I’ll illustrate the problem in one set of line charts. In the data ranges below, the cell for the value corresponding to category “c” is a null string, created using the formula =””, as shown below the first chart. Regardless of the *Show Empty Cells* setting, the null string (and any text) is plotted as a zero value data point, with a marker and data label.

This is what people are trying to plot as a gap.

## Plot #N/A (as #N/A) in Common Excel Chart Types

Since time immemorial, in a line chart, no marker is drawn for a #N/A value, but a line is drawn connecting the markers on either side. So if all you wanted was to suppress drawing of a marker, this is great. If the series has lines connecting points and you want the line to connect adjoining points, this is great. If you don’t want the line but want a gap, this doesn’t do it. All three charts are the same: #N/A isn’t a blank cell, so it shouldn’t care what *Show Empty Cells* As setting the user has selected.

The *Don’t Show NA As Empty Cell* in the chart titles is to distinguish legacy behavior from the new *Show NA As Empty Cell* behavior I will discuss below.

In a column chart, no data point is plotted (which is great). However, if you have data labels on the series, you’ll get a label floating above zero. Here the label reads #N/A because the *Show Values* option is selected for the data labels. As with line charts, all three charts are the same.

In an area chart, there are inconsistent behaviors. The three charts should be the same, but the *Show Empty Cells As Gaps* also shows #N/A as a gap, even though it shouldn’t recognize #N/A as a blank. In the other cases, unlike the column and line charts, it plots #N/A as zero; unlike the column chart, it does not display a data label.

I suspect that some of the behaviors for plotting #N/A as #N/A have been adjusted from earlier versions by the new #N/A as Blank feature, but I have not bothered to track down my suspicions.

## Plot #N/A as Blank Cells in Common Excel Charts

This feature works pretty much as advertised. When enabled, the #N/A value is treated just like an empty cell. I will not show all combinations as I have above, but just a few highlights.

In the line charts below, we see #N/A values treated *As Gaps*, *As Zeros*, and *Interpolated*.

In the column charts below, we see #N/A values treated *As Gaps* and *As Zeros*, with *Interpolated* mimicking the *As Gaps* behavior.

In the area charts below, we see #N/A values treated *As Gaps* and *As Zeros*, with *Interpolated* mimicking the *As Zeros* behavior.

Just like in the examples shown for blank cells.

## Excruciating Detail: Plotting Blanks and #N/A Values in Stacked Excel Charts

This section was moved to the end, because it seemed to present too much information at once, and confuse more than clarify. But now that you’ve seen the comparisons above, it might be safe to continue.

### Plot Empty Cells in Stacked Excel Charts

These settings work differently in stacked charts. Excel’s rationale is that you can’t plot a gap, because the point stacked on top of the gap might have a value, and it needs something for it to be stacked on. If you are trying to plot an empty cell as a gap, and it isn’t working, make sure you aren’t using a stacked chart type. I should note that Excel does not apply this rationale consistently.

One of the most confusing chart types is a stacked line. People choose it when selecting a chart type, not realizing that each series’ values are plotted on top of previous values. (It’s much easier to notice stacked series and comprehend the data in a stacked column or area chart).

This is how Excel plots a blank cell in a stacked line chart (it has only one series, but it’s still a stacked line chart). The only option you can select is *Show as Zeros* (though you can apply the other settings through the roundabout means described earlier), and what you always get is a point at zero.

In a stacked column chart, you have both the *As Gap* and *As Zero* options available. *As Zero* results in a zero-height data point (i.e., bar), which we can see because of the data label; *As Gaps* seems to lack this data point, in conflict with Excel’s apparent decision that it needs a zero point to stack subsequent point on. The disabled *Interpolate* setting reverts to *As Gaps*. Note that in the stacked column chart, the bars start at zero, not at the axis.

When you add Series Lines to the stacked column chart (please don’t use these confusing bits of clutter), the series lines connect the bars on either side of the gap (left and right), and connect with the zero-height bar in when plotted *As Zero* (center).

If we stack another series of bars atop our series with the blank value, we see two things. The second bar has no problem stacking on the place a missing bar would be, and the series lines show an interesting crossing pattern.

Like the line chart, the stacked area chart only has the *As Zeros* option available, and you can see the zero-value point where the blank cell goes.

### Plot #N/A in Stacked Excel Charts

In a stacked line chart, #N/A is plotted as zero, since you need a point on which to stack other series. The #N/A zero points have markers but no data labels.

In a stacked column chart, the behavior is similar to plotting of blank cells. Even though the #N/A is supposedly not treated as a blank, there are different behaviors based on the *Show Empty Cells* setting: *As Gaps* results in no apparent point, because there is no data label, while the *As Zeros* setting apparently plots a bar 0 units high, evidenced by the #N/A label. Unlike in the unstacked column charts above, bars start not at the X axis but at zero, so we can’t actually see the zero-height bar. *Connect Points With Line* is undefined as before, but reverts to the *As Gaps* behavior.

When we add series lines, we see further evidence for the data point in the *As Zeros* case: the series lines drop to the top of the zero-height bar in the center.

In an area chart, only the *As Zeros* case is enabled, and whatever the setting, the appearance of the chart is the same: the #N/A value results in a point at zero, with no data label.

K Lee says

Thank you.

I wish you had enough time, and derived enough benefit from it, that you could post every day.

Always clear, methodical, ingenious and practical advice.

Jeff Weir says

For anyone stuck on older versions, it’s worth noting that there is(was) an inconsistency in Excel 2007/2010 (aka “New Excel”) compared with older versions whereby:

1) if your graph points to a range stored on the spreadsheet, then #NA errors in that range don’t show up in a graph (which is what we want) BUT

2) if your graph points to a range stored in a named range (i.e. the array is constructed by a formula or data entered into Name Manager) then errors are plotted as zeros, rather than not being plotted (as they were in old excel).

That is, if you go to name manager, and input say ={100,#N/A,#N/A} and point your graph series to this named range, then New Excel actually plots this as {100,0,0}. But if you input 100 into A1 and =na() into A2 and =na() into A3 directly on the spreadsheet and plot this, excel only plots the first point.

This bug meant that if your axis includes zero then you’re going to get any #N/A points plotted at zero. This could really slow the performance of a dynamic chart down if suddenly you’ve got an extra couple of hundred points plotting.

This seems to have been fixed in 2016 – possibly before this latest update.

Jon Peltier says

Jeff –

In Excel 2013, if I enter ={100,#N/A,#N/A} in the Name Manager, I get a marker at 100, and no markers for the other two points. If I enter ={100,”#N/A”,”#N/A”}, I get a marker at 100 and two at zero. Make sure Excel knows you’re entering the values as #N/A errors and not as text labels (this is a common problem).

Jeff Weir says

This bug was in Excel 2007 and 2010, so looks like it was fixed in 2013. And it was definitely programmatically induced errors, and not the text string equivalent. Daniel Ferry had a work around, that he mentions here:

http://www.excelhero.com/blog/2010/05/excel-animated-chart-2.html#comment-795

Jon Peltier says

I’m not denying that the bug actually existed, but I cannot reproduce it in Excel 2016, 2013, 2010, 2007, or 2003. I use #N/A and Names all the time, but I rarely define a Name as a hard-coded list, so maybe that’s why I never encountered it.

I’m not surprised such a bug existed in early releases of Excel 2007, since that version was really still in beta when it was released. They fixed a lot of charting issues in Excel 2007 SP2, and any that made it into 2010 would have been cleaned up in 2010 SP1. Takeaway: always apply the latest updates and service packs.

Excel 2013 has a much improved charting infrastructure (better UI, nicer color themes, etc.).