Link Search Menu Expand Document
Excel toolbox

3.2 Conditional formatting

Conditional formatting makes it possible to adopt a particular format for a value if it does not meet a certain previously determined criterion.

Example: you want provinces with more than 600 health centres to be displayed in red.

Click on the Conditional Formatting icon image info under the Home tab and select Highlight Cells Rules.

image info

Please note : It is possible to use conditional formatting with multiple simultaneous criteria.

As mentioned above, the formatting conditions may vary. The basic functions of Excel allow different useful options, but if you want to sophisticate your formatting (for instance, by applying it to only one row, or column, or by applying several styles corresponding to answers in different cells), you will need to use formula-based formatting.

To give a concrete example, let’s say you want to indicate, in a file listing your stock entries whether the object is available (which would correspond to colouring the line in green), damaged (which would correspond to a red line) or already out of stock/used (this would correspond to a grey coloured line.) You can download this example here.

The procedure would thus be as follows: when creating conditional formatting, select “New Rule”.

image info

If you want the line to turn grey when an object is out, you can enter the following syntax in the formula bar (at the bottom of the formatting options list):


Here, we add a $ before D2 since we want the conditionality to be verified only in column D, and for each of the lines (that is why we do not set the number 2).

image info

You can then define the formatting you want, as well as the interval at which it should apply. In our case, we want the formatting to apply to a row corresponding to the table, we will thus select the interval from A2 to D8 by clicking Manage Rules.

image info

And by rewriting in “Applies to”, our chosen interval, then we validate the formatting.

image info

And you will notice that depending on whether you select (by adding an X in the corresponding box) the available box, the entire format of the line will change accordingly.

image info

Note : if you don’t change the interval correctly, your formatting will apply anarchically, so pay particular attention to this when you apply conditional formatting. (just like writing your formula), and set your priorities well (the first of the formatting rules, the highest one therefore, will always apply first).

With the same procedure, add red formatting on the line when the status displays “damaged” and green when the object is in stock.

Your file should therefore look like this:

image info

Download this file to check your syntax if necessary.

Additional tips:

You can use icons, and more complex formats in conditional formatting.

For icons, you can for instance use on a change interval, the following icon (which you can access by clicking Conditional Formatting => Icon Sets => icon choices, as shown below:

image info

By clicking Manage Rules, you can also make sure to limit the percentage from which icon types will appear, as in the example below:

image info

You can also combine progress bar and icon; you will only be limited by your own creativity (and basic graphics rules😊).