3.2 Conditional formatting
04-Oct-2022
3 mins
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 
under the Home tab and select Highlight Cells Rules.
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”.
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):
=$D2=”X”
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).
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.
And by rewriting in “Applies to”, our chosen interval, then we validate the formatting.
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.
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:
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:
By clicking Manage Rules, you can also make sure to limit the percentage from which icon types will appear, as in the example below:
You can also combine progress bar and icon; you will only be limited by your own creativity (and basic graphics rules😊).