Charting Date Gaps as Gaps

Hey everyone,

I've been searching for a solution to this for a bit, and maybe I'm just missing something, but I haven't found anything that'll do what I have in mind.

My data looks a little something like this:

2/26/11 - 5
2/27/11 - 6
2/28/11 - 7
4/3/11 - 8
4/4/11 - 9
4/5/11 - 10
etc.

(the "-" signifies a different column)

What I want to do is keep the x-axis smooth and uniform (have the x-axis plot all the dates, even the ones not identified), which is does already, but I don't want Excel to interpolate the values. In other words, when there's a date gap in my data, I want Excel to plot it as exactly that - a gap.

I figured that if it comes down to it, I could write up a code (or find one) to interpolate the dates into the date column from which I'm grabbing the data from, so then it would at least have a zero point to plot, but then I'd fill my data sheet with a whole bunch of blank rows, which I'd like to avoid.

Anyways, any help would be greatly appreciated.

Thanks.
-Reactant
(Excel 2010)

Andy,

Thanks for replying.

Unfortunately, that doesn't seem to fix my problem.

Original.JPG
Changed Settings.JPG

I've attached two pictures - the first is my original graph, while the second is after I changed the "Hidden and Empty Cell Settings", turning the "Show data in hidden rows and columns" to on. That was the only setting in the dialogue box that had any effect on the chart.

Basically, from my original graph, I'm hoping to cut out those lines that connect two non-adjacent data points (in terms of their dates - I'd like a line to connect 11/18, 11/19, 11/20, etc., but not a line connecting 10/20 to 11/18, if that makes sense).

I'm guessing this is a result of the way my data is laid out - again, I probably could populate the list so that it includes ALL the dates between data points, so I would actually have empty cells (in which case, I could go to the Hideen and Empty Cell Settings, I think, and solve my problem), but I don't necessarily want to have all those empty cells on my data sheet either.

I really appreciate the help though.
-Reactant

I've put together a little workbook that basically gets to the crux of my problem.

The top graph/data set is more or less what I have - big jumps in my date range, but no intermediate empty dates.

The bottom graph is how I want the data to display (with the gap). I got around my problem in this case by adding the empty date cells (as is apparent). However, my real data is much longer, and I don't especially want those empty cells. If that's the only solution, then so be it, but I thought I'd ask here to see if there was a simpler way.

Let me know if I need to clarify anything.
Thanks again - I really appreciate your help.

-Reactant

Ahh, that works perfectly. A little bit of a hassle, but MUCH better than putting ALL of the dates in.

Thanks again Andy.
-Reactant