New and Improved for 2007

First, we'll highlight the new features in the 2007 release of Microsoft Office and Excel, and later, we'll talk about features that have been "retired" from the program and summarize the features that will be new to you if you skipped the last upgrade.

Results-Oriented User Interface

The most obvious change in Excel 2007 is the way it looks and operates, as you can see in Figure 1-1. The familiar toolbars and menu bar are essentially gone, replaced by a single toolbar and a new "dashboard" called the Ribbon, with a task-oriented structure and easily recognizable tools. Most of the old menus have been reborn as tabs, except for the old File menu, which is now a big round button with the Microsoft Office logo on it, called the Microsoft Office Button. While you are working, you'll see applicable tools in context, showing you only the tools you need for the current task. For example, when you select a chart, the Chart Tools collection of contextual tabs appears on the Ribbon. And when you right-click pretty much anywhere on a worksheet, a context-sensitive menu of applicable commands appears as it has in past releases, but if appropriate, a floating toolbar also appears containing pertinent buttons.

Figure 1-1: Excel 2007 looks and operates differently than previous versions. For example, the big orb with the Microsoft Office logo in the upper-left corner replaces the File menu.

For more information, see "Exploring the Ribbon" on page 30; also see Chapter 3, "Custom-Tailoring the Excel Workspace."

Graphic Enhancements

Excel has improved greatly over the years as a platform for graphics and design, and this release includes a few subtle enhancements. Microsoft has folded the old Insert Diagram and Organization Chart features into a much more robust SmartArt feature. You display the SmartArt gallery shown in Figure 1-2 by clicking the SmartArt button on the Insert tab on the Ribbon. All the familiar devices such as Venn diagrams and flow charts are there, but now they sport a more sophisticated look. Other features with updated graphics include conditional formatting, shapes, charting, and the new document themes and cell styles.

Figure 1-2: The SmartArt gallery gives a clue to the kind of visual enhancements added in Excel 2007.

For more information about graphic tools including SmartArt, see Chapter 10, "Creating Spiffy Graphics."

Improved Formatting and Output Tools

Microsoft has built a lot more intelligence into the formatting features of Excel. Managing printed headers and footers is now easier using the new Page Layout view. And the new themes feature makes it possible to define distinctive color and font combinations that you can share with other Microsoft Office 2007 programs, making it easier to create a consistent look among your various electronic and paper-based communications materials. Many of the new formatting features such as cell styles come with palettes of presets, and you see live previews without actually having to apply any formats. Figure 1-3 shows the Color Scales palette of conditional formatting being used to find an appropriate preset to apply to a data table. Simply resting the pointer on the items in the palette causes Excel to change the selected area in the worksheet (or object), showing you what will happen when and if you click.

Figure 1-3: Rest the pointer on most palettes to see live previews of the effects on selected cells or objects.

Page Layout View

Now you can see exactly what's going to happen when you print-at all times, if you like. Unlike Print Preview, Page Layout view is really more than a view; it's a working mode with rulers and page previews in which you have full editing functionality and control over the appearance of your documents, as you can see in Figure 1-4. If you create a lot of worksheets destined for publication, this might become your view of choice.

Figure 1-4: Page Layout view is a welcome new feature for fine-tuning printed output.

For more information about themes, cell styles, and other formatting features, see Chapter 9, Worksheet-Formatting Techniques." For more about output options, including Page Layout view, see Chapter 11, "Printing and Presenting."

New Templates

Excel now ships with a few more built-in templates for popular uses such as balance sheets and expense reports, but the real news is the enhanced New Workbook dialog box that gives you access to a vast library at Microsoft Office Online. Dozens of templates are available, addressing tasks ranging from the commonplace to the esoteric, such as a Bathroom Remodel Cost Calculator and two Baseball Scorecards-with and without pitch count.

For more information about using templates, see Chapter 9.

Updated Conditional-Formatting Features

Conditional formatting is almost like a new feature. It has been around for a while, but this enhancement makes it much more accessible and compelling. You can add visual impact using gradients and data bars to call out trends and thresholds, providing instantly understandable visual cues directly in cells. You can use conditionally triggered icon sets to add graphic elements that help call attention to extreme values in cells, as shown in Figure 1-5.

Figure 1-5: Icon sets give you quick visual clues about relative values within tables of data.

For more information, see "Formatting Conditionally" on page 284.

Increased Capacity and Speed

The new XML file format and other internal improvements give Excel 2007 greatly increased capacities in just about every specification, such as spreadsheets that can hold more than 1 million rows of data and 64 levels of nesting in formulas. If your computer hardware supports it, you'll get built-in support for dual processors and multithreaded chipsets. You probably didn't even know you used to be limited to a mere 4,000 formats in a workbook, but those days are gone; you can now pack up to 64,000 formats into a single workbook. (And what must such a workbook look like?) Here are the most compelling speed and capacity improvements in Excel 2007:

• The total number of available columns in Excel is now 16,384 (was 256).

• The total number of available rows in Excel is now 1,048,576 (was 65,536).

• The total amount of computer memory that Excel can use is now the maximum allowed by Microsoft Windows (was 1 megabyte).

• The number of unique colors allowed in a single workbook is now 4.3 billion (was 56).

• The number of conditional format conditions on a cell is now limited only by available memory (was 3).

• The number of levels of sorting you can perform on a range or table is now 64 (was 3).

• The number of items allowed in the Filter drop-down list is now 10,000 (was 1,000).

• The total number of unique cell formats in a workbook is now 64,000 (was 4,000)

• The maximum number of characters allowed in formulas is now 8,000 (was 1,000).

• The number of levels of nesting that Excel allows in formulas is now 64 (was 7).

• The maximum number of arguments allowed to a function in now 255 (was 30).

• The maximum number of items returned by the Find command is now 2 billion (was 64,000).

• The number of rows allowed in a PivotTable is now 1 million (was 64,000).

• The number of columns allowed in a PivotTable is now 16,000 (was 255).

• The maximum number of unique items within a single PivotTable field is now 1 million (was 32,000).

• The maximum length of the MDX name for a PivotTable item, the string length for a relational Pivot Table, the caption length, and field label length is now 32,000 (was 255).

• The number of fields that a single PivotTable can have is now 16,000 (was 255).

• The number of array formulas that can refer to another worksheet is now limited only by available memory (was 65,000).

• Excel now doesn't have any limit to the number of rows that you can refer to in an array formula (was 64,000).

• The number of categories allowed for custom functions is now 255 (was 32).

• Excel now supports dual processors and multithreaded chipsets.

Better and Easier Tables

What used to be referred to as list management in Excel has metamorphosed into a more robust table feature, incorporating PivotTable-like intelligence with easier formatting and editing. When you create a table, Excel automatically labels column headings and creates filtering controls, so you can quickly and easily focus on table rows of interest. A new Table Styles gallery puts dozens of well-designed formatting configurations at your disposal, providing easy experimentation and modification. Column headings in long tables stay in view as you scroll down the page (as shown in Figure 1-5), and tables are dynamic, so when you add rows or columns, Excel extends the formatting along with the appropriate formulas and filters.

With structured referencing, you can create formulas that cite table elements using column and row labels and special code characters. And the new Remove Duplicates feature in Excel 2007 also makes it easy to flag and remove duplicate entries. You could do this in earlier versions of Excel, but the functionality was hard to find and hard to use. Now the command is right there on the Ribbon.

For more information about tables, see Chapter 21, "Managing Information in Tables." For information about structured references, see "Using Structured References" on page 454.

Improved PivotTables and PivotCharts

Can such a seemingly simple feature as the ability to undo the changes made to a Pivot-Table change your life? Well, it might if you work with a lot of PivotTables. This addition plus a few key improvements to PivotTable filtering, design, and layout add up to an easier PivotTable lifestyle. And, the greatly improved appearance of the charting features trickle down so you can create more compelling PivotCharts, as well, as shown in Figure 1-6.

Figure 1-6: Microsoft has improved PivotTables and PivotCharts, and the new interface makes it easier to work with them.

For more information about PivotTables and PivotCharts, see Chapter 22, "Analyzing Data with PivotTable Reports."

Better Database Connectivity

Along with better PivotTables, Excel 2007 gives you more access to the kind of data that PivotTables were born to massage. Full support for SQL Server Analysis Services is now built in, which includes support for the Unified Dimensional Model (UDM), defining specific business metrics and logical constructions. This provides default fields and metadata for PivotTables, as well as support for Online Analytical Processing (OLAP) browsers and Key Performance Indicators (KPIs).

For more information about connectivity issues, see Chapter 23, "Working with External Data."

Formula AutoComplete

It was just a matter of time. Many of us have gotten accustomed to features that "auto-complete" for us; we get a drop-down list of uniform resource locators (URLs) in the Microsoft Internet Explorer Address text box as we type, Microsoft Office Word 2007 fixes common spelling errors for us (teh to the), and for a couple of releases now, Excel has had in-cell AutoComplete, offering menus of similar entries while we type columns of data. Now, typing functions in formulas is a little easier as well. When you type an equal sign followed by any letter, you get a drop-down list of functions that start with that letter, as shown in Figure 1-7. Type another letter, and the list narrows. If you have defined cell range names in your workbook, these appear in the list as well. As an added bonus, a brief description of the highlighted function appears in a floating ScreenTip. It's very handy.

Figure 1-7: Type an equal sign and any letter (or letters) to display an AutoComplete function list with descriptive ScreenTips.

For more information about functions and AutoComplete, see Chapter 13, "Using Functions."

Tools for Creating Formulas

Besides Formula AutoComplete, Excel 2007 includes a few other niceties for your formula-building pleasure. The new formula bar is manually resizable to let you display or hide lengthy formulas, and you can enter longer formulas than ever, with more levels of nesting (parenthetical expressions within expressions). When you expand the formula bar, the worksheet cells move out of the way rather than the formula obscuring them. And Microsoft has updated the venerable cell range-naming feature to give you greater organization and editing possibilities using the new Name Manager dialog box, shown in Figure 1-8. In addition, Excel 2007 has many small improvements in the way it works internally, including improvements in subtotals and regression formulas.

Figure 1-8: Use the Name Manager dialog box to audit and organize named ranges in your workbooks.

Built-In Analysis Toolpak Functions

The Analysis Toolpak add-in has been around for a long time. In previous editions, you had to manually enable these useful functions. However, at long last, Microsoft has fully integrated the trusty statistical functions of the add-in into the program. The odd news is that the functions now might produce slightly different results; however, the results are so insignificantly different that Microsoft says the results are nonetheless "equally correct."

For more information about using formulas and functions, see Chapter 12, "Building Formulas."

Enhanced Charting Features

Excel 2007 includes lots of new chart types, updated graphics, and a new Chart Tools set of contextual tabs that makes experimentation and modification a whole lot easier, as shown in Figure 1-9. You now have a lot more graphic flexibility with the chart elements; you can apply shadows, bevels, and most anything else you can do with other types of graphic elements. In addition, Microsoft Office PowerPoint 2007 and Office Word 2007 now use the Excel 2007 charting engine, making creating charts much easier and more powerful within those applications, as well as generally making charts in all your 2007 Microsoft Office system documents more consistent and easier to share, link, copy, and update.

Figure 1-9: Charting is easier and flashier than ever.

For more information about charts, see Part 6, "Creating Charts."

New XLSX File Format

Although the phrase new file format tends to send shivers down the spines of IT professionals, we have good news to report. The new Microsoft Office Open XML file formats (with the extension .xlsx in Excel) provide better integration with external data sources and result in significantly smaller file sizes. When saving Excel 2007 files that you need to share with others using previous versions of Excel, you can easily identify features that are not backward compatible in order to make any necessary modifications before saving. In addition, you can install converters that let you open and work on XLSX files in older versions of Excel without affecting any functionality specific to Excel 2007. Also, a new Compatibility mode becomes automatically active whenever you open a workbook saved in an older format. If you try to use an Excel 2007-specific feature while in Compatibility mode (which is indicated in the title bar), Excel lets you know you'll have to save the workbook in the XLSX format to preserve your changes.

For more information about file formats, see "Exploring File Management Fundamentals" on page 45.

Inside Out-Saving in Portable File Formats

Adobe's Portable Document Format (PDF) has historically been the most reliable format for capturing and sharing visual representations of documents across applications and computer platforms. Microsoft's XML Paper Specification (XPS) is the new kid in town with similar skills, created to take advantage of the efficiency of the XML format. Support for one or both formats is available as a separate download. On the Microsoft Web site (www.microsoft.com), click Downloads, and search for the Microsoft Save as PDF or XPS add-in.

Improved Sorting and Filtering

Managing large amounts of data is easier with improved sorting and filtering features. In previous versions, the Sort command allowed three levels of sorting at a time (for example, sorting by State, then by City, then by ZIP Code), but now you can specify up to 64 levels, which you can easily rearrange. The Filter feature shown in Figure 1-10 is better as well, offering the ability to make multiple selections in Filter drop-down lists, filter by color or date, filter PivotTable data, and apply custom filters to multiple items in a list.

Figure 1-10: Filter is more automatic and provides more powerful filtering than ever.

The Remove Duplicates button (shown in Figure 1-10, in the Tools group on the Ribbon) helps make it easier to manage your database information, letting you specify in which columns to look for duplicate information.

For more information about sorting and filtering, see Chapter 21.

Collaboration Enhancements

If you have access to a server running Excel Services on Microsoft Office SharePoint Server 2007, you can save workbooks that other users can access by using Microsoft Office Excel Web Access. You also have control over which parts of the workbook are made available to these users. Excel Services makes it easy because you can upload an entire workbook and then specify both the components you want to publish and the permissions you want to grant. You can also use Excel Services with Document Management Server to establish validation actions and workflow notifications.

For more information, see Chapter 24, "Collaborating on a Network or by E-Mail."

Enhanced Security Features

Security is always top priority, and Microsoft has added a number of new features in Excel 2007 to help address this issue. The Trust Center is a central location where you can control how Excel responds to active content in add-ins, macros, and ActiveX controls, as well as providing control for trusted data sources. Plus, Excel now provides a Digital Signatures interface to help secure documents you share with others.

For more information, see Chapter 4, "Security and Privacy."