BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

How to Prevent Blank Cells from plotting in Excel Charts?

How many times have you created a chart in Excel and seen something like this?
That dip on the graph is because somewhere in your worksheet you have a blank cell. As well as being annoying to you as the chart creator, it is also misleading to anyone who tries to interpret what your chart is trying to tell them!
The obvious solution might have been to delete rows, or to put some values in the blank cells to pad out the data.
However this will end up being even worse... Either omit important information, or visualize incorrect data that wasn't there to begin with.

How can we create a chart that is accurate but takes into account those rogue cells?

Creating a Non-Continuous Line Graph

What we need is a chart like this one below...

Here are the instructions:
  1. Make sure the graph type is Line and not Stacked Line
  2. Select the chart
  3. In the chart menu click on: Design -> Select Data
  4. In the dialog that comes up, click the 'hidden and empty cells' button
  5. Then select 'gaps', and click OK.
There, isn't it easy once you see how it is done?

About the author of this article
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter








Jon Peltier said...

The default setting is to treat blank cells by placing gaps in the chart.

The problem is usually that the user has a formula that returns "". It looks blank to us mortals, but Excel knows that "" is text (albeit a short piece of text), and Excel automatically assigns text a value of zero. No blank cell setting anywhere (gap, interpolate, whatever) will change the treatment of a non-blank cell.

The best you can do is change "" in your formula to #N/A or NA(), either of which place the #N/A error into the cell. This error is not plotted as a point in an XY or Line chart, and if the series has lines, the lines interpolate across the #N/A.

Ashish Jain said...

Fantastic Jon :)


PS: It's my and my blog's pleasure to receive comments from an expert like you. I'm a big fan of you and Walkenbach :)

Anand Kumar said...

I also prefer NA() function as Excel treats text or even blank spaces as zero value. And yes I learned this trick and a lot others by following articles and comments by Jon Peltier and his website PeltierTech.

Regards,
Anand Kumar

Anil said...

Oh shit... its so simple... and i am doing everytime by doing that portion colour as white...

Thanks... thank you very much...

you are simply awesome

Beth said...

Any chance you know how to do this on Excel for Mac (2008)? I seem to be lacking the all powerful Hidden and Empty Cells Button on the Select Data Source page and I have no idea where to find it or if it's possible in the Mac version.

Ashish Jain said...

Hi Beth,

Change "" in your formula to #N/A or use NA(), hence the error won't plot in the chart. (Referenced from Jon)


Regards
Ashish Jain

Meighan Manor Horror Show said...

I have transposed a bunch of rows of data as an array using "=Transpose (Ax:By)" function. This returns a nice array of the correct numbers. However, where there were originally empty cells, zeros appeared in the array, which got plotted in my chart. I don't want zeros plotted on my charts. I want them to be ignored and the lines interpolated instead. So I replaced the empty cells with blanks (space bar). This resulted in apparently empty cells in my array, but they are still plotted as zeros in my chart, even when I tell Preferences not to treat empty cells as zeros, and to interpolate or leave gaps instead. Any ideas?

Excel 14.3.4 (Mac)