EXCELUsing Conditional FormattingIf 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.
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 2:
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: =AND(B2=2,C2=2) 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 highlighted. 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. |
