When you have data that spans a long period of time that you want to plot in a chart, the dates in the horizontal axis in a line or scatter chart can get very cluttered. With this clever workaround that Jim Fitch shared with me we can trick Excel into showing data correctly spaced over time and only label specific Excel Chart axis dates.
The scatter chart below illustrates the before:
Compared to after when we only label specific dates in the axis:
Watch the Video
Enter your email address below to download the sample workbook.
Steps to Label Specific Excel Chart Axis Dates
The trick here is to use labels for the horizontal date axis. We want these labels to sit below the zero position in the chart and we do this by adding a series to the chart with a value of zero for each date, as you can see below:
Note: if your chart has negative values then set the ‘Date Label Position’ to a value lower than the minimum negative value so that the labels sit below the line in the chart.
Step 1 - Insert a regular line or scatter chart. I’m going to insert a scatter chart so I can show you another trick most people don’t know*.
Step 2 - Hide the line for the ‘Date Label Position’ series:
Step 3 – Set the desired minimum and maximum dates (Scatter Charts Only)
Tip*: the dates are shown in their date serial number format, but you can simply enter the new date in your regional date format (d/m/y or m/d/y) and Excel will convert it to the date serial number for you after you press enter:
Step 4 – Hide the horizontal axis labels:
Step 5 – Add Data Labels to the 'Date Label Position' Series.
Use the drop-down list on the Chart Format tab to select the ‘date label position’ series as it’s no longer visible in the chart. While you’re adding elements (4 in image below) also remove the gridlines and legend if required (6 & 7 in image below):
Step 6 – Change labels to dates
Select the zero labels > Data Labels > More Options:
Check the box ‘Value from cells’ (available in Excel 2013 onward), then select the range of dates:
Set the label position to ‘Below’.
Step 7 – Rotate the Text
Step 8 – Resize Plot Area to allow room for date labels.
Step 9 – Chart Title
Give the chart a title and resize as required:
A big thanks to Jim Fitch for sharing this clever idea.