Conditional formatting (in pre Excel 2007 versions) had a limitation of only allowing for upto three conditions for formatting cells. A quick way to include more than three conditions when using conditional formatting is to use the custom format feature in tandem with it. Conditional formatting can be used to specify the first three conditions and then upto three additional conditions can be specified using custom format – giving us 6 different colors to work with.
Conditional Formatting to specify three conditions to color numbers
This part is easy. All we need to do is to open up the conditional formatting box and start specifying the conditions that we want to use and colors to go along with them. Just bear in mind that conditional formatting takes precedence over custom format when it comes to using colors. So if you end up specifying the same condition for both, the one that was specified in the conditional formatting will be used.
Custom Format to specify three additional conditions to color numbers
Custom format has the following format:
You can specify either one or more expressions (upto a maximum of 4) as a custom format for a cell. Each of the expression is a combination of a conditional statement (which when evaluating to true triggers the format) followed by the actual format to use when the condition evaluates to true. The first three expressions are reserved for numbers. If the fourth is not specified and is left blank, the application knows that the first three expressions are to be used for custom formatting numbers only.
So lets use the Custom format feature (ctrl+1) and write something like:
Well what does that do? By using this expression you are telling the application to custom format selected cells in the following manner –
– When the cell value is greater than -15, use the color whose value in the color index is 36
– When the cell value is greater than -40, use the color whose value in the color index is 40
– When the cell value is anything else, use the color whose value in the color index is 38
So that helps us take care of three colors. The flip side is that since custom format works only on numbers, you will not be able to format the cell fill color – which also means that we will only be able to format numbers.
Once we have both of the steps completed, we are free to use upto 6 different colors for six different conditions. While the conditions specified in conditional formatting can be made dynamic by specifying formulas instead of absolute numbers, the conditions in the custom format are more or less static and that could be a bit of a drawback of using this approach.
You can download a sample worksheet with a example of conditional formatting with custom format to use more than three colors here or click on the button below: