Excel Dashboards & Reports for Dummies, 4th Edition
Book image
Explore Book Buy On Amazon

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.

About This Article

This article is from the book:

About the book author:

Michael Alexander is a Microsoft certified application developer and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community.

This article can be found in the category: