I have some data that is plotted against dates (days).

Now, if the data is "continuous" (meaning, I have a data point for every day), there is no problem -- I can just let Excel do "auto" on everything and a "line" chart works as expected, when plotting the data point (Y-Axis) against the date (X-Axis).

However, if there are gaps in the data, (and the dates) - where I have a date+data value on eratic days, the normal line plot doesn't "work" properly... So when comparing plots from one 10-day period against anothe 10-day period for example, they are not visually comparable, as the X-Axis values are missing.

Is there a way that I can specify the X-Axis scaling to still do the plotting 'every day' and now show any data point when the data is missing... OR will I need to do some machinations with multiple columns and formulas, etc to "build" a range with the continuous days and build the chart as usual?

I simply want to use the 'standard' features of Excel and not have to do something with VBA, etc.

Edit: Thanks for the clues about Scatter plots... Although, I'm still trying to find the 'break on gaps in data' -type setting... An example:

image of different types of charts

  • 1
    take a look at scatter plots instead of line plots. Scater plots will place data points at their proper place where a line graph gives every point equal spacing. IE a line plot with 5 days one after the other may look right in both scatter an line. However if one of those days were to skip due to a weekend or a holiday, the line plot would still look the same where a scatter would leave a bigger gap where the weekend or holiday was jumped over. – Forward Ed Apr 29 '19 at 3:23
  • 1
    It is also possible to tell excel what to do when it hit a gap in the data series. Its one of the options when setting up the data. – Forward Ed Apr 29 '19 at 3:23
  • Right click the chart > Select Data > click Empty and Hidden Cells > for blank cells, draw line connecting points. – Jon Peltier Jun 10 '19 at 2:10

In an Excel line chart with dates on the X axis you can format the X axis to plot the data as a text axis or a date axis. If you select date axis, the X axis will have all the dates.

Chart plotted with text axis, data points are spaced evenly

enter image description here

The same chart plotted with a date axis, all dates are on the X axis:

enter image description here

If you don't have data for all dates, click Select Data on the Chart Design ribbon, then click the button for Hidden and Empty Cells and select "Connect Data points with Line".

Chart with missing data points:

enter image description here

Chart plotted connecting empty data cells with line:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.