How do you make a hole in a line graph?
 

So, this project I'm working on shows the percentage of reports submitted each month that are successful.
However, for some months, no reports are submitted, so there is no data. It doesn't really make sense to record it as 0% or 100%, since the month wasn't a total failure or totally successful. And it also doesn't make sense to show it as somewhere in between the months on either side, because that's not correct.

Ideally, the line would just stop at the previous month and start back up again at the next month.

Does anyone know a way to do this?

(I have my empty cell set as =NA(). And in the graph settings, I have "Show Empty Cells As:" set to "Gaps". I'm using Excel 2007)

The attached was created in Excel 2010 and it does what you're after automatically. Perhaps see if you can copy the attached graph into your workbook and link it to your data... You never know, Microsoft might stun us all and have something actually work well!!

That's a good thought, but as it turns out I don't think it will work because I have a formula in the cell. The formula sets the cell value to =NA() if there is no data available.

So when I opened your file (in Excel 2010), I tried putting =NA() and "" in the cell, and each time, it drew the line instead of leaving a hole.

Anyway, I think what I'm going to end up doing is putting the data in a bar graph instead of a line graph, so the empty spaces will for sure be empty. (The line is a running average trend line.)

An alternate solution was to make a macro that deleted the contents of any cells without data and connect it to a button on the sheet.

Anyway, thanks for your help!

Is there any chance you could post a dummy version of the table you're making the graph from?