When the X-axis is crowded with labels one way to solve the problem is to split the labels for each data point into two parts and use two rows of labels on the X-axis. It’s really easy to do, even if you’ve already created the chart.
For a new chart on which you want to use two rows of labels enter the two rows of X-axis labels as shown in the figure below. In this example, the cell containing month contains the actual date and the cell below uses the YEAR() function to produce the year from the date in the cell above.
When you select the chart data, select all of it, in this case from C18:J20. Then, create the chart using Insert, Chart or by pressing F11. (In the figure I’ve already deleted the legend and horizontal grid lines as you should normally do for a chart with a single line.)
I’ll bet you probably have a few charts you’ve already created that would look better with a second row of labels, but you sure don’t want to go through the pain of building them from scratch. Well it’s pretty easy to add the second row of labels to an existing chart.
Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.
To begin with, insert a blank row between the current X-axis labels and the data. In this case that is row 19. Now, fill that row with the labels you want on the new X-axis label row. When you do this you won’t see any change in the chart.
To get the second row of X-axis labels included in the chart, you now need to modify the chart’s data formula. You can do that with the Select Data Source dialog box. To display that dialog box, select the chart then on the Design tab under Chart Tools, in the Data group, click the Select Data tool, shown with a red box in the figure. The Select Data Source dialog box displays as shown. Notice that the second row of labels you’ve inserted isn’t part of the data selected for the original chart.
The Select Data Source dialog box is a very handy tool for modifying your charts. It has a lot of tricky uses. You can edit the cells referenced by the chart for its legend labels, data sources, and horizontal axis labels.
Now that you have the Select Data Source dialog box open, click the Edit button on the right side to edit the Horizontal (Category) X-axis labels. The Axis Labels dialog opens where you can now enter the range of both X-axis labels. Here is where you will show Excel where the second row of X-axis labels are. In the Axis Label Range box drag or enter the cell reference containing both rows for the X-axis labels.
Click Ok to close the Axis Labels dialog box and click Ok to close the Select Data Source dialog box. You’ll find your second row of labels are added to the chart.
BTW: If you want the chart labels so the year is on the second row, then just switch the rows you enter them in the worksheet.