How to make a scatter plot with date on the x axis and values on the y axis

Question

Likely a very dumb question, but I have a series of data that looks like this:

Date	Value
04/20/2015	15
03/20/2015	19
02/19/2015	24
01/21/2015	34
12/19/2014	38
11/18/2014	31
10/17/2014	38

But when I do Charts>Scatter etc. I get a ScatterPlot that has just numbers on the x axis, ie 1, 2, 3, 4, ... rather than the actual dates.

How does one force Excel to plot the y-axis as a function of date values, rather than substituting a 1, 2, 3, value for the dates on the x-axis?

Thank you!

Answer 1

If you want to use the dates as labels rather than as plotted data you don't want a Scatter Plot... Use a Marked Line instead.

Once the chart is created, right-click the X Axis labels, select Format Series, then choose the Text option in the Scale settings. This is the result:

Answer 2

Is this what you need?

Or

_________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone, and do not reflect upon my position as a Community Moderator.

Answer 3

Yes, the top of your two charts. What I am getting instead is a series of numbers from 1 to 80 on the x-axis, instead of 80 monthly dates. The column with the dates in it is formatted as type "Date".

Thank you very much for your willingness to answer and share your knowledge!

Answer 4

In sample one I put the Value after the Dates

In sample two I put the Dates after the Values

I tried using the button to reverse X and Y and and could not seem to get it to work so that is why I swapped position of.

I don't normal do charts that often and therefore don't know how to set the increments which evidently rounded to nearest increment of 5 instead of 1.

Some one Else That can do charts. (Several of the MVP's come to mind).

Let's hope they will chip in.

_________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone, and do not reflect upon my position as a Community Moderator.

Answer 5

Thanks much for taking the time to reply. I'm still puzzled why this isn't working, however. I formatted the first column as date, but here's the data input and the resulting graph:

It's likely something totally obvious that I am overlooking, but how do I get dates on the x-axis? Much appreciated.