Microsoft Excel

New Worksheet Functions

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.

Calculates a conditional average (similar to SUMIF and COUNTIF).

Calculates a conditional average using multiple criteria.

Calculates a conditional sum using multiple criteria.

Calculates a conditional COUNT using multiple criteria.

In addition, 39 worksheet functions that used to require the Analysis Toolpak add-in are now built-in. Excel 2007 also includes seven new CUBE functions that retrieve data from SQL Server Analysis Services.

Enhanced Conditional Formatting

Conditional formatting refers to the ability to format a cell based on its value. Conditional formatting makes it easy to highlight certain values so that they stand out visually. For example, you may set up conditional formatting so that if a formula returns a negative value, the cell background displays green.

In the past, a cell could have at most three conditions applied. With Excel 2007, a you can format a cell based on an unlimited number of conditions. But that's the least of the improvements. Excel 2007 provides a number of new data visualizations: data bars, color scales, and icon sets.

Excel 2007 includes quite a few other improvements to conditional formatting. In general, conditional formatting is much more flexible, easier to set up, and relies less on creating custom formulas to define the formatting rules.

Formula AutoComplete

Entering formulas in Excel 2007 can be a bit less cumbersome, thanks to the new Formula AutoComplete feature. When you begin typing a formula, Excel displays a continually updated drop-down list of matching items, including a description of each item. When you see the item you want, press Tab to enter it into your formula. The items in this list consist of functions, defined names, and table references.

Formula Auto Complete in Excel 2007