Excel includes a nifty little feature called conditional formatting that tells the program to format a cell range in one way when the range contains one type of data entry and in a completely different manner when it doesn't. Conditional formatting can go a long way in helping you keep tabs on very significant or potentially disastrous conditions that crop up in your spreadsheet.
Most of the time, you apply conditional formatting to cells that contain formulas whose evaluated values are key in the spreadsheet (such as subtotals or grand totals). Applying conditional formatting to these cells can help you flag any potential errors that result from goof-ups made in the input cells referenced in the formulas. You can also use conditional formatting to flag unexpected formula results, such as totals that exceed your wildest expectations as well as totals that are way below your most pessimistic projections.
This tutorial looks at ways you can use conditional formatting to alert yourself when certain key values change in your spreadsheet (for better or worse). It begins by looking at how you can use conditional formatting to select one type of cell format when normal conditions exist in the cell and another special formatting when some sort of abnormal condition crops up. You also find out how to use conditional formatting to warn you when certain kinds of errors have crept into the spreadsheet.

Formats to Suit Every Condition

Conditional formatting enables you to set up two types of criteria that determine when the program applies the conditional formatting you designate:
  • Cell Value Is: Compares the value (constant) you specify in the Conditional Formatting dialog box against the value entered in the cell. When Excel compares this constant to the cell value and the criteria you specify for them is met (is between, equal to, greater than, less than, greater than or equal to, or less than or equal to), Excel applies the conditional formatting to the cell. When the condition is not met, Excel uses the regular formatting applied to the cell.
  • Formula Is: Evaluates the logical formula you enter in the Conditional Formatting dialog box. When this formula evaluates to TRUE, Excel applies the conditional formatting you define to the cell. When the formula evaluates to FALSE, Excel applies the regular formatting to the cell.
When you want to be warned when a cell contains a particular value or exceeds or falls below a certain number, the Cell Value Is type of conditional formatting is the way to go. To get an idea of how you would use this type of conditional formatting, follow along with the steps for displaying the entry in a cell in red with bold and strikethrough whenever it contains a negative value:
  1. Position the cell pointer in the cell where you want to apply conditional formatting.
  2. Choose Format → Conditional Formatting to open the Conditional Formatting dialog box.
    When Excel opens this dialog box, the Cell Value Is option is automatically selected along with Between as the comparison operator.
  3. Select Less Than in the second drop-down list box (the one that now contains Between).
  4. Press Tab to select the last text box and then type 0.
    After setting up the condition for the special formatting, you must specify what formatting attributes to use. The Font attributes that you can set for conditional formatting are limited to font style, underlining, strikethrough, and color. For this example, I turn on strikethrough and bold and select red as the font color.
  5. Click the Format button in the Conditional Formatting dialog box to open the Format Cells dialog box.
  6. On the Font, Border, and Patterns tabs, select the attributes to be used when the condition is true. When you're finished selecting your attributes, click OK.
    When you close the Format Cells dialog box, the Conditional Formatting dialog box shows your first condition along with a preview of the formatting that Excel will apply when the condition is true. At this point, you can either add another condition (see the next section, "When two conditions are better than one"), or if you need only the one condition - as is the case here - you can close the dialog box.
  7. Click OK to close the Conditional Formatting dialog box and put the conditional formatting into effect in the current cell.

When two conditions are better than one

When setting up conditional formatting for a cell, you're not limited to a single condition. You can set up several conditions, each with its own unique attributes that are used when its particular condition is true. Most of the time, you find that two conditions are completely adequate to cover all the possible contingencies.
To set up a second condition in the Conditional Formatting dialog box (Format → Conditional Formatting), you click the Add button after defining the first condition and the formatting to use when this condition is true. Clicking this button expands the Conditional Formatting dialog box by adding a Condition 2 area with identical controls for defining the second condition and the formatting that it applies.

Finding cells with conditional formatting

Excel makes it easy to locate and select the cells in your worksheet that use conditional formatting. This makes it quick work of finding particular conditional formatting that you want to reuse by copying to other parts of the spreadsheet.
Open the Go To dialog box (F5 or Ctrl+G) and then click the Special button. Excel opens the Go To Special dialog box where you select the Conditional Formats option button and then click OK.
Excel then selects all the cells in the worksheet that contain some type of conditional formatting. You can use the Enter or Tab key or Shift+Enter or Shift+Tab keys to move from one selected cell to another throughout all the ranges.
To save time and select only the cells that contain a certain type of conditional formatting, position the cell pointer in a cell that uses the type you want to locate. Then open the Go To Special dialog box, select both the Conditional Formatting option button and the Same option button, and click OK.

Making Your Outstanding Errors Stand Out

Although conditional formatting is most useful for alerting you to anomalies that crop up in your spreadsheet data, you can also use it to warn you when certain types of errors crop up that aren't related to errors in the formulas themselves. For example, you can use conditional formatting to flag errors in a typical sales table - one that totals the columns and rows of figures to ensure that the sum of the column subtotals and the sum of the row subtotals are always equal to the grand total at the intersection of the two.
Note that the only way the subtotals wouldn't equal the grand total is when you or a coworker accidentally deletes or replaces one of the SUM formulas that calculate a column's or row's subtotal. If you don't protect your formulas against this type of unintentional editing, you can at least set up this type of conditional formatting to notify you if such an error occurs.

Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks


Cloud Power For You

Website Hosting At Low Price


Empire Views