Date formatted charts in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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%20Date%20Chart%20data - Date formatted charts in Excel

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

Regular%20Date%20Chart%20choice - Date formatted charts in Excel

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

Regular%20Date%20Chart%20result - Date formatted charts in Excel

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.

Irregular%20Date%20Chart%20data - Date formatted charts in Excel

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%20Date%20Chart%20result - Date formatted charts in Excel

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’

Irregular%20Date%20Chart%20 %20Format%20Axis - Date formatted charts in Excel

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.

subs profile e1563205311409 - Date formatted charts in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address