Excel: The eyes have it
Some of Excel's useful but underused features have been brought to the fore in Office 2007, including saving a workspace (a collection of open workbooks), freezing panes and tracking changes. You can quickly remove duplicate rows thanks to a new interface.
Likewise, PivotTables, powerful but complex analysis tools, are easier to create and manipulate thanks to a Task Pane that lets you check boxes rather than use the awkward drag-and-drop technique of previous versions. The interface makes it easier to apply filters and select multiple conditions at once. New styles make PivotTables more attractive.
In addition to the graphic artistry of SmartArt, Excel's charts are much better looking -- shadows and glow effects add great dimension (see Figure 10) -- and are easier to control.
 |
|
|
Shadow effects on a pie chart (Click image to see larger view) |
Charts aren't the only way to understand data quickly, however. Excel offers new ways to compare data or spot anomalies. For example, you can set the background of a cell to show a bar with a length proportional to the data value contained within it.
The feature uses gradient fills with moderate success; we found it useful for comparing values but not for evaluating values (a bar twice as long as another is only a rough approximation of the values it represents). A further caveat: In some worksheets we created, a bar representing a value of 200 didn't always look twice as long as a bar representing the value 100 (see cells D2 and D3 in Figure 11).
If you like, you can instead use full-color backgrounds to indicate values (see Figure 12), or assign icons (up or down arrows, for example) based on the values within cells.
 |
|
|
Indicate cell values with color (Click image to see larger view) |
A different approach to visualization is to use a color scale (from green to yellow to red, for example -- you can choose a default color scheme or specify your own beginning and ending colors). This is easy to apply but initially proved more puzzling to change. (The secret is to use the Manage Rules option from the Conditional Formatting button in the Home Ribbon, then select "Format all cells based on their values" -- see Figure 13.) Also new: icon sets (think of inserting a traffic signal's red, yellow or green light into a cell) based on cell values.
Table formatting takes advantage of a gallery of styles (shown in Figure 14), and a new set of cell styles lets you highlight individual cells or ranges with some attractive, predefined settings (similar to styles in Word).
 |
|
|
Pick a table style and go (Click image to see larger view) |
Speaking of formatting, as with Word, PowerPoint and Access, if you're connected to the Internet, you can easily choose from a variety of templates from the Microsoft Office Gallery. Use one and it is added to the File/New dialog box's "Recently Used" section (complete with thumbnail image).
The Lowdown on Office 2007
 |
The Formulas Ribbon contains colorful icons for formula categories (date and time, logical, financial and so on), but there's no new help once you begin building a formula (the Excel 2003 interface, maintained in Excel 2007, is probably adequate for most users). Unfortunately, if you press F1 for help, Excel isn't smart enough to show you a help screen for the function being used.
As with Word, the organization of some Excel 2007 features is puzzling. Locking cells is under the Format command of the Home Ribbon, not in a separate security group. Many formatting buttons actually sit in the Number group on that Ribbon (for changing the number of decimal points, for example) -- options that were found using the Format/Cells menu command in Excel 2003. To insert a symbol, you'll find the Symbol button in the Text group of the Insert Ribbon in Excel but in the Symbols group in Word (which has a small gallery of frequently used symbols).
One thing's for sure: There are fewer constraints in Excel. You can now have 16,385 columns (up from 256 in previous versions) and 1,048,576 rows (up from 65,536). Other limitations have also been eased: A workbook can have 1,024 spreadsheets, there are 64 levels of sorting (including the ability to sort by color) and AutoFilter drop-downs can show 10,000 conditions (up from three). Functions can now have 255 arguments, and the maximum length of formulas has been raised to 8,000 characters (up from an already generous 1,000) with support for up to 64 levels of nesting (up from seven); in addition, the formula bar now expands to handle long formulas. Functions can now have 255 arguments (up from 30). For other new limits, see MSDN's Excel 2007 blog.
Excel has a neat trick up its sleeve when it comes to tables. Right-click in a table and choose Create Table; Excel will automatically create column headings and AutoFilters. Those headers stay in view (the column headings replace the standard column letters) as you scroll down the page -- without any work on your part.