In this blog post I’ll walk through some of the new Conditional Formatting features in Excel 2010 such as:
- Cross-Sheet References
- Robust Error Handling
- Performance Enhancements
In Excel 2010, you can create conditional formatting with references to a different sheet on the workbook. This means that you no longer need to copy or link your data to the same sheet when using conditional formatting.
For Example, let us look at the monthly sales data for a bookstore chain. The bookstore organizes their sales data as follows:
- Each location gets its own worksheet
- The last worksheet contains the cumulative company wide sales figures with the averages for each department.
We will apply conditional formatting so that we can easily determine if the department at the local branch is doing better or worse as compared to the other branches in the company. Let us use the San Francisco branch as our example. We will apply a green background fill for the departments if they are performing better than average.
1. Start on the sheet for San Francisco location and select the cells in the sales column to which you want to apply conditional formatting. Go to the Home Tab-> Conditional formatting -> Highlight Cell Rules -> Greater Than.
2. Click on the (RefEdit icon) and simply navigate to the sheet whose value you want to compare against. In our case, we will navigate to the company wide worksheet and click on the average for the first row in the department column.
3. Now here is a neat little tip – references in Conditional Formatting follow all the rules of absolute and relative references. The Cell References are relative to the active cell in the “Applies To” range (in this case the top-left cell in the range).
Let us modify the reference to be relative to the rows by deleting “$” symbol before the row number.
The result is that each department on the “San Francisco” sheet being compared to the same type of department on the “Company” sheet.
4. Click OK and you have a Conditional Formatting Rule that is dependent on other sheets in the workbook.
Have you ever used ranged based conditional formatting (such as icon sets, color scales and data bars) only to find that one of your formulas have evaluated to an error? In Excel 2007, errors in the range would lead to conditional formatting not being applied to all the cells in the range. Users told us that this was frustrating.
In Excel 2010, conditional formatting silently ignores the errors and continues applying conditional formatting to the rest of the cells in the range.
For this release we’ve also improved the performance of formatting in Excel 2010. For conditional formatting we store dependencies of the formulas used so we don’t need to reevaluate the entire conditional format as often. We’ve also made banding in the table and pivot table styles faster, so this will impact things like refreshing a pivot table or scrolling with a large table on your sheet. For all the above cases, we also cache the format of the cell so if you scroll off the screen and come back, or switch sheets and come back, we’ll remember the cell formatting and can display faster.
In our next Excel 2010 post, we’ll look at a new object model feature for conditional formatting.