Search code examples
excelscatter-plotexcel-2016

MS Excel Scatterplot converts Months to Numbers

Software: MS Excel 2016

Link to files: GitHub Repository

Sheet2 of month.xlsx has table

enter image description here

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!

enter image description here

Solution

  • enter image description here

    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