14

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.

8

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.

| improve this answer | |
  • 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 Jun 17 '14 at 19:30
  • 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 Mar 9 '19 at 16:09
13

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.

| improve this answer | |
  • 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 Jun 17 '14 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 Jun 17 '14 at 19:33
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.

| improve this answer | |
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.

| improve this answer | |

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.