Excel’s number formatting syntax consists of different individual number formats separated by semicolons. By default, the syntax to the left of the first semicolon is applied to positive numbers, the syntax to the right of the first semicolon is applied to negative numbers, and the syntax to the right of the second semicolon is applied to zeros.
Positive Number Format; Negative Number Format; Format for Zeros
Interestingly enough though, Excel allows you override this default behavior and repurpose the syntax sections using your own conditions. Conditions are entered in square brackets.
In this syntax example, a blue color is applied to any cell containing a number over 500, a red color to any cell containing a number less than 500, and n/a to any cell containing a number equal to 500.
[Blue][>500]#,##0;[Red][<500]#,##0;”n/a”
One of the more useful ways to use conditions is to convert numbers to thousands or millions depending on how big the number is. In this example, numbers equal to or greater than 1,000,000 will be formatted as millions, while numbers equal to or greater than 1,000 will be formatted as thousands.
[>=1000000]#,##0.00,,”m”;[>=1000]#,##0,”k”
You can leverage custom number conditions to force an annotation on a chart if a data point goes below a certain amount. All you need to do is apply the custom number formatting to the source data for the chart.
Imagine that you want any data point in your chart below 300 to show the words Under Threshold. Simply apply this custom number format to the source data range for that series in your chart.
[<300]”Under Threshold!”; #,##0
After your custom formatting is set, you can add Data Labels to your chart series. At this this point, you will have a chart that effectively pops up a dynamic annotation.
Each time a data point goes below 300, the words Under Threshold will draw attention that point.
