MS Excel Scatterplot converts Months to Numbers

Question

Software: MS Excel 2016

Link to files: GitHub Repository

---

Sheet2 of month.xlsx has table

When I select it and Insert Scatterplot, Chart1 of month.xlsx is the result. No matter what I try, I cannot get the X-Axis to display in Months (right now it displays Month's number).

Help!

Answer 1

You can not make scatterplots using a category. Scatterplots assume both axes are numerical to enable you to have fractional values on x and y axis. You will have to change month onto numbers such that they scale correctly. I'd suggest the day of the year. February 5 would be day 36, for example. Or use the month number but beware of the differences in the number of days in a month.

Answer 2

The problem is most likely the way you have your months stored. By the looks of things I would hazard to guess they are actually text and not numbers formatted to display the name of the month in full. In order to get the months to display in your graph, you will either need to enter the months as a serial date which Excel can understand as a number, or create a dummy column to hold this same information which you can latter hide.

once you have your months in a serial date that excel can handle, you can then select the X-axis and set the format style. Select custom as the category, and then use mmm or mmmm as you custom format to display just the name of the month. Alternatively you could show the whole date using date formats.

Two ways to enter a date serial are to simply enter the numeric date format that matches your regional settings or use the following formula:

=DATE(year,month,day)

Where year, month and day are all integer, or formulas that evaluate to acceptable integers