Conditional formatting for chart axes

Excel 2016 2013 2010 2007 2003
One of the useful tricks to attain the attention of the audience and improve the effect of presenting your data is to use axis label formatting customized for the specific value ranges:
Three different formats Excel 365   Condition format Excel 365  

The conditional formatting includes:

See more about custom formatting.

There are some differences in customizing axes in Excel:

  • You can apply conditional formatting:
    • For the vertical (Value) axis labels in scatter, line, column, and area charts,
    • For the horizontal (Category) axis labels in a scatter and bar chart.
  • You can't apply conditional formatting - Excel ignores the custom conditional formatting for such axes:
    • For the horizontal (Category) axis labels in line, column, and area charts,
    • For the vertical (Value) axis labels in a bar chart.

So, the standard conditional formatting can be applied for both axes just for scatter plots. For other charts, see below the workaround for applying conditional formatting for axis labels.

Apply standard conditional formatting for axes

To change the format of the label on the Excel for Microsoft 365 chart axis (horizontal or vertical, depending on the chart type), do the following:

   1.   Right-click on the axis and choose Format Axis... in the popup menu:

Format Axes in popup Excel 365

   2.   On the Format Axis task pane, in the Number group, select Custom category and then change the field Format Code and click the Add button:

Format Axis in Excel 365
  • If you need a unique representation for positive, negative, and zero values, just write multiple formats, separating each with a semicolon (or a comma, see how to change the semicolon to a comma or vice versa):

    {positive value}; {negative value}; {zero}; {text value}

    • If you write two formats, the first applies to positive and zero values, and the second applies to negative values.
    • If you write three formats, the first applies to positive values, the second to negative values, and the third to zero.
    • If you write four formats, they apply to positive, negative, zero, and text values, respectively.

    [Blue]#,###;[Red]#,###;[Green]~0;

  • If you want to create a custom condition based on values below or equal to some point (for example, -60) and greater than it, you could type the following condition codes:

    [Cyan][<=-60];[Magenta][>-60]

Conditional formatting for vertical axis in Excel 365

See also How to hide points on the chart axis.


Workaround for conditional formatting

Conditional formatting for both axes in Excel 365

To apply formatting for the vertical (Value) axis for the bar chart or horizontal (Category) axis for other types of charts, where Excel ignore the standard conditional formatting, do the following:

   1.   Create new data for the axis labels

The main purpose of the new data series is to substitute the axis labels with custom values with different formatting. So, the new data series values should be displayed instead of the axis labels.

For example, to highlight the horizontal (Category) axis labels for all negative values, add two data series - for positive and for negative values:

New data for conditional formatting in Excel 365

   2.   Add a new data series to the chart

   2.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.2.   In the Select Data Source dialog box:

  • Under Legend Entries (Series), click the Add button:
    Select Data Source in Excel 365
  • In the Edit Series dialog box, in the Series values field, type the constant values equal to the minimal visible value in the axis as many times as labels you need to add:

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

    Edit Series dialog box in Excel 365

   2.3.   Repeat previous steps to add the second additional data series for negative values.

After closing the Select Data Source dialog box, Excel rebuilds the chart:

Excel with additional data series in Excel 365

   3.   Hide the horizontal axis labels

   3.1.   Right-click on the horizontal axis and choose Format Axis... in the popup menu:

Format Axis popup in Excel 365

   3.2.   On the Format Axis pane:

If necessary, return the correct labels:

  • On the Axis Options tab, in the Axis Options group, in the Bounds section, type the correct Minimum and Maximum values.

Hide the axis labels:

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

   4.   Add the new data series labels

   4.1.   Select the data series (see more about how to select chart elements), then do one of the following:

  • Click on the Chart Elements button, select the Data Labels list:
    • For the horizontal axis, choose Below (or Outside End),
    • For the vertical axis, choose Left:
    Add labels to the 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 the place for the labels. For example, Below:

    Add labels below in Excel 365

   4.2.   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

   4.3.   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.

   5.   Repeat steps

Repeat step 4 for the second data series - add the data labels for the negative values and format them

   6.   Customize the data labels as you prefer

   7.   Hide the unneeded data series

   7.1.   Right-click on the data series you want to hide and choose Format Data Series... in the popup menu:

Format Data Series with labels in popup menu Excel 365

   7.2.   On the Format Data Series pane, in the Fill & Line group:

  • In the Fill section, select No fill,
  • In the Border section, select No line:
Empty Data Series in Excel 365

Apply another formatting as desired.

See also this tip in French: Mise en forme conditionnelle des axes du graphique.

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.