Customize the horizontal axis labels

Excel 365
Excel provides very few options for formatting horizontal axis labels. You cannot format different axis labels with different colors or font sizes. Conditional formatting also only works in some cases. You need to implement some tricks to format the horizontal axis, for example:

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:
    Select Data in Excel 365
  • Right-click on the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

   2.   In the Select Data Source dialog box:

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

Select Data Source dialog box in Excel 365

   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}:

      Edit Series dialog box in Excel 365
    • For the column chart, 4 points:

      = {0,0,0,0}:

      Edit Series dialog box 2 in Excel 365

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:

Move buttons in Select Data Source Excel 365

   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:

New data series in Excel 365

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):

Format Axis in popup menu Excel 365

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:
    Hide Axis text in Excel 365
  • On the Axis Options tab, in the Axis Options group, in the Labels section, from the Label Position list, select None:
    Hide Axis labels in Excel 365

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

    Hide Axis labels 2 in Excel 365

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:
    Add Data Label in Excel 365
  • Click on the Chart Elements button, select the Data Labels list, then choose Below (or Beside End for the column charts):
    Add labels below in Excel 365
  • On the Chart Design tab, in the Chart Layouts group, click the Add Chart Element button:
    Add Chart Element button in Excel 365

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

    Add data labels below in Excel 365

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

Format Data Labels in popup menu Excel 365

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 Select Data Label Range dialog box:
      Select Data Label Range in popup menu Excel 365
    • Unselect all other checkboxes.
  • Optionally, under Label Position, select the Below (Beside End) option:
    Format Data Labels below in Excel 365

   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:
      No Markers in Format Data Series Excel 365
    • 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:
Empty Data Series in Excel 365

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

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more