Date formatted charts in Excel

The secret to a timeline chart in Excel is the data.

Emily writes asking “How can I make an Excel chart with dates on the X axis with a proper time scale – even if the dates aren’t regular? “

We could not understand Emily’s question until we asked for more details. Making a date based chart is quite easy in Excel. It turns out Emily had tried making a date based chart years ago and given up. It’s one of those things that Office used to promise but was difficult. Now it’s quite easy.

Let’s start with a regular chart with nice evenly spaced dates. In this case, monthly.

Regular Date Chart data image from Date formatted charts in Excel at

Select both columns of data then Insert | Chart | Line or whatever chart you think appropriate.

Regular date Chart choice image from Date formatted charts in Excel at

Excel 2013 makes chart selection a lot easier with a proper gallery and live preview.

Regular Date Chart result image from Date formatted charts in Excel at

It’s quite a basic look but the important elements are all there. You can tinker from there to make the chart look better.

Irregular Dates

If only all dates were evenly spaced. Often they are not. Either data is missing or, as in this example, the events aren’t regularly spaced.

Windows 10 from people 'in the know'

A detailed and independent look at Windows 10, especially written for the many people who use Microsoft Office.

Fully up-to-date with coverage of the Anniversary 2016 major update of Windows 10.

This 670 page book shows you important features and details for all serious Windows 10 users.

Irregular date Chart data image from Date formatted charts in Excel at

We added the first shuttle mission (Launch date and flight duration) so the gap in the timeline is realllllly obvious .

Selecting the B and C columns to make a chart gives you this:

Irregular Date chart - result image from Date formatted charts in Excel at

Notice that Excel has arranged the horizontal axis into a timeline automatically. There’s a long gap between the last two data points (due to the gap between Apollo and Shuttle programs).

To adjust the timeline, right-click the axis and choose ‘Format Axis’

Chart format axis image from Date formatted charts in Excel at

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 along the axis.

Don’t be afraid to tinker with the chart settings and formatting because Undo is your friend and constant companion.

Check the data

The trick isn’t in the chart, it’s in the data. We nerds call it normalizing data, normal humans call it making the dates all the same type and look.

Before making a date based chart, look carefully at the date data. Is it all in Excel date format or as text? Make sure all the dates are in the format Excel recognizes as dates – not text.

That was Emily’s main problem – some of the ‘date’ cells were actually text so Excel didn’t know how to format the axis.  Once that was fixed, the chart appeared without fuss.

It makes things a lot easier if the source dates are in the format you need for the chart. For example, the original Apollo data had landing date and time down to the second. To make the chart easier to make and manipulate, we reformatted the column into date only.