17

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.

user176692
  • 273

4 Answers4

14

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.

dav
  • 10,618
10

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.

baochan
  • 1,312
1

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.

Jason C
  • 11,385
0

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.

Gabe
  • 1