In this guide, we’re going to show you how to format axis labels individually in Excel.
As you have already known that there is no way to select an individual item in a chart axis. This is because Excel populates axis points on the fly based on data and chart size. You may have already seen disappearing items when you shrink the chart.
However, thanks to the custom number formatting feature, you are not out of options. You can alter how certain numbers are displayed or assign basic colors.
Custom Number Formatting
Excel allows you to write your own number format in two simple syntaxes:
- Positive; Negative; Zero; Text
- First Condition; Second Condition (if exists); Third Rule if previous conditions are not met.
Each section is optional and separated by a semicolon character (;). Here are a few examples:
If you are new to Excel's number formatting concept, you can check Number Formatting in Excel - All You Need to Know article.
How to add custom formatting to a chart's axis
- Double-click on the axis you want to format. Double-clicking opens the right panel where you can format your axis.
- Open the Axis Options section if it isn't active.
- You can find the number formatting selection under Number section.
- Select Custom item in the Category list.
- Type your code into the Format Code box and click Add button.
Examples of formatting axis labels individually
Distinctive positive-negative coloring
Our first example is using the first syntax where the first two sections represent positive and negative numbers. Since the sections are optional, you can omit the rest. On the other hand, you may also add formatting for zero section. Otherwise, the zero value (0) gets the positive number formatting.
Including Zero
Do not worry about the "General" text. Excel adds it automatically unless you entered a format for number itself.
Excluding Zero
Showing individual items
By using condition-type syntax, you can display selective items.
Displaying a single value
The following code formats axis items equal to 60 to color Cyan.
There are two important point with this code:
- There must be an item which is equals to 60 on the axis. Otherwise, the custom code doesn't make it visible if Excel doesn't display it. Thus, you may want to adjust axis bounds and unit intervals to ensure your number is in the axis.
- You need to put a semicolon (;) at the end to hide the remaining items. The semicolon marks that you passed the next section. If you end with a semicolon, Excel thinks that there won't be a number format for the last section.
Mark a single value
Set different colors for number ranges
Since you have three sections for the conditions, you can set up to three colors.
Bonus: Special characters and emojis
Another cool application of individual labels are non-alphanumeric characters. Almost all emojis exist as a Unicode character which you can simply copy-paste to your code. So, Google any character you want and use.
