I have a table of data, with one row for each day of the year (1/1/2018 through to 31/12/2018). I want to show this on a line chart with the x axis labelled with the short text format of the dates, so Jan through Dec. I don't mind if the text is lined up with the 1st of each month or with the middle of each month.
I cannot find settings to allow me to do this. The closest I can get is to display Jan underneath the intercept with the y axis, and then the months getting progressively out of line as they go on, because the major unit of the x axis can only be set to a fixed number, 31. Any higher and the misalignment gets worse, any lower and Jan appears on the axis twice because it has 31 days. Ideally the text on the x axis should take into account that each month has a different number of days.
Is this possible? Do I need to use some kind of grouping or pivot table chart?
1 Answer
You can format the date axis to show just the months.
- Right click on the data axis and choose "Format Axis" from the context menu.
- In the Units section of the Axis Options, change the "Major" interval to 1 and the pick "Months" (instead of "Days") from the combobox.
- In the Number section of the Axis Options, pick "Custom" from the Category combobox and change the "Format Code" to mmm.
The Format Axis pane should look like the following picture:
