Blank Cells

Plot Blank Cells and #N/A in Excel Charts

Wednesday, February 7, 2018 by Jon Peltier 21 Comments

Wednesday, February 7, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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. New behavior introduced in Excel 2016 makes these older workarounds unnecessary.

tl;dr

A new feature introduced in a late build of 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 that 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 the 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.

Plot Blank Cells in Stacked Column Chart

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 when plotted As Zero (center).

Plot Blank Cells in Stacked Column Chart with Series Lines

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 the 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.

Plot #N/A in Stacked Column Chart with Series Lines

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.

Posted: Wednesday, February 7th, 2018 under Data Techniques.
Tags: Blank Cells, UserVoice.
Comments: 21

Another approach to plotting gaps in Excel charts

Monday, March 10, 2014 by Jon Peltier 10 Comments

Monday, March 10, 2014 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Note

A new feature in Office 365 (and Excel 2019), 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.

Read about it in Plot Blank Cells and #N/A in Excel Charts.

This new behavior in Excel makes this article’s workaround obsolete.

Constable Henry: Just a moment, sir. I know everyone in this neighborhood, but I’ve never seen your face before. Can you account for yourself?

Freddy: Yes, I am Dr. Frederick Fronkonsteen, newly arrived from America.

by The FrankensTeam

This guest post is from the E90E50 FrankensTeam, i.e., Roberto Mensa, Kris Szabó, and Gábor Madács. The team has done a great deal of work with Excel and its graphics, and they’ve shown how many types of “advanced” graphics can be accomplished right in Excel. Such FrankenCharts include heatmaps, treemaps, network diagrams, and a number of other creative displays. Oh, and they’re as crazy about the movie Young Frankenstein as they are about Excel.

While I was reading Jon’s work using VBA Approaches to Plotting Gaps in Excel Charts I started to think, because I felt there had to be another solution which was waiting to be discovered…

Jon provided two different VBA routines as a solution for the problem: one changes the chart series formatting, the other changes the underlying data. As he clarifies at the very beginning, the disadvantage of using VBA routine is that it must be re-run every time the data changes.

Some time ago I realized that it is possible to use union of ranges in chart series. You can do it by using the union reference operator in the SERIES formula of the chart or via named formula. (The union reference operator is the same as the formula separator character, which is a comma in US-English versions of Excel.) In the project where it came up it was not particularly interesting, so I just stored this fact in my mind.

Now, a few months later, thinking about the gap problem… Eureka!

The union of separate ranges could be a solution, especially if we can build it up using the UDF within a named formula!

I was very excited by this possibility, so I quickly put together some code and shared the idea with Jon, who suggested some modifications in the UDF. Then we did a lot of tests with Kris and Gábor and corrected the problems showed up while we tested the solution in different Excel versions.

Let us explain how it could work.

Below is a simple line chart plotting data from column B: the #N/A error value is not plotted (no marker) but the line is continuous, while the string is plotted as zero.

The series formula of the chart looks like:

=SERIES("ref to rng",,line_chart!$B$3:$B$13,1)

Where y values come from a continuous range: $B$3:$B$13

The idea

To plot gaps on the chart, you will need empty cells in the range used for y values. The union of ranges idea is very simple: we use the cells containing the number values from the original range (column B) but use empty cells from another column (column C) instead of the error or text values.

You can easily select the non-continuous range by mouse while holding the Ctrl key down.

On the below picture you can see how it looks from the Edit Series dialogue box.

And this is how the SERIES formula shows the union:

=SERIES("ref to rng",,
    (line_chart!$B$3:$B$5,line_chart!$C$6,line_chart!$B$7:$B$9,line_chart!$C$10,
    line_chart!$B$11:$B$13),1)

Note the y range is now surrounded by brackets, since it is composed of multiple areas.

You can easily define a named formula for the above range:

Name: y_rng_union
Refers to: =$B$3:$B$5,$C$6,$B$7:$B$9,$C$10,$B$11:$B$13

And use it as Series values:

Formula solution

To make a solution a bit more dynamic, instead of using static ranges, you can put together a dynamic solution with IF formulas. You have to check the cells one by one, and if the cell has a value to be plotted, then use the reference of the cell, else use an empty cell from another column. Then you separate the IF formulas by comma (or semicolon) to create the union of unique cells.

This way the chart will automatically be updated if the data changes, but if you have more data to be plotted, you will have to update the named formula manually.

For example you can put a similar formula to the name, supposing that column C is empty, so use the cells of this column:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,$C$3), IF(ISNUMBER($B$4),$B$4,$C$4), IF(ISNUMBER($B$5),$B$5,$C$5), ..., IF(ISNUMBER($B$13),$B$13,$C$13)

Or you can use only one empty cell (for example C3) instead of the cells of column C:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,$C$3), IF(ISNUMBER($B$4),$B$4,$C$3), IF(ISNUMBER($B$5),$B$5,$C$3), ..., IF(ISNUMBER($B$13),$B$13,$C$3)

More generally, you can use a name (for example x) referring to an empty cell:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,x), IF(ISNUMBER($B$4),$B$4,x), IF(ISNUMBER($B$5),$B$5,x), ..., IF(ISNUMBER($B$13),$B$13,x)

If you are interested in the above formula solution, you may also find useful a help formula in an Excel range to build up the above (very long!) formula.

The logic is simple: A copy-down formula will concatenate together all the necessary IF(ISNUMBER(...) parts.

(Please note, if you use non-english versions of Excel, you will need to replace IF and ISNUMBER with your local language formula names. Also, you will have to replace the separator string (comma) if your version uses semicolons.)

For example, supposing that the header of your data is in B2, and data starts in B3, write this formula in cell M3 and copy down:

=IF(ROW(A1)=1,"=",M2&",")&"IF(ISNUMBER("&CELL("address",B3)&"),"
&CELL("address",B3)&",X)"

In our example we have to copy down this formula to cell M13 (since data ends in row 13). The string we get in M13 is exactly the formula we need to use in y_rng_union, so we copy M13 cell and paste as value. Now the formula could be copy-pasted as a reference into the name:

For better understanding, you can check these solutions in the without-udf example file.

Obviously the formula solution is not really effective and has limitations, but it could be an alternative for those who can not use VBA.

General solution with UDF-based named formula

For a more convenient resolution we will need some VBA code: a user defined function will build up the union-range and provide it to the chart, nested in a named formula. So if your data changes, the union range will change accordingly and if your data range grows, the chart will also capture new data.

Important to highlight that it will happen automatically, together with calculation, without any user interaction!

Here is the UDF code:

Function serie_with_empty(rDataIn As Range, _
        Optional cOffset As Long = 1)

    Dim rCellIn As Range
    Dim rCellOut As Range
    Dim rDataOut As Range

    Application.Volatile

    For Each rCellIn In rDataIn.Cells
        If Not IsNumeric(rCellIn.Value) Then
            Set rCellOut = rCellIn.Offset(, cOffset)
        Else
            Set rCellOut = rCellIn
        End If

        If rDataOut Is Nothing Then
            Set rDataOut = rCellOut
        Else
            Set rDataOut = Union(rDataOut, rCellOut)
        End If
    Next

    Set serie_with_empty = rDataOut

End Function

The procedure loops through the data range (first argument) and checks for non-numeric values. If the value is numeric (so to be plotted) then the cell itself is added to the union-range. If the value is not numeric, then another cell determined by offset will be added to the union. At the end, the function returns a range object.

This range object could not be used directly on the chart, it must be wrapped in a named formula.

To avoid errors when UDF could not be run (because macros are disabled) we suggest using an error check in the named formula:

Name: y_rng_union
Refers to: =IF(TYPE(serie_with_empty(rng,0))=16+NOW()*0 ,rng, serie_with_empty(rng,1) )

Where rng is the original data range, serie_with_empty(rng,1) is the UDF-calculated union-range.

The second argument of the UDF is the column offset parameter. Above we use 1 supposing that the column next to the original data is empty. You have to ensure a sufficient number of cells in this column. The offset parameter is optional with 1 as default value.

You can find more details about the logical test part of the IF formula under section Further details.

Using the above defined name on the chart, the result is correct with gaps, and refreshes as your data or underlying range changes.

The method could be used for xy scatter charts too. For this chart type only the y values need to have empty cells for gaps, so no need to change the original x values range. As an example here is a possible SERIES formula of an xy chart:

=SERIES("ref to y_rng_union" , line_chart!$D$3:$D$13 ,
   Serie_with_empty_udf_xy.xlsm!y_rng_union , 1)

with the arguments:

x values (normal range): line_chart!$D$3:$D$13

y values (UDF calculations): Serie_with_empty_udf_xy.xlsm!y_rng_union

where line_chart is the name of the worksheet containing the x values, and Serie_with_empty_udf_xy.xlsm is the name of the workbook.

You need to reference the name y_rng_union to the worksheet or workbook. I usually start my chart with a regular range in the formula, so I leave the worksheet name and exclamation point intact, and replace the address with the named formula, like line_chart!y_rng_union. If necessary, Excel will change the worksheet name to the workbook name.

Below you can see what our example xy chart looks like, illustrated with ranges as plotted.

We created two example files, one for line chart, another for xy chart. We used random data, so after pushing F9 you can see how data, ranges and chart changes.

Further details

We need to test if the UDF was run or not.

In practice, macros are usually disabled in the Trust center under Excel Options. When you open the file, Excel will try to refresh the chart, but the UDF code will be unable to run, so the range will not be correctly built. The chart will raise an “Invalid reference” error message. Unfortunately, after enabling the macros, the chart will not be refreshed, because it remembers that the reference is invalid.

If the UDF can not run it results a #NAME error, which is number 16 in the TYPE formula. In this case the IF formula will give the original range, so the chart will get a valid range – although the line will not have gaps. To see the correct gaps, you need to enable macros and trigger calculation (so push F9). We use a volatile formula in the name ( NOW()*0 ) this is necessary for the chart to be updated after calculation. (The chart ignores the Application.Volatile in the UDF code!)

Problems with `Cell.Text` property

The base concept of gaps on the chart is to plot only numbers. In the UDF,

If Not IsNumeric(rCellIn.Value)

tests the cell’s .Value property. We were intending to provide another possibility for the users to mark unplottable data using formatting. For example they could set a custom formatting to hide 0 values if these should not be plotted. In VBA Cell.Text property could be used to get cell’s text as displayed on the screen, but using .Text property caused Excel crashes in case of xy charts. In case of line charts we have not observed crashes, but the SERIES formula was not visible on the formula bar, so we stopped using the .Text property.

About the authors:

Roberto Mensa, Kris Szabó, and Gábor Madács are three Excel-enthusiastics from Europe, thrilled to push the boundaries of Excel and do what seems to be impossible. You can find more about them on their team-site.

Posted: Monday, March 10th, 2014 under Guest Post.
Tags: Blank Cells, SERIES Formula, UDF, User Defined Functions, VBA.
Comments: 10

VBA Approaches to Plotting Gaps in Excel Charts

Monday, February 17, 2014 by Jon Peltier 18 Comments

Monday, February 17, 2014 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Note

Read about it in Plot Blank Cells and #N/A in Excel Charts.

This new behavior in Excel makes this article obsolete.

In Mind the Gap – Charting Empty Cells, I described in gory detail how Excel’s various chart types treat empty cells, that is, cells that are totally empty. I also described why none of the approaches we mortals have ever tried to produce a gap across a simulated blank cell has ever worked.

Cells that have formulas that return a null string (i.e., “”) are plotted like any text, with a marker at a value of zero. In line and XY Scatter charts, an error value of #N/A almost works, because it suppresses plotting of a point, but does not produce a gap in the line segments connecting points.

The only way to get a gap in the lines of a chart is to have empty cells is to rely on a VBA routine to correct the appearance of the chart, whether by changing the formatting of the points and lines you want to hide, or by clearing the contents of the associated cells. These work well, but must be repeated every time the data changes, and if you’ve cleared any cells, their contents must be recreated in case those points should no longer show gaps.

This post contains two routines to fix the chart’s appearance, one by changing the chart series formatting, the other by changing the data.

The Gap Problem

Below is a simple data range I created to illustrate this problem. The “Broken” column shows a set of random values centered around 3, with either #N/A or “x” inserted into random cells in place of the random values. The “x” is text, so it will be treated the same as “”, but it is used instead so it shows up in the cells.

In the “ChartFix” and “DataFix” I’ve included this same data, increased by 1 so I could plot it with the “Broken” data without obscuring it. I’ll use two XY Scatter charts, one to show how fixing the chart formatting works, the other to show how fixing the data works.

Here are the two charts I promised. The lower (blue) series is the “Broken” data, which will not be changed by these routines. The upper (orange) series is the “ChartFix” series (left) or the “DataFix” series (right).

Both series in both charts show a missing marker at X=5, with a line connecting the points at X=4 and X=6. These missing points correspond to #N/A errors in the corresponding cells. All four series also show a point plotted at X=15, Y=0, corresponding to the “x” value in the corresponding cells.

We want our code to leave no markers and no connecting lines at these points in the orange series, while the blue series remain intact to remind us where changes were made. In general, you will probably want to process all series in your charts.

The Code

Both procedures parse the series formula (see detailed documentation at the end of this article) to find the ranges containing the X and Y values. If you are not using XY Scatter charts, remove the X value components of the code, because non-numeric X values are allowed for other chart types.

The first procedure loops through the parsed X and Y values, and where it finds a non-numeric value, it formats the point to have no marker and for the connecting line segment on either side to show no line. Where it finds a numeric value, it restores marker and line segment formatting, in case the chart already was “fixed” but now the data has changed.

Sub FixLineFormatInChart()
  Dim iPt As Long
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sXVals As String
  Dim sYVals As String
  Dim rXVals As Range
  Dim rYVals As Range
  Dim vXVals As Variant
  Dim vYVals As Variant

  With ActiveChart
    ' just process the orange series
    With .SeriesCollection(2)
      sFmla = .Formula
      vFmla = Split(sFmla, ",")

      sXVals = vFmla(1)
      sYVals = vFmla(2)

      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      vXVals = rXVals.Value
      vYVals = rYVals.Value

      For iPt = 1 To .Points.Count
        If IsNumeric(vXVals(iPt, 1)) And IsNumeric(vYVals(iPt, 1)) Then
          .Points(iPt).MarkerStyle = .MarkerStyle
        Else
          .Points(iPt).MarkerStyle = xlMarkerStyleNone
        End If
      Next

      For iPt = 2 To .Points.Count
        If IsNumeric(vXVals(iPt - 1, 1)) And IsNumeric(vXVals(iPt, 1)) And _
            IsNumeric(vYVals(iPt - 1, 1)) And IsNumeric(vYVals(iPt, 1)) Then
          .Points(iPt).Format.Line.Visible = True
        Else
          .Points(iPt).Format.Line.Visible = False
        End If
      Next
    End With
  End With
End Sub

The second procedure loops through the parsed X and Y values, and where it finds a non-numeric value, it clears the cell containing the non-numeric value. When rerun on a chart with changed data, it cannot restore the appropriate cell contents where it finds a numeric value, because the relevant formula or value was previously deleted.

If you plan to use this approach, it is best to leave the original data or calculations intact, and use another worksheet range that simply links to the original data, so the links are easy to recreate.

Sub FixSourceDataInSheet()
  Dim iPt As Long
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sXVals As String
  Dim sYVals As String
  Dim rXVals As Range
  Dim rYVals As Range
  Dim vXVals As Variant
  Dim vYVals As Variant

  With ActiveChart
    ' just process the orange series
    With .SeriesCollection(2)
      sFmla = .Formula
      vFmla = Split(sFmla, ",")

      sXVals = vFmla(1)
      sYVals = vFmla(2)

      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      vXVals = rXVals.Value
      vYVals = rYVals.Value

      For iPt = 1 To .Points.Count
        If Not IsNumeric(vXVals(iPt, 1)) Then
          rXVals.Cells(iPt).ClearContents
        End If
        If Not IsNumeric(vYVals(iPt, 1)) Then
          rYVals.Cells(iPt).ClearContents
        End If
      Next
    End With
  End With
End Sub

The Results

I selected the first chart (title = “Change Chart”) and ran the first procedure (FixLineFormatInChart). Then I selected the second chart (title = “Change Data”) and ran the second procedure (FixSourceDataInSheet). Here is the resulting data. Note that the ChartFix data is unchanged because the FixLineFormatInChart procedure only changes the chart, while the DataFix data now has a couple of blank cells because the FixSourceDataInSheet works by changing the data and leaving the chart alone.

Here are the resulting charts. The two procedures produce identical results, changing the interpolated line across point 5 (the #N/A value) to a gap, and changing the plotted zero at point 15 (the text label “x”) to a gap. The interpolated line across point 5 and the zero value plotted at point 15 remain in the blue series.

What if the Data Changes?

Worksheets are not always static pictures of data. The original data may change, or you may perform the same analysis with new data. The following table represents new data overlaid on the previous data range.

The original non-numeric cells now contain numeric values, but we’ve gained text values in rows 4 and 5. Because the previously non-numeric cells in column D were cleared, whatever formulas we had there could not recompute new values for cells D6 and D16. In the charts from before, we still see gaps, whether because we formatted them not to appear (left) or because we deleted the values (right).

We’re fine if we used the Change Chart approach, or if we simply pasted values on top of the data, which filled in any blank cells from before. But if we deleted formulas, as in column D above, we need to restore them, as shown in the worksheet range below.

The chart with reformatted points still shows gaps (left) but the formatting will be restored to numeric points by the procedure. The chart with unchanged formatting and restored data (right) shows all markers and line segments. The text values are plotted as the two zeros near the left edge of these charts.

We run the corresponding procedures on the two charts. The data is changed in column D, as before.

The orange series in both charts now show gaps where the blue series remind us of the text values plotted as zero.

Parsing the Series Formula

A series formula has the following form:

=SERIES(Sheet1!$C$2,Sheet1!$A$3:$A$19,Sheet1!$C$3:$C$19,2)

The four arguments of the series formula are:

Series Name – can be a worksheet address or defined name, a text label, or empty. In this example it’s a cell reference, Sheet1!$C$2.
X Values (Category Labels) – can be a worksheet address or defined name, a literal array such as {1,2,3} or {“A”,”B”,”C”}, or empty. In this example it’s a worksheet address, Sheet1!$A$3:$A$19.
Y Values – can be a worksheet address or defined name, or a literal array such as {1,2,3}. In this example it’s a worksheet address, Sheet1!$C$3:$C$19.
Plot Order – an integer, in this case 2.

Generally, to parse the series formula, you first need to strip off everything except the parameters, which means the open parenthesis and the preceding text, and the closing parenthesis. Then you split the resulting string into a comma-separated array. This works as long as none of the ranges contain multiple areas, in which case there are commas separating the addresses of the individual areas, and simple separation by commas will produce surprises. The result is a zero-based array.

Since we only want the X and Y values, we don’t care that the first parameter will contain the opening parenthesis and the preceding text, or that the last parameter will contain the closing parenthesis.

So our code looks like this, with loads of nice documentation:

    With .SeriesCollection(2)
      ' get series formula
      ' =SERIES(Sheet1!$C$2,Sheet1!$A$3:$A$19,Sheet1!$C$3:$C$19,2)
      sFmla = .Formula

      ' split formula into its arguments
      vFmla = Split(sFmla, ",")
      ' vFmla(0) = "=SERIES(Sheet1!$C$2"
      ' vFmla(1) = "Sheet1!$A$3:$A$19"
      ' vFmla(2) = "Sheet1!$C$3:$C$19"
      ' vFmla(3) = "2)"

      ' get the individual addresses for X and Y
      sXVals = vFmla(1)
      sYVals = vFmla(2)

      ' find the ranges containing the X and Y values
      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      ' put the values from the X and Y ranges into arrays
      ' (faster processing in an array than cell-by-cell in a range)
      vXVals = rXVals.Value
      vYVals = rYVals.Value

Now we can test for non-numeric values in the arrays, and either clear the corresponding cells or change the formatting of the plotted points.

You can put the X and Y values of a series directly into an array as follows:

      vXVals = ActiveChart.SeriesCollection(2).XValues
      vYVals = ActiveChart.SeriesCollection(2).Values

The problem here is that the values saved internally in the series have already been changed. The #N/A remains #N/A in the array, but any blanks and any text values are converted to zeros, so they are undetectable as non-numeric.

Posted: Monday, February 17th, 2014 under VBA.
Tags: Blank Cells, VBA.
Comments: 18

Area Chart With Gap

Tuesday, March 23, 2010 by Jon Peltier 11 Comments

Note

Read about it in Plot Blank Cells and #N/A in Excel Charts.

This new behavior in Excel makes this article obsolete.

In Mind the Gap – Charting Empty Cells I showed how the various Excel chart types treat blank cells, through various settings like Leave a Gap, Treat as Zero, and Interpolate.

In that article I wrote:

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.

Area Charts, With Empty Cells Treated as Zeros, or Supposedly Interpolated

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.

Area Charts, With Empty Cells Treated as Gaps, if We Had Our Way

How rude of me to hint at an approach, but not to share it!

For this chart appearance, you need a line chart series to provide the visible category axis, and an area chart on a hidden date scale axis to provide the visible area chart. Here is the data range:

Data for Area Chart With Gap Having Vertical Sides

Recall a date scale axis shows multiple Y values on the same category in a vertical line, which will provide the vertical sides to the gap in the area chart. Start by making a line chart with the red-shaded range. Copy the area chart data in the blue range, select the chart, and use Paste Special to add the data as a new series.

Line Chart, with Area Chart Data Added As Another Line

Change the area chart series to an area chart type, then assign the area chart tot he secondary axis.

Area Chart Series Changed to Area Type and Moved to Secondary Axis

Excel probably only provided a secondary Y axis, so add a secondary X axis, using a date scale rather than a category scale. Hide this secondary axis by using no line, no axis tick marks, and no axis tick labels.

Secondary Date Scale Category Axis Added, Then Hidden

Format the secondary Y axis so that the secondary X axis does not cross at the maximum (it will default to zero), then delete the secondary Y axis.

Secondary X Axis Moved to Y=Zero, Secondary Y Axis Deleted

Hide the line chart series by formatting it to have no lines and no markers. Add data labels to the area chart series.

Line Chart Series Hidden, Data labels Added to Area Chart Series

Use a number format like 0;;; for the area chart data labels, to hide labels for zero values.

Data Label Number format Changed to Hide Zero Labels

As I said, it’s easy, if you know how.

Posted: Tuesday, March 23rd, 2010 under Chart Types.
Tags: Area Charts, Blank Cells.
Comments: 11

Mind the Gap – Charting Empty Cells

Thursday, July 30, 2009 by Jon Peltier 101 Comments

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.

Note

Read about it in Plot Blank Cells and #N/A in Excel Charts.

This new behavior in Excel makes this article obsolete.

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.

XY and Line Charts, With Empty Cells Treated as Gaps

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.

XY and Line Charts, With Empty Cells Treated as Zeros

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.

XY and Line Charts, With a Line Interpolated Across Empty Cells

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.

Column Charts, With Empty Cells Treated as Gaps and as Zeros

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.

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.

Pie Charts, Without and With Empty Cells

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.

XY and Line Charts, Empty Cells Simulated by Zero-Length Text

Column and Area Charts, Empty Cells Simulated by Zero-Length Text

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.

XY and Line Charts, Empty Cells Simulated by #N/A Error

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;;”).

Column and Area Charts, Empty Cells Simulated by #N/A Error

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.

Pie Charts, Empty Cells Simulated by Zero-Length Text and by #N/A Error

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.

Update

Microsoft recently launched the Excel UserVoice channel, and I proposed Give us a proper NULL() worksheet function. The suggestion has received 164 votes (as of 9 January 2016), I’ve had discussions with Microsoft Excel Product Group people so they understand how I would use this function, ind it is now officially listed as “Planned”. So Microsoft is listening, and hopefully soon we’ll get a function that will help with this in an upcoming update of Excel 2016.

Update 2

A new feature in Office 365 (and Excel 2019), 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.

Read about it in Plot Blank Cells and #N/A in Excel Charts.

Posted: Thursday, July 30th, 2009 under Data Techniques.
Tags: Blank Cells.
Comments: 101

Blank Cells

Plot Blank Cells in Excel Charts

tl;dr

Excel’s Hidden and Empty Cell Settings Dialog

What’s a “Blank Cell”, Anyway?

Use #N/A Instead of Blanks

Other Workarounds

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

Plot Blank Cells in Common Excel Chart Types

Plot Null String in Excel Charts

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

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

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

Plot Empty Cells in Stacked Excel Charts

Plot #N/A in Stacked Excel Charts

Note

The idea

Formula solution

General solution with UDF-based named formula

Further details

We need to test if the UDF was run or not.

Problems with Cell.Text property

About the authors:

Note

The Gap Problem

The Code

The Results

What if the Data Changes?

Parsing the Series Formula

Note

Note

Plotting Without Empty Cells

Plotting With Empty Cells

Simulating Empty Cells

Summary

Update

Update 2

Problems with `Cell.Text` property