dcsimg
Results 1 to 3 of 3

Thread: [FAQ's: OD] How to do Conditional Formatting in MS Excel

  1. #1

    Thread Starter
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    [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)
    Last edited by RobDog888; Feb 27th, 2007 at 12:03 AM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  2. #2

    Thread Starter
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    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.


    Last edited by Siddharth Rout; Feb 18th, 2007 at 04:46 AM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    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

    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width