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.
Table of contents
YouTube Date Axis in Excel Chart is wrong
Date Axis Bounds Excel
Date Axis in Excel Chart is wrong- the issue
Under ‘Axis Type’ you can force Excel to consider the data text or date but if Excel has guessed wrong then there’s usually a problem with the source data. The top axis options can be adjusted – for example you could change the scale to start 1 Jan 1969 and end 31 Dec 1981 or change the unit settings so there’s not so many labels. In the 'Format Axis' dialog, select 'Number'. This will enable you to select the appropriate display format, which is likely set to 'General' if you're seeing raw integer values vice 'dates'. How Excel plots dates on a chart axis. In this video, we'll look at an example of how Excel plots dates on a horizontal axis. When you create a chart using valid dates on a horizontal axis, Excel automatically sets the axis type to date. For example, this stock price data is spaced out over a period of more than 10 years, in random intervals.
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.
Excel automatically displays every other date on the horizontal axis, with major units set at 2 (auto). Over-riding it to 1 didn't make any difference. Expanding the width of the chart so that the number of data points per centimeter became just over 2, displayed all date labels. To change the axis type to Date axis, execute the following steps. Right click the horizontal axis, and then click Format Axis. The Format Axis pane appears. Click Date axis. Result: Conclusion: the trendline predicts a population of approximately 250 bears in 2024.
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.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Axis Type | Axis Titles | Axis Scale
Most chart types have two axes: a horizontal axis (or x-axis) and a vertical axis (or y-axis). This example teaches you how to change the axis type, add axis titles and how to change the scale of the vertical axis.
To create a column chart, execute the following steps.
1. Select the range A1:B7.
2. On the Insert tab, in the Charts group, click the Column symbol.
3. Click Clustered Column.
Result:
Axis Type
Excel also shows the dates between 8/24/2018 and 9/1/2018. To remove these dates, change the axis type from Date axis to Text axis.
1. Right click the horizontal axis, and then click Format Axis.
The Format Axis pane appears.
2. Click Text axis.
Result:
Axis Titles
Date Axis Excel Not Working
To add a vertical axis title, execute the following steps.
1. Select the chart.
2. Click the + button on the right side of the chart, click the arrow next to Axis Titles and then click the check box next to Primary Vertical.
3. Enter a vertical axis title. For example, Visitors.
Result:
Axis Scale
By default, Excel automatically determines the values on the vertical axis. To change these values, execute the following steps.
How To Plot Excel Chart With Date Axis Excel
1. Right click the vertical axis, and then click Format Axis.
The Format Axis pane appears.
2. Fix the maximum bound to 10000.
3. Fix the major unit to 2000.
Result: