Customize the vertical axis labels

Excel 365
Excel provides very few options for formatting vertical axis labels. All axis labels are a single object - you can change the font size and color or apply effects to all labels at the same time. Even the gradient fill with the color of the axis labels works for us as for the whole item and not for individual objects.

In addition to changing the font size and reorganizing the axis labels, it is often necessary to hide or highlight some of the labels with color or larger font size. For example:

  • In the chart with Critical zones, the vertical axis should display only some labels:
    The Competition Chart in Excel 365

    See more about creating a chart with critical zones.

  • In simple funny charts such as a tally chart, the vertical axis should display different colors for different bars:
    Axis labels in bar chart Excel 365

    See more about creating a tally chart in Excel.

Note: See also how to conditionally highlight axis 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:
    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:

  • In the Series values or Series X values box (for the scatter chart), type the constant values equal to the minimal value on your chart as many times as many labels you want to see on the chart.

    For example:

    • For the tally chart, 5 points:

      = {0,0,0,0,0}:

      Edit Series dialog box in Excel 365
    • For the Critical zones chart, the first date on the x-axis 6 times for the Labels data series and 3 times for the Labels2 data series::
      Edit Series dialog box 2 in Excel 365

      Note: You can create one additional data series even for the Critical zones chart, but it is a little more complex, but you will need to customize each new data series label instead of customizing all data series labels.

  • For the scatter chart, in the Series Y values field, type the values from the y-axis you want to see.

    For example:

    • In the charts with one additional data series, repeat the same values for the y-axis.
    • For the Critical zones chart (two additional data series), the first 6 and the last 3 values of the additional data for the Labels and Labels2 data series, respectively.

Note: For scattered bar charts and 100% scattered bar 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

After closing the dialog box, Excel rebuilds the chart - you should see the added data series at the axis.

For example:

  • The tally chart, the first data series:
    Tally chart draft in Excel 365
  • The Critical zones chart with two additional data series:
    Critical zones chart draft in Excel 365

Note: See how to select the invisible chart elements for more details. If you don't see a new data series in the axis even after selecting it, check the data in the Edit Series dialog box (step 2.2.).

Hide the axis labels

   3.   Right-click on the vertical axis and choose Format Axis... in the popup menu (or double-click the vertical 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, 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 Left:
    Add labels left 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 Left:

    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 Data Label Range dialog box:
      Select Data Label Range in popup menu Excel 365
    • Unselect all other checkboxes.
  • Optionally, under Label Position, select the Left option:
    Format Data Labels left 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 Marimekko or Mekko chart.

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.