• Illustration: Quick Charts in Excel 2007
• Illustration: Chart Design and Layout Tabs
• Illustration: New Conditional Formatting Effects

Faster calculations and quicker formatting for presentations are coming with Excel 2007, the next version of Microsoft's spreadsheet application. Of the Office 2007 client applications, Excel 2007 incorporates the most improvements over its predecessor and could benefit both experts and occasional users. However, all users will need to change work routines for the new user interface, and Excel's new graphics features can't substitute for sound data presentation.

More New Client Features than in 2003

While less widely used than Outlook and Word, Excel remains a critical application for many business users. Historically, Excel's vanguard of users was financial analysts and others who use its calculation and charting capabilities to define and evaluate numerical models. However, Excel is also pressed into service as a desktop database management tool, where it's often used to consolidate data across multiple systems and occasionally even as the primary data store for smaller data sets.

Excel 2007 improves all of the product's main functions:

• Charting and formatting features have been simplified and updated with new graphics styles, reducing the number of steps required to create attractive presentations of data
• Data management features introduced in Excel 2003 have been simplified and extended, making them more useful for occasional users and potentially helping experts create more maintainable spreadsheets
• Calculation now takes advantage of more memory and processors, and larger spreadsheets are supported, enabling larger and more compute-intensive models.

Many Excel 2007 client improvements aren't new features, per se, but improved graphics styles and simplifications of the user interface, using new Office 2007 mechanisms such as galleries, which provide thumbnail previews of how selected options will look, and "live preview," which enables users to hover over a command and preview the result of that command in the document. In addition, Excel 2007, like Word 2007 and PowerPoint 2007, introduces a new, XML-based file format. (Excel 2007 will also have a new binary format, intended for very large models that would take too long to load in XML form.)

The most interesting new features in the last version of Excel, Excel 2003, supported integration with Microsoft servers, particularly SharePoint Portal Server, Windows SharePoint Services, and Windows Rights Management Services. In contrast, the new Excel 2007 client delivers notable improvements that work with or without new Microsoft servers—all of the Excel 2007 client improvements described here can be exercised without server upgrades.

At the same time, Excel 2007 does include new features for Microsoft servers, particularly the upcoming SharePoint Server 2007. Most notably, Excel will be able to publish spreadsheets to a new SharePoint component (called Excel Services), which allows users to access shared spreadsheets with a browser. Excel 2007 also has many features to support On-Line Analytic Processing (OLAP) with SQL Server 2005 Analysis Services.

Charting and Presentation

Charting improvements include faster chart formatting and fresh designs.

A new graphics engine enables many new visual effects, including finer drop shadows for objects, gradient color fills, and more colors overall. Default chart styles use these effects, so an initial attempt at a chart often looks better than the corresponding chart in Excel 2003. (For examples of these styles, see the illustration "Quick Charts in Excel 2007".)

More formatting choices are offered for users who want to go beyond the defaults. A new Design tab holds galleries of Quick Styles and Quick Formats for charts; each gallery offers a set of coordinated formatting options that the user can select with a single click. Users can then fine-tune the results in a Layout tab, which exposes many Excel charting options that were previously buried in property dialog boxes. (For a view of the custom chart design interface, see the illustration "Chart Design and Layout Tabs".) Excel 2007 charts can be exported as template files, which could enable organizations to redistribute standard chart styles more easily than redistributing an entire spreadsheet template.

PivotChart improvements, useful for OLAP users and others who analyze multidimensional data tables, include the similar formatting options as regular charts, although some chart types, such as scatter plots, are still not supported as PivotCharts.

Conditional formatting, introduced in Excel 2003 to format spreadsheet cells based on values (e.g., to color negative values red in a financial spreadsheet), offers new graphical effects; for example, it can superimpose data bars over cell values, or draw icons next to notable values. Excel 2007 also simplifies the user interface for writing formatting rules, supplying many examples for common conditions, e.g., formatting the top 10% of values in a range. (For examples of these effects, see the illustration "New Conditional Formatting Effects".)

Integration with other Office applications is improved, particularly with Word and PowerPoint. Both Word and PowerPoint 2007 will use Excel 2007 for creating and editing embedded charts, offering all the Excel 2007 formatting options. (The older Microsoft Graph charting system, the default in Word and PowerPoint, will still be available in those applications when Excel 2007 is not installed on the same system.) Excel, PowerPoint, and Word all support the new Office 2007 themes mechanism, which enables a user to adjust the colors and fonts of all objects in a document to a single, coordinated set. The themes feature will be particularly useful for matching embedded Excel charts and spreadsheets to the Word or PowerPoint documents that contain them.

PDF and printing improvements. Excel 2007 (as well as Word 2007 and PowerPoint 2007) can print directly to Adobe Portable Document Format (PDF) files and to Microsoft's analogous XML Paper Specification (XPS) format. A new Page Layout View in Excel 2007 provides a simpler way to adjust margins, headers, and footers for printing than using the dialog boxes in earlier Excel versions.

Data and Tables

Excel 2007 delivers several improvements for users who employ it as a "quick-and-dirty" database management system. Together, the improvements don't radically change the product's data management capabilities, but they could make the full capabilities more visible to occasional users.

Most noticeably, Excel 2007 expands support for user-defined tables, which were called "lists" in Excel 2003. In Excel, a table is a range of a worksheet that contains rows of data, typically headed by named columns. When the user marks a range as a table, Excel 2007 provides a number of useful features for maintaining the table, including the following:

Automatic naming and table formulas. Excel will automatically name the table and its columns, and enable the user to employ these names in formulas. For example, the user can write a formula like "=SUM(FY05[Revenue])" to sum the Revenue column of the user-defined table named FY05. This can substantially simplify maintenance of a spreadsheet.

Editing and expansion. Excel will automatically expand a table to include new rows and columns and update values that depend on the table. For example, when a row is added to a table, a cell that references one of its columns (e.g., a cell with the formula "=SUM(FY05[Revenue])") will automatically recalculate.

Formatting. Excel 2007 provides a gallery of quick table formats (e.g., alternating light and dark rows) and formatting commands, and automatically extends formatting to new rows and columns of tables.

Utilities. Excel 2007 sorting and filtering commands automatically recognize the boundaries and headers of tables, which makes the commands easier to use and also helps avoid errors (for example, excluding a row at the end of a table during a sort).

Excel 2007 also delivers a few improvements to data management commands even when not used with tables. For example, a new Remove Duplicates utility can quickly remove rows that match others on a set of key columns (e.g., customer rows that match on the "last" and "first" columns). The Excel 2007 sorting command supports more than the maximum of three sort keys in Excel 2003. The autofilter command, which enables users to restrict a worksheet to rows that meet some condition, has also been simplified. It includes templates of typical filtering conditions (e.g., "Next Month" for filtering a date column) and drop-down boxes for selecting column values like those for PivotTables, enabling selection of multiple values without the complicated Boolean expressions required in Excel 2003.

Calculation and Formulas

Excel 2007's calculation engine has been completely rewritten. Many limitations have been removed or raised and formula editing has been improved. These changes will particularly benefit financial analysts and engineers who are pushing Excel's limits with large, computationally intensive models, as well as other users who employ large spreadsheets for data management and consolidation.

Major improvements include the following:

Multithreading. Excel 2007's calculation engine is multithreaded, which enables it to take advantage of multiple processors or processor cores on a computer. Performance improvements are not guaranteed, but spreadsheets with many independent calculations should see speedup on multiple processors or cores. Multithreading also enables calculations to run while Excel is performing input/output (e.g., loading data from a database), which could benefit spreadsheets that work with external data.

Higher limits. Excel 2007 worksheets can now include up to 16,384 columns and approximately 1 million rows, and can use the maximum amount of memory allowed by Windows. Many other limits (e.g., the size of text in cells and the complexity of formulas) have been raised.

New functions, analysis functions built in. Excel 2007 includes new functions which will simplify some complex formulas; for example, a new IFERROR function will simplify formulas that must check for errors in results. Also, the Analysis Toolpak, an optional install in Excel 2003, is built into Excel 2007. The Toolpak provides a variety of useful functions for business calculations (e.g., bond price and yield formulas, work calendar calculations), statistical analyses (e.g., analysis of variance), and engineering (e.g., Fourier analysis).

Formula editor improvements. Complex formulas and long text values could become easier to handle, thanks to changes in the formula bar, the region near the top of the Excel window where cell formulas are displayed and edited. Notably, the formula bar no longer expands automatically to display complete cell contents, a feature that could obscure parts of a worksheet when users selected a cell with a long value or formula. Instead, users can explicitly expand the formula bar by double-clicking it. Excel 2007 also auto-completes more elements of formulas while they are being typed than earlier versions did. For example, it auto-completes user-defined names and the names of functions.

Name debugging. Excel 2007 introduces a new dialog box for managing named cells and ranges, areas of a spreadsheet that are given user-defined names to make formulas more readable. Of particular note: users can search for names that have errors (e.g., they do not refer to any cell or range), a useful feature for large models that use many defined names.

Removed Features and Compatibility

Excel 2007 is mostly compatible with earlier versions. It can read and write earlier file formats, and it can run in a Compatibility Mode that disables features not supported in earlier versions. (Microsoft will also offer a patch to enable Excel 2000, 2002, and 2003 to read Excel 2007's XML file format.)

Nevertheless, there are some incompatibilities, including the following:

File import. Excel 2007 cannot import several file formats (including Lotus 1-2-3) supported by earlier versions, preventing organizations that upgrade exclusively to Excel 2007 from reading archived files in these formats. (Microsoft says the removed formats are almost unused, according to data collected from customer sites by its Customer Experience Improvement Program.)

Charts. Charts from earlier Excel versions will not always import unchanged into Excel 2007 because of the new graphics system. For example, text wrapping could change in an imported chart. Some charting features (e.g., 2D walls and gridlines in 3D charts) have been removed altogether.

Web. Excel 2007 can no longer publish interactive Web pages for standard Web servers using the Office Web Components ActiveX controls; publishing interactive Web pages now requires Excel Services. Web pages published by Excel 2007 will not "round-trip"; when importing such pages back into Excel 2007, data and other elements will be lost.

Evaluating Excel 2007

Overall, Excel 2007 deserves evaluation on its own merits, even in organizations that aren't rolling out complementary servers such as SharePoint Server and SQL Server Analysis Services. Excel is also the most changed of the four applications (Access, Excel, PowerPoint, and Word) that are adopting the new Office 2007 user interface and file formats, and so serves as a good point of departure for organizations evaluating the suite as a whole.

For expert users, Excel 2007 potentially offers a quicker path to presentation-ready charts and spreadsheets, as well as features such as table formulas and name debugging that will help maintain complex models. Experts working with very large or computationally intensive spreadsheet models will also appreciate multithreading and large memory support. Experts may initially be thrown by the user interface, which relocates many of the features they have learned to use, but Excel 2007 retains nearly all of the capabilities of earlier versions, along with their keyboard shortcuts.

Less expert Excel users, in contrast, will benefit from the simpler user interfaces to charting and data management features. Users are more likely to find and make effective use of these features, even though they aren't fundamentally new.

However, the improvements have several limits. Excel charting and conditional formatting features are inherently complex, and even experts will have a hard time deciphering interactions among features (such as the Themes, Design, Layout, and Format tabs for charts) without good documentation and training. Microsoft and organizations that use Excel 2007 will also have to ensure that the Quick Formats and other provided style elements stay current with the latest graphical design fashions; otherwise, users will no longer use them as a starting point. Finally, Excel can't dictate good judgment in data presentation; its new capabilities create new opportunities for users to accidentally obscure or deliberately deceive with beautifully formatted charts and worksheets.

Availability and Resources

Excel 2007 is scheduled to appear for volume license customers in Oct. 2006 and for retail and PC buyers in Jan. 2007. The Professional Edition of Excel 2007 will be required for some capabilities (such as protecting worksheets with Windows Rights Management), but all of the features described here are planned for all editions.

A blog previewing Excel 2007 features in detail is blogs.msdn.com/excel.

The Excel Services Web-based spreadsheet application is outlined in "Excel 12 to Get Server" on page 25 of the Nov. 2005 Update.

Office 2007 user interface changes are outlined in "Office 12: New User Interface, Content Management" on page 23 of the Oct. 2005 Update.

New file formats for Office 2007 are discussed in "Office 12: New File Format" on page 27 of the July 2005 Update.

Code compatibility for Office macro users and developers is discussed in "Custom Code Moves to Office 2007".