5

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.

11 Answers 11

4

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!

Blake

1
  • To build on this, if you have cells that are empty with no dates yet, but will be populated in the future and you don't want to have to change the cell range for the graph every time you add a new row of data, you can just put =NA() in the cells that are not yet populated with dates, which by default will exclude those values from being graphed at all and still allow the other dates to appear correctly on the axis. Otherwise, blank date cells will fail in exactly the same way @Blake described for bad date cells and prevent the entire axis from even appearing as dates. Aug 19, 2023 at 17:05
2

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

01.08.2015 00:30:00

Won't work, but

01.08.2015 00:30:00

01.08.2015 00:34:00

Should work.

1
  • Similar to what you say, I found that the same wacky chart behavior (= x-axis dates 1900 etc.) resulted when my date field was (on purpose) a formula which returned an empty string (on purpose), which I intended to be blanks in the chart but which ended up confusing it and making it go all 1900s on me. In this case, I can just delete the formulas from those otherwise-empty cells and remove the confusion for the chart (yay), but I'm not sure what you'd do in a case where formula deletion isn't an option.
    – mlibby
    Dec 13, 2018 at 12:45
1

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.

2
  • 1
    No, a line chart treats dates as dates. However, if the dates are not correctly specified as the X values for a series, Excel will use the counting numbers as X values. Jan 12, 2017 at 2:47
  • @JonPeltier, darn, if you ain't right. Looks like I'll need to update this answer.
    – fixer1234
    Jan 12, 2017 at 4:00
1

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.
0

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."

4
  • One additional thought on why the axis appears short by several months. Independent of the data range, the chart axis has its own parameters. Look at the properties for the axis and check what it's using for a maximum value.
    – fixer1234
    Jan 5, 2017 at 21:03
  • 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.
    – jalea148
    Jan 5, 2017 at 22:18
  • The vertical axes format windows show options for min/max and other parameters. The horizontal axis format window does not.
    – jalea148
    Jan 5, 2017 at 23:00
  • 1
    What kind of axis is specified for the horizontal axis? Your choices are Auto, Text, and Date. Select Date and see if it helps. If not, at least some data in the X range is not interpreted as dates by Excel. Jan 12, 2017 at 2:48
0

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.

1
  • I think this is the opposite of the asker's question. He wants the axis to display dates, not text. As long as the axis is based on a row or column that has exclusively valid dates, Excel will provide a date axis. Otherwise, if a single cell is not a valid date, it will convert it in the display and space the points all equally instead of based on the date separations (e.g., if the first 3 dates in the date row have values of Jan 1, Jan 15, and Feb 22, the first two points should be closer together than the second and third) Aug 19, 2023 at 17:14
0

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).

0

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.

0

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.

0

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

-1

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

1
  • Please provide more information on how to find this option. When you say "updated version of spreadsheet", do you mean a more recent version of Excel?
    – Worthwelle
    Dec 6, 2019 at 15:47

You must log in to answer this question.

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