Scribd Logo
Upload

Welcome to Scribd!

  • 68 views

    Conditional Formatting

    Uploaded by

    Lony
    Conditional formatting allows cells to be formatted automatically based on their contents. For example, cells with negative values can be highlighted in yellow. When values change, Excel checks the conditional formatting rules and applies the appropriate formatting. This makes it easy to identify problematic cells. Custom rules can be created using formulas to format cells in various ways, including based on their rank, value compared to average, or whether they are unique or duplicate values. Displaying a single icon from an icon set is possible using two rules - one to format cells meeting the criteria and another to stop formatting cells not meeting it.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    Conditional Formatting

    Uploaded by

    Lony
    68 views3 pages
    Conditional formatting allows cells to be formatted automatically based on their contents. For example, cells with negative values can be highlighted in yellow. When values change, Excel checks the conditional formatting rules and applies the appropriate formatting. This makes it easy to identify problematic cells. Custom rules can be created using formulas to format cells in various ways, including based on their rank, value compared to average, or whether they are unique or duplicate values. Displaying a single icon from an icon set is possible using two rules - one to format cells meeting the criteria and another to stop formatting cells not meeting it.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Conditional formatting allows cells to be formatted automatically based on their contents. For example, cells with negative values can be highlighted in yellow. When values change, Excel checks the conditional formatting rules and applies the appropriate formatting. This makes it easy to identify problematic cells. Custom rules can be created using formulas to format cells in various ways, including based on their rank, value compared to average, or whether they are unique or duplicate values. Displaying a single icon from an icon set is possible using two rules - one to format cells meeting the criteria and another to stop formatting cells not meeting it.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    68 views3 pages

    Conditional Formatting

    Uploaded by

    Lony
    Conditional formatting allows cells to be formatted automatically based on their contents. For example, cells with negative values can be highlighted in yellow. When values change, Excel checks the conditional formatting rules and applies the appropriate formatting. This makes it easy to identify problematic cells. Custom rules can be created using formulas to format cells in various ways, including based on their rank, value compared to average, or whether they are unique or duplicate values. Displaying a single icon from an icon set is possible using two rules - one to format cells meeting the criteria and another to stop formatting cells not meeting it.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 3

    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 checks 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 a useful way to quickly identify erroneous cell entries or cells of a particular type. You can use a format (such as bright-red cell shading) to make particular cells easy to identify.

    Specifying Conditional Formatting


    To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home Styles Conditional Formatting drop-down to specify a rule. The choices are 1. Highlight Cell Rules: Examples rules include highlighting cells that are greater than a particular value, between two values, contain specific text string, or are duplicated. 2. Top Bottom Rules: Examples include highlighting the top 10 items, the items in the bottom 20 percent, and items that are above average. 3. Data Bars: Applies graphic bars directly in the cells, proportional to the cells value. 4. Color Scales: Applies background color, proportional to the cells value. 5. Icon Sets: Displays icons directly in the cells. The icons depend on the cells value. 6. New Rule: Enables you to specify other conditional formatting rules, including rules based on a logical formula. 7. Clear Rules: Deletes all the conditional formatting rules from the selected cells. 8. Manage Rules: Displays the Conditional Formatting Rules Manager dialog box, in which you create new conditional formatting rules, edit rules, or delete rules.

    Making your own rules


    For do-it-yourself types, Excel provides the New Formatting Rule dialog box, shown in Figure 21.3. Access this dialog box by choosing Home Styles Conditional Formatting New Rules. The New Formatting Rule dialog box lets you recreate all the conditional format rules available via the Ribbon, as well as new rules. Following is a summary of the rule types: Format all cells based on their values: Use this rule type to create rules that display data bars, color scales, or icon sets. Format only cells that contain: Use this rule type to create rules that format cells based on mathematical comparisons (greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). You can also create rules based on text, dates, blanks, nonblanks, and errors. This rule type is very similar to how conditional formatting was set up in previous versions of Excel.

    Format only top or bottom ranked values: Use this rule type to create rules that involve identifying cells in the top n, top n percent, bottom n, and bottom n percent. Format only values that are above or below average: Use this rule type to create rules that identify cells that are above average, below average, or within a specified standard deviation from the average. Format only unique or duplicate values: Use this rule type to create rules that format unique or duplicate values in a range. Use a formula to determine which cells to format: Use this rule type to create rules based on a logical formula.

    Displaying only one icon


    In some cases, you may want to display only one icon from an icon set. Excel doesnt provide this option directly, but displaying a single icon is possible if you use two rules. Following figure shows a range of values. Only the values greater than or equal to 80 display an icon.

    Heres how to set up an icon set such that only values greater than or equal to 80 displays an icon: 1. 2. 3. 4. 5. 6. 7. 8. Select the cells, choose Home Styles Conditional Formatting Icon Sets, and select any icon set. Keep in mind that only the last icon of the set will be used. With the range selected, choose Home Styles Conditional Formatting Manage Rules. Excel displays its Conditional Formatting Rules Manager dialog box. Click Edit Rule to display the Edit Formatting Rule dialog box. Change the first icon setting to When Value Is >= 80 and specify Number as the Type; leave the other icon settings as they are, and click OK to return to the Conditional Formatting Rules Manager. Click New Rule and then choose this rule type: Format Only Cells That Contain. In the bottom section of the dialog box, specify Cell Value Less Than 80 and click OK to return to the Conditional Formatting Rules Manager. The range now has two rules. Place a check mark next to Stop If True for the first rule. Figure 21.15 shows the completed dialog box. Click OK.

    The first rule checks to see whether the value is less than 80. If so, rule checking stops, and no conditional formatting is applied. If the value is greater than or equal to 80, the second rule kicks in. This rule indicates that values greater than or equal to 80 are displayed with an icon.

    You might also like