The major improvements in Excel 2007 (see Table C-1) include the completely changed user interface and several new worksheet functions:
The logical functions now include the IFERROR() function that eliminates the need to nest the IF(ISERROR(...);...) functions.
The statistical functions include three new functions: AVERAGEIF(), AVERAGEIFS(), and COUNTIFS(). AVERAGEIF() and AVERAGEIFS() calculate the average using one or more filter conditions. COUNTIFS() complements COUNTIF() to allow number calculations based on more than one filter condition.
The math and trigonometry functions include one new function: SUMIFS() complements SUMIF() to allow summations based on more than one filter condition.
The cube functions category is new. The functions in this category allow formulas to access the data cubes of Microsoft Analysis Services (a database server with special data cubes). The cube functions extend the data access to these databases, which in previous versions was limited to evaluations in PivotTables.
Table C-1. The New Functions in Excel 2007 (in Alphabetical Order)
Table C-2 compares several internal limits in Excel 2007 with the limits in Microsoft Office Excel 2003 and earlier versions. You might not know the previous limits, because they are not relevant for normal tasks performed in Excel. The comparison shows the improvements in Excel 2007 in this area.
Table C-2. Comparison of the Limits in Excel Versions
Description | Excel 2003 and Earlier | Excel 2007 and Excel 2010 |
|---|---|---|
Number of columns in a worksheet | 256 (28) | 16,384 (214) |
Number of rows in a worksheet | 65,536 (216) | 1,048,576 (220) |
Maximum number of characters in a cell | 1,024 (210) | 32,768 (215) |
Maximum length of formulas (characters) | 1,024 (210) | 8,192 (213) |
Maximum nesting levels in functions | 7 | 64 |
Maximum number of arguments per function | 30 | 255 |
Maximum number of array formulas in a worksheet containing references to another identical worksheet | 65,536 (216) | Unlimited |
Maximum number of categories for custom functions | 32 | 255 |
Maximum number of sort keys | 3 | 64 |
Maximum number of elements displayed in the AutoFilter picklist | 1,000 | 10,000 |
Number of rules for conditional formats in a cell | 3 | Unlimited |
Number of different formats in a workbook | 4,000 | 65,536 (216) |
Number of colors in a workbook | 56 | 4,294,967,296 (232) |
Maximum usable RAM | 1 GB (230) | The max RAM of Windows |
The specifications for the new functions and limits apply only if you save your workbook in the file format of Excel 2007 or Excel 2010.
The new file formats save all information (data, formats, and so on) in XML structures. The Microsoft Office Open XML specification has been published and was adopted as an ISO standard. This open standard allows developers to read as well as create the new file format. An exception is the nonstandard binary format that allows you to save and load workbooks quickly.
The XML structures are saved in a compressed file to reduce the file size. Table C-3 lists the new file extensions.