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'. If I leave the dates as dates then Excel interprets this as data for one day out of seven, so I get thin columns with large gaps: I can resolve this by formatting the dates as text, which gives me the style I want: However, I want a date scale where only the first of each month is labelled, which I think requires a date formatted axis.
May 03, 2013 Convert the horizontal axis to a date-based axis. Excel thinks your chart is plotting daily dates from May 13, 1905, through July 2, 1905. Because no date format has been applied to the cells, they show up as the serial numbers 1955 through 2005. Excel displays the chart properly, even though the settings show that the base units are days. Steps to Label Specific Excel Chart Axis Dates. The trick here is to use labels for the horizontal date axis. We want these labels to sit below the zero position in the chart and we do this by adding a series to the chart with a value of zero for each date, as you can see below: Note: if your chart has negative values then set the ‘Date Label. The time-scale axis is a special type of category axis that is organized by date. The time-scale axis in the chart displays the dates in chronological order even if the dates are not in that order on the worksheet. The scaling of the time-scale axis is determined by an algorithm that Excel uses on the range of dates in the axis.
Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis. Use a scatter plot (XY chart) to show scientific XY data.
To create a line chart, execute the following steps.
1. Select the range A1:D7.
2. On the Insert tab, in the Charts group, click the Line symbol.
3. Click Line with Markers.
Result:
Note: only if you have numeric labels, empty cell A1 before you create the line chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically places these numbers on the horizontal (category) axis. After creating the chart, you can enter the text Year into cell A1 if you like.
Let's customize this line chart.
To change the data range included in the chart, execute the following steps.
4. Select the line chart.
5. On the Design tab, in the Data group, click Select Data.
6. Uncheck Dolphins and Whales and click OK.
Result:
To change the color of the line and the markers, execute the following steps.
7. Right click the line and click Format Data Series.
The Format Data Series pane appears.
8. Click the paint bucket icon and change the line color.
9. Click Marker and change the fill color and border color of the markers.
Result:
To add a trendline, execute the following steps.
10. Select the line chart.
11. Click the + button on the right side of the chart, click the arrow next to Trendline and then click More Options.
The Format Trendline pane appears.
12. Choose a Trend/Regression type. Click Linear.
13. Specify the number of periods to include in the forecast. Type 2 in the Forward box.
Result:
To change the axis type to Date axis, execute the following steps.
14. Right click the horizontal axis, and then click Format Axis.
The Format Axis pane appears.
15. Click Date axis.
Result:
Conclusion: the trendline predicts a population of approximately 250 bears in 2024.
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 in Excel Chart is wrong- the issue
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
Date X Axis Excel
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.