[FAQ's: OD] How to do Conditional Formatting in MS Excel
Conditional Formatting
Note: This has been tested in Excel 2003
Conditional Formatting enables you to apply cell Formatting selectively and automatically, based on the contents of the cells
For Example, you can set things up so that all negative values in a range have a light-yellow background color. When you enter or change a value in the range, Excel examines the value and evaluates the Conditional Formatting rules for the cell. If the value is negative, the background is shaded, If not, No Formatting is applied.
Conditional Formatting is very useful for quickly identifying erroneous cell entries or cell of a particular type.
1) Specifying Conditional Formatting
To apply Conditional Formatting to a cell or range
a) Select a cell or Range
b) Choose Format=>Conditional Formatting. Excel displays the Conditional
Formatting dialog box as shown in picture below
c) In the first dropdown box, select either ‘Cell Value Is’ (for simple Conditional Formatting) or ‘Formula Is’ (for Formatting based on a formula).
d) Specify the condition (or enter a formula)
e) Click the Format Button and specify the Formatting to apply of the conditions is true
f) To add conditions (up to three). Click ‘Add’ and then repeat steps 3 through ‘e’.
g) Click OK.
After you have performed these steps, the cell or range will be formatted based on the condition(s) you specify. This Formatting, of course, is dynamic. If you change the contents of a cell, Excel re-evaluates the new contents and applies or removes the Formatting accordingly.
Formatting types you can apply
When you click on the Format button in the Conditional Formatting dialog box, you get the format cells dialog box as shown in picture below. This is a modified version of the standard format cells dialog box. it does not have the number, alignment, and the Protection Tabs, and it includes a Clear button.
Specifying conditions
The left most drop-down list in the Conditional Formatting dialog box (refer to picture 2 above) enable you to choose one of two options:
a) Cell Value is: For simple conditions
b) Formula Is: For more complex, formula based conditions
Let's understand these two in details.
Cell Value is: For simple conditions
When you select cell value is, you can specify conditions of the following types:
a) Between (You specify Two Values)
b) Not Between (You specify Two Values)
c) Equal to (You specify One Value)
d) Not Equal to (You specify One Value)
e) Greater than (You specify One Value)
f) Less than (You specify One Value)
g) Greater than or Equal to (You specify One Value)
h) Less than or equal to (You specify One Value)
You can enter the value(s) directly or specify a cell reference.
Formula Is: For more complex, formula based conditions
When you select formula is, you can specify a formula. Do so by specifying a cell that contains a formula or by entering a formula directly into the Conditional Formatting dialog box. As with normal excel formulas, the formulas you enter here must begin with an equal sign (=).
For Example
a) Select A1:A5 and ensure that the cell A1 is the active cell.
b) Choose Format=>Conditional Formatting. The Conditional Formatting dialog box appears (refer to t picture1 above)
c) Select the Formula Is item in the drop down list.
d) Enter the formula in the formula box
=A1=""
e) Click the format button to display the format cells dialog box
f) In the format cells dialog box, specify a pattern for the cell shading and click OK to return to the Conditional Formatting dialog box.
g) Click OK to close the Conditional Formatting dialog box.
Notice that the formula entered contains a reference to the upper-left cell in the selected range, to demonstrate that the reference is relative, select cell A5 and examine its Conditional Formatting formula. You’ll see that the Conditional Formatting formula for the cell is
=A5= “”
Generally, when entering a Conditional Formatting formula for a range of cells, you’ll use a reference to the active cell – which is normally to the upper-left cell in the selected range. One exception is when you need to refer to a specific cell.
For example, suppose that you select Range A1:B5 and you want to apply Formatting to all cells in the range that exceeds the value in cell C1. Enter this Conditional Formatting formula:
=A1>$C$1
In this case, the reference to cell C1 is an absolute reference; it will not be adjusted for the cells in the selected range. In other words, the Conditional Formatting formula for cell A2 looks like this:
=A2>$C$1
The relative cell reference is adjusted, but the absolute cell reference is not.
Working with Conditional formats
This section describes some additional information about Conditional Formatting that you might find useful.
a) Multiple Conditions
You can specify as many as three conditions by clicking the Add button in the Conditional Formatting dialog box (refer to picture 2). For example, you might enter the following three conditions (and specify different Formatting for each):
Cell value is less than 0
Cell value is equal to 0
Cell value is greater than 0
In this case, The sign of the value (negative, 0, or positive) determines the applied Formatting.
If none of the specified conditions is TRUE, the cells keep their existing formats. If you specify multiple conditions and more than one condition is TRUE for a particular cell, excel applies only for the Formatting for the first TRUE condition. For example, you may specify the following two conditions:
Cell value is between 1 and 12
Cell value is less than 6
Entering a value of ‘4’ satisfies both conditions. Therefore, the cell will be formatted using the format specified for the 1st condition.
b) Things we need to take care while pasting
We need to be very careful; while pasting copied data as it’s very easy to wipe out the Conditional Formatting in a cell or range.
c) Copy cells which contain Conditional Formatting
Conditional Formatting information is stored with a cell much like standard Formatting information is stored in a cell. This means when you copy a cell that contains Conditional Formatting, the Conditional Formatting is also copied.
Inserting rows or columns within a range that contains Conditional Formatting causes the new cells to have the same Conditional Formatting.
d) Deleting Conditional Formatting
To remove only Conditional Formatting (and leave other Formatting intact), you need to use the Conditional Formatting dialog box.
Select the cells; then choose Format->Conditional Formatting. The Conditional Formatting dialog box appears.
Click the delete button in the Conditional Formatting dialog box. The delete Conditional dialog box appears
In the delete Conditional Formatting dialog box, specify the conditions that you want to delete. This dialog boxes always display check boxes for three conditions, even if you haven’t defined many.
Click OK to dismiss the delete Conditional Format dialog box, and then click on OK again to close the Conditional Formatting Dialog Box.
e) Locating cell that contain Conditional Formatting
You cannot tell, just by looking at a cell, whether it contains Conditional Formatting. You can, however, use Excel’s Go-To Dialog box to select such cells.
Select Edit->Got To (or press F5) to display the Go-To Dialog Box.
In the Go-To Dialog Box, Click the special button. The Go-To Special Dialog box appears (As show in picture below)
Select the Conditional Formats option
To select all cells on the worksheet containing Conditional Formatting, Select the All option. To select only the cells that contain the same Conditional Formatting as the active cell, select the same option.
Click OK and Excel selects the cell for you.
f) Using Reference to other sheets
If you enter a Conditional Formatting formula that uses one or more references to other sheets, excel responds with an error message. If you need to refer to a cell on a different sheet, you must create a reference so that cell on the same sheet contains the Conditional Formatting. For example, if you Conditional Formatting formula needs to refer to cell A1 on sheet 3, you can insert the following formulas into a cell on the active sheet.
=Sheet3!A1
Then use a reference to that cell in you Conditional Formatting formula.
Some Conditional Formatting Formula Examples
a) Identifying non numeric data
=ISTEXT(A1)
b) Identifying dates in particular month
=MONTH(A1)=6
c) Identifying today’s date
=A1=TODAY()
d) Identifying maximum value in a range
=A1=MAX=($A$1:$A$30)
Example 1 Using (Cell Value Is)
Example 1 Using (Cell Value Is)
Condition: Checking if the cell is blank.
Simply follow instructions in the picture.
Example 2 Using (Formula Is)
Example 2 Using (Formula Is)
Condition: Checking if the value entered in the cell is 'Text'
Simply Follow Instructions in the Picture
