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.
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?:
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.