Adding Conditional Formatting on a column

Here we want to make the Salary column to have a red background if the amounts shown are less that 60,000.

1. Click on the column header for column D
   
   
   
2. Click on the Conditional Formatting field on the Styles tab of the Home ribbon on the top of the screen,
   
   Then select Highlight Cells Rules and Less Than...
   
   
3. Change the value in the field on the popup to read £60,000 and set the with field to Light Red Fill with Dark Red Text, then click [OK]
   
   
   
4. We should now see that this has actually caused a problem, in that although it has applied the designed formatting, it has also applied it to all cells below our data too (i.e. D22 and beyond). To fix this - undo this action using the keyboard buttons [CONTROL]-Z (a standard Windows keyboard action) and then apply the formatting again but instead of selecting the whole D column, select D2 and drag down to D21** - then apply repeat the formatting steps

** IMPORTANT TIP: To select all active cells in a column, you can also simply click on the top cell D2 and then use [CONTROL]+[SHIFT]+[DOWN ARROW] on the keyboard - this is actually the recommended method for this kind of action.

Advanced Tip

1. Click on cell B2
   
   
2. From the Conditional Formatting drop down list of options, click on New Rule.... to create a custom rule
3. Now select Use a formula to determine which cells to format and then type in =E2=1 in the Format values where this formula is true: field (IMPORTANT: this value should start with the equals sign, otherwise the result is put into quotes and not work as expected)
   

4. Still on the popup, click on the [Format] button, then select the Fill tab, and select a green
   
   
   
5. Click [OK] from the Format Cells popup
   
   
6. Click [OK] on the New Formatting Rule popup
   
   
7. Click on cell B2 and then click on the Format Painter (paintbrush) icon (you should see the cursor changes to a paintbrush)
   
   
8. Select cells E21 through to E2 to apply the formatting to all the selected cells

Background Notes (IMPORTANT)

To see what has happened, or to adjust the formatting

1. Click on one of the selected cells, e.g. E9
   
   
2. Click on Conditional Formatting from the ribbon
   
   
3. Click on Manage Rules....
   
   
4. Click on the first rule in the Rule list
   
   
5. Note that the Applies to value reads =$B$2:$B$21 ($B means "always B regardless of where you apply this formula", and $2 means "always row 2 regardless of where you apply the formula")
   
   
6. Click on [Edit Rule...]
   
   
7. Note that the Format reads =E2=1 even though you are looking at E9

If you were to add new rows in to the table then this may need to be revised