Using Conditional Formatting
If you want a quick visual way of
seeing whether values in a spreadsheet meet certain criteria then conditional
formatting is a good solution. To apply conditional formatting, first highlight the values you want to format if they meet the condition (e.g. a whole column of numbers). Now click on the Format drop-down menu and choose Conditional
Formatting, a window appears as shown below.
You can use the Condition
boxes to set up your condition by selecting options from the drop-down boxes and
entering either a number, text or a cell reference to compare with in the right
hand value box. For each condition
you set you can then click the Format button to choose the font colour,
size and weight and any shading or bordering you want on those cells. By clicking the Add button in the main Conditional Formatting
window you can add more criteria and set different formats if they are met. In our example the cell has red text on a white background if it is
greater than 150 and blue text on a shaded pale blue background if it is greater
than 140. Cells which do not meet
either of these conditions will stay in their original font. If you are using multiple conditions, as in this example, it is important
to put the value that is less likely to be matched first (in this case, the
higher one) as Excel will format the cell according to the first condition that
evaluates to True. If our
conditions were the other way round, all the cells would be formatted according
to the greater than 140 format.
Here is a sample of our conditionally formatted data:
The above example basis the conditional format on the
value of the cells. However, it is also possible to use a formula to set
the conditional formatting. In the following data the A and B values often
add up to 4 but we may want to only format those where both A and B are equal to
To apply this type of conditional formatting we first
click on cell D2 and set the formatting for row 2. We test whether the
values for A and B are both equal to 2 using the formula:
This will return the value 1 representing 'True' if the
expressions on both sides of the comma in the bracket evaluate to 'True'.
In the example above we have set the cell to
be shaded with a yellow background if the condition is met.
To copy conditional formatting which
involves a formula without disrupting the current values of the cells you are
copying to you can use the Paste Special facility. Copy the cell that has
your conditional formatting applied and then highlight the cells you want to
copy the formatting to. Click on the Edit menu and select Paste Special
and then click to place a bullet beside Formats.
When you click OK the formatting will be
applied to the selected cells without changing the results of any calculations
in them. Here is our formatted data:
To find out which cells have had
conditional formatting applied to them, press CTRL and G together
to display the GoTo window as below.
Click the Special button and
the window shown below appears:
Click the bullet beside Conditional
Formats and, if you want to highlight all the conditionally formatted cells,
click beside the word All under Data validation. To only find cells that have the same conditional formatting as the cell
you were clicked on before entering this window, put the bullet beside Same. When you click OK, the cells with conditional formats will be
If you want to remove the
conditional formatting you can highlight all the formatted cells by using the
technique just described to find them and then click on the Format
drop-down menu and choose Conditional Formatting to display the
Conditional Formatting window. Click
on the Delete button at the bottom of the window and a small window
appears where you can choose the conditions you want to delete as below. Click OK to leave
these windows and the formatting for the deleted conditions will be removed.
Click here to
return to the Excel index.