Introduction to the new features of conditional formatting in Excel 2010

New features for conditional formatting in Excel 2010 This article contains the following: Here are some new conditional formatting features in Excel 2010:

Cross-table references

Robust error Handling

Performance enhancements

Cross-table references

In Excel 2010, you can create conditional formatting that refers to different worksheets in the workbook. This means that when you use conditional formatting, you no longer need to copy or link data to the same worksheet.

For example, look at the monthly sales data for a chain bookstore, which organizes sales data in the following ways:

Each location has its own worksheet

The last worksheet contains the cumulative sales data for the entire company, using the average of each department.

Below we apply conditional formatting to make it easy to determine if the department in the local branch is doing better or worse than the other branches of the company. Let's use the San Francisco branch office as our example. We will apply a green background to fill those departments that are higher than the average.

Step 1 Open the worksheet that contains the San Francisco, and select the cells in the sales column that you want to apply conditional formatting to. Click Start-Conditional formatting-highlight cell rule-greater than.

Step 2 Click the RefEdit icon in the dialog box to navigate to the worksheet where you want to compare the values. In this example, navigate to the company-wide worksheet and click the average of the first row in the Department column.

Step 3 is an excellent tip--references in conditional formats follow all absolute and relative reference rules. The cell reference is relative to the active cell in the range of cells that you applied (in this case, the upper-left cell of the range of cells).

The $ symbol before the row number is deleted, and the modification reference is relative to the row.

The result is that each department in the San Francisco worksheet is compared to the same type of department in the company worksheet.

Step 4 Click OK to establish a conditional formatting rule that depends on the other worksheets in the workbook.

Error handling

Have you ever used a conditional-formatted region (such as icon sets, levels, and data bars) to find a formula with a wrong result? In Excel 2007, errors in a range of cells cause all cells in the range to not apply conditional formatting.

In Excel 2010, conditional formatting silently ignores these errors and continues to apply conditional formatting to the remaining cells in the range.

Performance enhancements

The performance of the format has been improved in Excel 2010. For conditional formats, we store the interdependent formulas used, so there is no need to reevaluate the entire conditional format frequently. We also make banded tables and PivotTable styles faster, so this can affect operations such as refreshing a PivotTable report in a worksheet or scrolling a large table. In all of the above cases, we also cache the format of the cell, so if you scroll off the screen and return, or switch the worksheet and return, we will remember the cell format, which can be displayed faster.