Excel offers a few ways to deal with empty cells in a chart’s source data range. This is the cause of much confusion, especially over the definition of “empty cells”. Let’s take a look at this problem.

Plotting Without Empty Cells

Here are some typical Excel charts, to remind us what they look like with fully populated data ranges. I’ve placed data labels on the charts to help show their behavior when empty cells (and other non-numeric fillers) are included in the source data.

Plotting With Empty Cells

The default behavior for most charts is to treat an empty cell by leaving a gap on the chart. Here are XY and Line charts that show how this looks. Note the lack of data labels where the gaps occur.

You can instead have Excel treat blanks as zeros (which in general is deceiving, since empty cells mean the absence of a value, not a value of zero). Note the data labels indicating a value of zero.

You can also tell Excel to interpolate over the missing data point. No data point means no data label.

Purists may say that the interpolation option may cause readers to think there is data along the line that spans the gap. Maybe so, but if the line or XY chart plots actual data points with markers, this is less of a risk.

Column charts offer only the gap and zero options for empty cells. Interpolating makes no sense in a column chart, which has discrete bars for each actual data value. The chart with a gap has no data label at the gap, the chart with zeros has a zero data label.

If we change the vertical axis so that the horizontal axis crosses below zero, we see that the first chart has no data point (bar) corresponding to the gap, but the second has a point (bar) with a value of zero.

Area charts seem to offer both the zero and interpolation options, but in both cases, the chart plunges to zero without a corresponding data label. If we mess with the vertical axis, the chart still plunges exactly to zero in both cases.

A more suitable appearance for an area chart would be one that leaves a real gap, with vertical edges, as below. To get this I had to make a two-axis chart, with a hidden series on the primary axis to provide the A-B-C category axis labels, and an area chart on the secondary axis, with two points at the second category (Y=4 and zero) and two at the fourth category (Y=zero and 8). A date scale on the secondary category axis aligns these points above each other to produce the vertical-sided gap. This only took me three minutes to construct, but an average user might never quite get it.

The protocol to produce an area chart with a gap that has vertical sides is given in Area Chart With Gap » Peltier Tech Blog

Pie charts don’t seem to offer any options for dealing with blanks, but the grayed-out default is the gap option. In the chart at the right, there is no point (wedge) and no corresponding data label where the empty cell would be plotted.

Apparently all charts except the Area chart provide a non-plotted point for an empty cell in the data range, though we can spoil that by selecting the option to treat a gap as a zero value.

Simulating Empty Cells

The main confusion comes along when someone uses formulas to fill the source data range.

The problem arises because  a formula that links to an empty cell

=A1

doesn’t display a blank, it displays the value zero.

Unfortunately Excel has no formulaic way to simulate a blank in a cell. There is no formulaic way, then, to get an actual gap in a chart. A function like BLANK() or NULL() would be nice, and we’ll keep asking. But we won’t hold our breath. And if we really need it, we can hack something in VBA.

Andy Pope shares a workaround in Broken Lines for formula linked data, but it requires one or more dummy series with a line color matching the chart background, which obscure the existing line where the gaps should go.

What people try to do to fake a blank is return “” in the formula.

=IF(A1=”",”",A1)

The result looks to you and me like a blank, but to Excel, “” is a text string, albeit a short one, and it is therefore assigned a value of zero. These charts use “” in a logical yet misguided attempt to simulate an empty cell. All charts plunge to zero, and all display a data label showing the value zero.

People have learned that the #N/A error in a worksheet cell is often not plotted in a chart. In a formula, the function NA() returns this error.

=IF(A1=”",NA(),A1)

What #N/A does is prevent the rendering of a marker in XY and line charts: notice the lack of markers and data labels. It’s not a gap, but it’s pretty much the next best thing.

This doesn’t work as well in other chart types. The column and area charts place a data label showing #N/A at zero. For these chart types it would be better to use “” or even zero, and apply a custom number format that suppresses the display of zeros (a format like “0;-0;;”).

A pie chart will behave like a column or area chart: there is no visible point (wedge) but there is a data label showing zero or #N/A where the wedge would be located.

Summary

The least deceptive way to display an empty cell in a chart, is to treat it as a blank. In line or XY charts, it is generally acceptable to interpolate across the blank with a line, if there are markers plotted for every actual data value. In the vast majority of cases, it is not appropriate to treat empty cells as zeros.

To simulate an empty cell in a line or XY chart, use NA() in your formula to produce the #N/A error in the cell. This produces an ugly #N/A error in the cell, but this error suppresses plotting of a point, and the line spans the position corresponding to the error’s slot in the data range. It’s not as good as a gap, but it’s perhaps the next best thing.

In an area or pie chart, the best approach is to use the null string “”, in conjunction with a number format that suppresses the display of zero values.

For an area chart you have to stand on your head to create a complicated chart that simulates a vertical edged gap and provides appropriate category axis labels.

Alternatively you could rely on VBA to clear cells which need to be cleared, but this routine would have to run every time the data range is changed, and it would have to reintroduce formulas into previously cleared cells as well as clear cells where blanks are needed. Or it could do all of the calculations in VBA, eliminating the worksheet formulas.

Related Posts:

• Area Chart With Gap
• Label Last Point for Excel 2007
• Label Each Series in a Chart
• Callout Labels with XY Line Segments
• Interactive Charts with Checkboxes and Formulas
• Link Chart Text to a Cell
• How to Build a 2×2 Panel Chart
• Chart Busters – Compare Employee Sales
• Fake Line Chart (Dummy XY Series for X Axis)
• Callout Labels with Error Bars