You’ve built your chart, it all makes sense and suddenly you look at your horizontal axis, and the date axis is wrong. This happens a lot. You expect to see the dates you have in the data but different dates are showing, normally the beginning of month date instead of the end of month date.
As shown below, the data clearly shows month end dates (31 Jan 2016) but the chart is showing the beginning of the month (1st Jan 2016) on the horizontal axis of the chart. This may seem like an error but it is actually a feature. The key is to understand that if Excel sees a valid date (e.g. the 31 Jan 2016) it will open up new options in some of its tools and charts are one of them.
If you right click on the horizontal axis and choose to Format Axis, you will see that under Axis Type it has 3 options being Automatic, text or date. As we have entered valid dates in the data the Automatic chooses dates and therefore you get the option in the second box. If Excel sees valid dates it will allow you to control the scale into days, months or years.
So if, instead of having the Base unit as Months, I change it to Days the Chart will adjust the axis to show the data points on a chart that has a daily scale. So below you can see the gaps in days between the points.
If I choose years it collapses the time period into years.
How to force Excel to use your typed in dates in a chart
Although this feature is useful, sometimes you just want Excel to show the dates you typed.
In order to do this you just need to force the horizontal axis to treat the values as text by
- right clicking on the horizontal axis,
- choose Format Axis
- Change Axis Type to be Text
Note that you immediately lose the scaling options and the date scale puts in exactly what is in the data, onto the horizontal axis.