2

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?

Improve this question

1 Answer 1

Reset to default
1

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:

enter image description here

Improve this answer
2
  • I don't have those options in 2010 - the Format Axis box looks like this: dummies.com/wp-content/uploads/218259.image0.jpg
    – Wilskt
    Commented Jan 19, 2018 at 14:57
  • @Wilskt I'm afraid I don't remember the options in Excel 2010. Do the "Display units" or "Major tick mark type" comboboxes allow you to select months?
    – Blackwood
    Commented Jan 19, 2018 at 15:14

You must log in to answer this question.

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