Excel graph not showing all dates

My Excel 2010 line chart has a secondary axis. Its horizontal date axis is incorrect. Dates should range 1/1/2013 to 12/31/2013: All date data is explicit [ex 3/8/2013]. Dates

Excel graph not showing all dates

My Excel 2010 line chart has a secondary axis. Its horizontal date axis is incorrect.  Dates should range 1/1/2013 to 12/31/2013:

Date should range 1/1/2013 to 12/31/2013

All date data is explicit [ex 3/8/2013]. Dates are M-F except for holidays. The last 2 dates are 12/30/2013 and 12/31/2013. There are 252 dates with the start of the range correctly listed in the horizontal edit window.

I had the same problem. I tried reducing how much data was included in my set and found that there was one specific entry that was causing the plot to revert to {1,2,3...} rather than the using dates entered. My date axis would behave completely fine until I included that one entry. There was nothing wrong with the formatting and for a while it was getting very frustrating.

It turns out I accidentally keyed in a date that does not exist. I had entered 11/31/2019, but the month of November only has 30 days. Instead of throwing an error when I originally input the erroneous date, Excel just stopped treating the whole set as dates when I included it in my data set.

I hope this helps!


Revised; original answer wasn't accurate.

Kevin's answer almost nailed it.  Either a line chart or XY chart should work, but there appears to be a problem with the way the data range is specified for the chart.  The question and comments don't describe how the range is specified, so just verify that the series data range reflects the dates column of your data as the X values.

What is happening is that without the dates specified as the X values, it is using the sequence numbers of the values.  So your first date is input #1, the second is input #2, etc.  Excel stores dates as a day count, starting with January 1, 1900 being day 1.  When you format the axis with a date format, you get what Kevin described--sequence number 1, as a date, is 1/1/1900.  Your last input is entry number 252, which corresponds with 9/8/1900.

I have a possible solution for this issue. I was trying to plot dates from an Excel plugin versus values on the Excel Scatter chart and but the dates were shown 1, 2, 3, 4, 5 etc.

What I found out which cleared the issue was that this can happen if you try to plot some dates that contain the time, and some dates that don't.

For instance, plotting on axis:


01.08.2015  00:30:00

Won't work, but

01.08.2015  00:30:00

01.08.2015  00:34:00

Should work.

Short answers:

  1. Check chart type. Try specifying "Line" and not "X Y scatter".
  2. Just check you haven't got any impossible dates in your list.

Longer answers:

  1. I was having the same problem. My X axis was meant to show "real" dates, they were correctly specified in the sheet (or so I thought [see below]), but I was just seeing "default" dates from Jan-00 on the chart.

Couldn't work it out. Eventually went to chart type. Saw that the chart was set as an X Y scatter. Changed in to Line. All instantly sorted.

Of course, the chart might not display your data spaced how you want. As line charts and X Y charts are different: https://excel-example.com/charts/difference-line-chart-scatter-chart

Hope this might help someone.

  1. Check you don't have any impossible dates. I thought my dates were good. And 99% of them were. But, for some reason, my date list included 31/09/2012... September doesn't have 31 days. This seemed to be throwing it off as well. Once I resolved that. And then deleted and reinstated the horizontal axis, it started working again.

It's hard to troubleshoot this without seeing your data sheet or where the x-axis data came from, but if you entered the data for the date column like this:

1 2 3 4

Then Excel will default to interpreting those as days since the beginning of "the era."  You can cure this by entering the dates in full, like this:

1/1/2013 1/2/2013 1/3/2013 1/4/2013

Additionally, it appears that your graph is ending in August (or maybe early September).  If you have fewer than 365 observations -- because, for example, holidays and weekends are excluded -- then, again, inputting the actual dates will help.

If you already have input the dates, it might be that your graph isn't using those cells as the x-axis labels.  In that case, you can click on the graph, then "select data," then specify "horizontal (category) axis labels."

Make sure that the cells you are referencing (as axis labels) are "text" category and not "date" or another category. THEN, in the horizontal axes additional formatting dialogue, click "axes options", under "axis type" make sure "Text axis" is checked.  This should help.

One issue I had was I had to create new cells that were "text" from the get-go. If I changed the original cells from "date" to "text" (and even rewrote in the dates), the chart would not fix itself. BUT, once I created new "text" cells, filled them with the correct labels, and referenced to those new cells as the correct data, the axis labels changed and were correct.

Check that both series 1 and series 2 have horizontal (Category) Axis labels. if you've built your chart without an overall chart data range, the series have independent horizontal (category) axis labels, if one of them is blank, then the dates will be ignored and points will be treated as sequential from 01/01/1900.  To check, in the Select Data Source dialogue for your chart, click on series 1 in the legend Entries (Series) on the left then click the Edit button on the Horizontal (Category) Axis labels (on the right), repeat (and correct) for each Legend Entries (Series).

For me the answer was very simple. One of the cells in the range that contained the dates, contained some text instead of a valid date. It was the last cell. My date range was too long and it included the last cell which had text in it. I reduced the length of the range by one cell to exclude the cell containing the text and the problem disappeared. My series definition was: =SERIES(ActualData!$E$3,ActualData!$A$4:$A$20,ActualData!$E$4:$E$20,1)

So I edited it to read this, and it fixed the problem =SERIES(ActualData!$E$3,ActualData!$A$4:$A$19,ActualData!$E$4:$E$19,1)

NOTE that all of my date cells were formatted as Date (as they should be) not Text.

The Primary Axis Data set has to have the dates as the horizontal labels.  If you move the Data set with the dates as the horizontal labels to the secondary axis, what you describe happens.

Via Chart Design > Select Data Source

I removed all references to table headers (i.e., Series Name)

and then re-added them.  This fixed the issue.

Chart Option

Chart Option

Series Option

Series Option

Treat labels as text in horizontal axis chart style should be ticked. Its the option in updated version of spreadsheet

Video liên quan