Working with Excel

Conditional formatting

This can be found on the Home > Style > Conditional Formatting.

Excel Conditional Formatting

Whenever you want to want to use a formula, I found it easier to do this trick. Create the formula next to the column you want to apply the formatting. In the case below, I want to check if the value is greater than 100 (=A2>100, =A2>100 etc)

Excel Template

As we can see the formula works just fine as we get the values TRUE and FALSE. Now it’s time to apply it to the Numbers column and set the colour GREEN if TRUE. Select the range, go to Conditional Formatting and select New Rule…

New Rule Conditional Formatting

Enter the Formula and set the format.

Formula Conditional Formatting

The results:


To extend the formula for all the rows I’ve modified the Applies to value to =$A$2:$A$2000

Conditional Formatting Rules Manager

To format more columns with the same formula use the same logic. By using the dollar sign I was able to lock the column (E) which I want to use in my formula.

Conditional Formatting Multiple columns

Then I applied the formula next to my table and as you can see F and G match in values. Now I can go an add this as Conditional Formatting.

Conditional Formatting Rules Manager Multiple columns

Dollar Symbol ($) in an Absolute Reference

A particularly useful and common symbol used in Excel is the dollar sign within a formula. Note that this does not indicate currency; rather, it’s used to fix a cell address in place in order that a single cell can be used repetitively in multiple formulas by copying formulas between cells.


By adding a dollar sign ($) in front of the column header (C) and the row header (3), when copying the formula down to Rows 7–15 in the example below, the first part of the formula (e.g., C6) changes according to the row it is copied down to while the second part of the formula ($C$3) stays static always enabling the formula to refer to the value stored in cell C3.