I'm looking for a simple way to chart a list of values each correlating to a date. For example:

Date      Value
11/7/13   2
11/8/13   2
11/11/13  3
11/14/13  2

Etc. The problem is, the chart does not show a proper trend since many dates have no occurrences or value for that date. So, in the chart, things look fine, but are distorted since there are no dates with a value of 0. How can I fill in this sequence of dates with 0 values for the missing dates?

I am looking for the simplest possible approach. If I wanted to bypass Excel, I could just adjust the input data with Perl or something.

4 Answers 4


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:


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.

  • This is a simple expression, so may have to stick with this. Sounds quick and portable, and seems to yield the correct chart. Will spend a little more time with it, but this might fit the bill.
    – user176692
    Commented Jun 17, 2014 at 19:30
  • 1
    The "2" in the formula refers to the "second column in the A:B range", which is column B. That took me a minute or two to figure out.
    – Freewalker
    Commented Mar 9, 2019 at 16:09

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.

  • 1
    Thanks, I'm fine with Excel expressions, though would prefer something like this. Can't figure out how to show 11/9/13, for example, as 0 also though.
    – user176692
    Commented Jun 17, 2014 at 19:02
  • If you want a gap or zero, you'll need to have an X Axis value for the date, but no value or zero for the Value. As your data is presented, you can either get a chart like I included above, or a chart that only has the values you have listed (11/7, 8, 11, 14). If you include a date, but no value, you can customize in the Select Data dialog box for how your series will be displayed.
    – dav
    Commented Jun 17, 2014 at 19:33

You could just create an XY scatter plot with dates as X and values as Y. The spacing will be correct and the dates will appear in the X axis labels. Sort the values by date and use the connected line scatter plot type.


If you right click on the chart and select "select data" a dialog box pops up. In the bottom left portion of this dialog box is a button for hidden and empty cells. It is here you can select zeros for missing information.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .