About this lesson
Leveraging Custom Number Formats in order to provide conditional formatting for chart axes
Download this lesson’s related exercise files.Conditional formatting of chart axes.xlsx
78.9 KB Conditional formatting of chart axes - Completed.xlsx
Conditional Formatting of Chart Axes
Using custom number formats for conditionally formatting chart axes.
When to use
Use when you want to add conditional formatting to a chart axis to show your axis in different colors.
Global syntax of custom number formats
- A custom number can hold up to four different formats, separated by semi-colons
- The general setup is: positive format; negative format; zero format; text format
- If a value doesn’t meet a category, the program will evaluate it against the next format
- If no format is found that supports the data, it will default to the first format
Adding conditional constraints to a custom number format
- Add a conditional constraint by enclosing it in square braces prior to the number format
- Eg [>=15000]#,##0
- If a value doesn’t meet this condition, it moves on to the next condition (next ; )
- The following format shows values in red if they are greater than 500 or less than -500
- If the values fall in between, then they are shown in the default color
- [Red][>=500]#,##0;[Red][<=-500]-#,##0; #,##0
Hints & tips
- You may use a comparison to a negative value in the first test if needed
- Take care not to set up a rule that is always true in the first test. Tests such a <500 would trigger as true for both positive and negative values!
Lesson notes are only available for subscribers.