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

---

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions

or looking for a job ?
 Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP        OFFSET        Custom Format       Array Formula

---