In this tutorial we're going to look at how we can skip dates in the Excel chart axis for those dates that have no data.
When you plot data in a chart that has a time axis Excel is clever enough to recognise you’re using dates and will automatically arrange the data in date order. The axis will also include all dates in the range, even if you don’t have data for those dates in your chart source.
This can be a nice time saver but sometimes you don’t want the missing dates included. For example, if you’re plotting stock prices then you don’t expect to have data for the weekends and as such you don’t want those ‘missing’ weekend dates in your chart cause it’ll look ugly:
Note: If you plot the data in a line chart, as you typically would for stock prices, then Excel will (by default) continue the line to fill in the missing data:
An aside: notice how the vertical axis on the column chart starts at zero but the line chart starts at 146? That’s a visualisation rule – column charts must always start at zero because we subconsciously compare the height of the columns and so starting at anything but zero can give a misleading impression, whereas the points in the line chart are compared to the axis scale. And anyway, if you were to show an equities trader a stock price column chart with an axis that started at zero they’re probably throw it at you. It’s just not how it’s done in their world. They like line charts (or box and whisker) with clear fluctuations because even a 0.01% change can mean millions of dollars to their bottom line.
That said, I don’t tend to plot stocks, commodities or foreign exchange rates so I prefer to always start my line chart vertical axis at zero to avoid being responsible for giving my boss a heart attack 😉 Those peaks and valleys can be scary!
I teach more visualisation tips and rules like this in my online Excel Dashboard course.
Enter your email address below to download the sample workbook.
Omit Missing Dates
If you want Excel to omit the weekend/missing dates from the axis you can change the axis to a ‘Text Axis’.
Right-click (Excel 2007) or double click (Excel 2010+) the axis to open the Format Axis dialog box > Axis Options > Text Axis:
Now your chart skips the missing dates (see below). I’ve also changed the axis layout so you don’t have to turn your head to read them, which is always a nice touch.
Note: When you set your Axis Type to Text you must ensure the dates in your source data are sorted as this is the order they will appear in the chart.
For completeness here's how the line chart looks when you skip dates. Better eh?:
Hi Mynda,
In Office 365, there doesn’t appear to be a Text Axis option to select. Has it been removed?
Thanks in advance.
Regards,
Daniel
Hi Daniel,
No, it hasn’t been removed. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
You deliver an outstanding instructional format with clarity and efficiency! I’m a huge fan!
Thanks so much, Mark!
Thank youuuuuuuuuu lfe saver!!
Glad we can help 🙂
What if I have a data range that goes from 1991 to 2020 and I when I plot it, the graph goes from 1900 to 2020 so that 2/3 of the graph is just empty space? There is no 1900 datapoint, so I have no idea why it starts there.
Hi Brendan,
You can change the axis minimum bound, which is the start date. Right-click the axis > format axis.
Mynda
Dear Sir,
i need to make bar chart in excel with dates and its default has to show in that excel for constructions industry planning
Hi Kumara,
If you’re stuck building the chart please post your question and sample Excel file on our forum.
Thanks,
Mynda
I have a quick question. What if you have so many dates it becomes illegible and you just want to show say every 5th date in the axis but maintain the data in the chart?
Hi Brett,
Yes, you can set the Date Axis major units to 5 days (Axis Options). The data will still be plotted, it’s just that the axis labels will only display every 5 days.
Mynda
Hey,
I am working with excel 2010. I am trying to visualise some data, taken from may until the end of november. However, october was not sampled at all, so I am interested in making a visual gap in the bar graphs x-axis, that covers the month of october.
How is this possible?
Best regards,
Cecilie
Hi Cecilie,
You need to enter proper dates for the x-axis and then in the Axis Options set the Axis type to ‘Date’. You can always format the dates to only show the month name with a custom number format of mmmm
I hope that helps but if you get stuck the best thing is to send a sample workbook via the help desk so we can show you an example.
Mynda
Thank you so much
This has driven me crazy for years!!!
Brilliant (and so simple – when you know how!!
xx
Thanks, Lynne. Glad you found it useful 🙂
Mynda
I like what you guys are doing.
It’s great:)
Cheers,
Hans
Thanks, Hans! Glad you like it.
Mynda
Hi Mynda
If you actually select a time series range (of dates that does NOT include weekends, say by using formulae to only return weekdays) when setting up the chart, will Excel still include them in the chart axis (presumably by filling in all dates between first and last?)?
It seems a little presumptuous / autocratic of MS to think that you just gotta have weekends in your time series even if they aren’t in the source data in the first place!
Cheers
Col
Hi Col,
Yes it will include all dates irrespective of whether they’re in your chart source or not. It will also arrange the data in date order, even if the source isn’t. In some ways this feature is helpful, but there are occasions where you don’t want to use Excel’s built in date intelligence.
Mynda