Excel Custom Number Formatting: Auto-Colour Values by Performance

When preparing an Excel dashboard, there are occasions when you may wish to have values highlighted to show performance against a defined target value. For example, performance that is 100% and above should be indicated in green font while all others are to be indicated in red font.

To set up an Excel dashboard so that the values are automatically highlighted in the desired colors when the Target and Actual values are updated, you can use the Number formatting feature in Excel. It is also possible to use the Conditional Formatting feature for this, but if you also wanted to reference these formatted numbers in a chart like the one below, this would not be enabled by Conditional Formatting.

Step 1: To customize the Number formatting of a cell's content, use Home>Number>More Number formats.. or use the keyboard shortcut Ctrl+1.

Step 2: Click the "Custom" Category on the left section of the dialog box, and enter the custom type highlighted in the yellow box below:

This code basically tells the system to colour in green all numbers greater than or equal to 1 (1 is used instead of 100 as fraction division was used to derive the percentages seen) and add a % sign after them, and colour in red all numbers less than 1 and also add a % sign after them.