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. If plot this stock price data as a line chart, the horizontal axis is automatically set up as a category axis with a type of “date”. You can see category in the name, and if I open the format task pane to axis options, you can see Excel is using the Automatic setting, which, since we have valid dates, means we have options for minimum and maximum dates, as well as date intervals. Notice since we have a large date range, the interval is set to 1 year, and all dates are January 1. This is a little strange and maybe even confusing. It’ll be easier to see what’s happening if I add data labels and drop lines to the chart. Now it’s clear that the stock price data is plotted across the date range, and the data points don’t necessarily line up with the dates in the axis. In many cases, this is just fine. The axis shows an accurate distribution of the data. However, there may be times where you want a simple, even distribution. In that case, you can switch the type to text. Now the data points are evenly spaced, and line up with the dates shown on the horizontal axis. Notice that even though the axis type is now text, Excel still understands the dates. For example, I can apply a different date format, and the chart immediately updates. Let’s look at one more example. This is monthly stock price data over a period of more than 15 years, from July 2001 through May 2017, in more than 100 rows. If I plot the data as a line chart, Excel correctly interprets the dates and builds an automatic horizontal axis to fit the date range, with the unusual setting of 8 months for units. To show only years, and make sure the line is displayed correctly across the range, I can set units to 1 year. Then enter January 1, 2001 as the minimum and Jan 1 2018 as the maximum. I can then apply a custom date format to show only the year in the horizontal axis.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
