How to fill in missing dates in chart

Excel has a built-in chart option to deal with this issue.

  1. Select your horizontal axis.
  2. Right-click and select Format
  3. In Axis Options, look for Axis Type. Select the Date Axis radio button and Excel will automatically add the missing dates, while only plotting your data.

Here's what it looks like:

Missing Dates

This should work with either Line or Column charts.


I'm a big fan of vlookups. Assuming your data is in columns A and B, make a range of dates down column D using the autofill (type 11/1/14 in D1, then drag the bottom-right corner down). Then in E1 use:

=IFERROR(VLOOKUP(D1,A:B,2,FALSE),0)

Which should search in column A for every date and put the number in column B associated with it, or 0 if nothing is found (the IFERROR wrap is needed for that, otherwise it shows N/A). Then autofill column E and you should be able to make a chart on columns D and E. Takes up some space but it's easy and it works. You can hide it on another sheet if it looks too messy.