Customize the horizontal axis labels

• To see the timeline data in the XY scatter plot instead of numeric labels:
  
• To apply formatting for the horizontal (Category) axis for the column, line, or other chart types (like for the vertical (Value) axis for the bar charts), where Excel ignores the conditional formatting.

  Note: Conditional formatting only works for numeric values on the axis - see how to conditionally highlight axis labels.

• To apply the custom formatting for the axis labels:
  

  See more about how to highlight only some data labels.

Add a new data series to the chart

The main purpose of the new data series is to substitute the axis labels - the new data series labels will be displayed instead of the axis labels.

To add one or multiple data series to the existing chart, follow the next steps:

   1.   Do one of the following:

• On the Chart Design tab, in the Data group, choose Select Data:
  
• Right-click on the chart area and choose Select Data... in the popup menu:
  

   2.   In the Select Data Source dialog box:

   2.1.   Under Legend Entries (Series), click the Add button:

   2.2.   In the Edit Series dialog box:

• For the scatter chart, in the Series X values field, type the same data as for the other data series to see the same values on the horizontal axis.
• In the Series values or Series Y values box, type the constant values equal to the minimal visible value on your chart as many times as many labels you want to see on the chart.

  For example:

  • For the scatter chart, 12 points:

    = {80,80,80,80,80,80,80,80,80,80,80,80}:

    
  • For the column chart, 4 points:

    = {0,0,0,0}:

    

Note: For scattered column charts and 100% scattered column charts, ensure the added data series is the first in the order of the data series. To reorganize data series, in the Select Data Source dialog box, use the Move Up and Move Down buttons:

   2.3.   Click the OK button twice to close dialog boxes.

Excel rebuilds the chart - you can see the added data series at the axis for some chart types:

Note: See how to select the invisible chart elements for more details.

Hide the axis labels

   3.   Right-click on the horizontal axis and choose Format Axis... in the popup menu (or double-click the horizontal axis):

On the Format Axis pane, do one of the following:

• On the Text Options tab, in the Text Fill & Outline group:
  • In the Text Fill section, select No fill,
  • In the Text Outline section, select No line:
  
• On the Axis Options tab, in the Axis Options group, in the Labels section, from the Label Position list, select None:
  

  Note: The Labels section looks different for the stacked charts:

  

Add the new data series labels

   4.   Select the new data series (even if it is invisible, see how to select such elements on the chart), then do one of the following:

• Right-click on the selection, then choose Add Data Labels -> Add Data Labels in the popup:
  
• Click on the Chart Elements button, select the Data Labels list, then choose Below (or Beside End for the column charts):
  
• On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element button:
  

  From the Add Chart Elements list, choose Data Labels, then select Below (or Beside End for the column charts):

  

   5.   Right-click on the added data series labels and choose Format Data Labels... in the popup menu:

On the Format Data Labels pane, on the Label Options tab, in the Label Options section:

• Under Label Contains:
  • Select the Value From Cells checkbox, then choose data labels in the Data Label Range dialog box:
    
  • Unselect all other checkboxes.
• Optionally, under Label Position, select the Below (Beside End) option:
  

   6.   Make formatting you want for the new data series labels instead of axis labels.

Hide the labels data series

To make the additional data series invisible (if needed), on the Format Data Series pane, on the Series Options tab, in the Fill & Line group:

• For the scatter chart:
  • In the Marker Options section, select None:
    
  • In the Border section, select No line if necessary.
• For other charts:
  • In the Fill section, select No fill,
  • In the Border section, select No line:

See a more complex solution where the positions of the new labels on the axis are calculated in the Mosaic plot.