Software

10 key enhancements in Excel 2007


This information is also available as a PDF download.

Given the learning hurdle that comes with an entirely revamped (albeit improved) user interface, Excel 2007 must offer significant enhancements to encourage you to upgrade. Herewith, my top 10.

#1: Make the Ribbon work for you

The Office 2007 redesign includes some radical new interface elements aimed at making it easier to find the features you need and execute tasks more efficiently. The most prominent of these new elements is the Ribbon, which offers task-oriented tabs with commands readily available instead of hiding in a menu structure.

For some users, this will definitely be an enhancement, but it's hard for others to see past the changes.

From the absence of a "classic mode" in Excel 2007 (and the other Office programs that use the Ribbon), one could draw either of two conclusions:

  • Microsoft was so sure the Ribbon UI would be a boon to mankind that it didn't think anyone would care to look back.
  • Microsoft was so nervous about Ribbon adoption that it didn't dare give us a way to look back.

Whichever it is, many users are finding that mastery of the new interface is a slow go. Here are some ways to mitigate the pain.

First, keep Microsoft's Interactive Excel 2003 to Excel 2007 command reference guide close at hand. With this handy tool, you can click on an old-style menu command and read a tip describing the Ribbon equivalent (Figure A). To get to the guide from within Excel, click the Help button and enter interactive in the Search field. If you use it regularly, open it in your Web browser, then drag the icon to the left of the URL in the browser's Address Bar to your Quick Launch toolbar or desktop. You might even want to install Microsoft's Get Started Tab for Excel. It's a free download that places a Get Started tab on the Ribbon, with options for accessing demos, online tutorials, videos-and the aforementioned Interactive Command Reference Guide. Figure A

Want a more permanent set of training wheels? Install the Excel version of Classic Menu for Office 2007 (See "Add Office 2003 tools and menus to your Office 2007 interface" for more details). This add-in makes a great learning tool, and if you really hate the Ribbon you can use the add-in exclusively.

Finally, if you liked to customize the old menu system and miss the ability to do that in Excel 2007 (we don't count the Quick Access Toolbar as a proper customization tool), check out Patrick Schmid's RibbonCustomizer. This add-in lets you rearrange the Ribbon by filling out a simple dialog box; no programming is required.

#2: Create consistent, appealing formats with styles and themes

Styles, always the smartest way to format Office documents (because they let you change your mind without having to reformat every heading and table), have not always been the easiest way. That's changed now. Excel 2007 offers several kinds of styles, including cell styles, table styles, chart styles, and PivotTable styles.

Cell styles provide a quick way to emphasize "good," "bad," and "neutral" values (see also the new conditional formatting features, described below), as well as to distinguish the functional sections of a worksheet (data from calculations, for example). Table, chart, and PivotTable styles (which appear on the Design tab when a table, chart, or PivotTable is selected) can make your work presentation-worthy with a single click.

Because styles are linked with the set of display themes shared by Word, PowerPoint, and Excel, they can also help you create a consistent look to your work-consistent within itself and consistent with your company's design guidelines.

#3: Build bigger, more capable, worksheets

It's certainly possible that the old worksheet capacity-65,536 rows by 256 columns (16,777,216 cells) was quite enough. But just in case, Excel 2007 has expanded to allow 1,048,576 rows and 16,384 columns (a whopping 17,179,869,184 cells). If you import from large corporate databases, you might actually find the new limits invaluable. And even if you don't, there are plenty of other increases to enjoy. To name just a few: When sorting, you're no longer limited to three fields; you can specify up to 64. You can now stuff as many as 10,000 characters into a formula (a tenfold increase), and your functions are no longer limited to 30 arguments (with the new limit of 255, they can argue till they're blue in their faces). Formulas are also no longer restricted to seven levels of nesting (the new limit is 64).

#4: Check out Excel's new conditional formatting features

With cell styles, you can mark bad or good values statically. With conditional formatting, you can do it dynamically, so that, for example, spending figures turn red only when they exceed associated budget values.

The conditional formatting features are significantly easier and more versatile in Excel 2007. You can do more without writing formulas. For example, menu picks now let you flag such conditions as "top 10 %" or "Above Average." And there are some nifty new display options. With data bars, for example, your numbers can appeal to the left and right brain hemispheres simultaneously, and in the same column (Figure B). With color scales you can create "heat" diagrams -- e.g., red for high numbers, green for low ones. With icon sets you can flag values with traffic lights, arrows, and other easily recognized symbols. Figure B

#5: Turn ranges into tables

Pressing CTRL+T within a worksheet range turns that range into a table, a formal entity that's mostly new in Excel 2007. (Excel 2003's list was a weak forerunner.) Tables offer several advantages over ordinary ranges.

  • You can apply styles to them that do such things as apply horizontal banding-formatting that alternates by row so your eye can more easily follow a row across the table. If you did this (manually) in the past, your banding turned to hash as soon as you sorted the range. Now it stays put no matter what you do.
  • Tables expand intelligently as your data grows. Add a row or column, and all formatting and formulas welcome the new data. Charts built from table columns also expand automatically.
  • Calculations performed on table constituents can use structured referencing-references that identify table cells by their column headings or row positions.
  • You can toggle a total row on or off, displaying sums, averages, and other aggregate calculations when needed.

#6: Let Formula AutoComplete assist with function building

As soon as you type an equals sign followed by a letter, Excel displays the names of all available functions starting with that letter. Type another letter and the list narrows, and so on. Select an item in the list, and a tip appears describing the selected function. If you then press the parenthesis key, the tip changes to enumerate the function's arguments. Macro programmers have long had this kind of help available for VBA functions; now regular spreadsheet jockeys can enjoy the same assist.

#7: Build prettier charts with fewer clicks

The Ribbon is clearly a boon for chart-makers. When you select a data range (or a single cell within a range) and click the Insert tab, the major chart types appear as separate icons within a Ribbon command group. Click one of these major types (Column or Pie, for example), and a gallery of subtypes appears. Once you've planted your chart on the worksheet, three new Ribbon tabs arrive, giving you similarly direct access to nearly all the formatting features you'll need. If you still want to dig deep into the formatting dialog box, you can do so, but most of the time it won't be necessary.

Microsoft disappointed a good many users by not adding new chart types in Excel 2007. But the old standbys have a new look and can dazzle with more razzle. On the Format tab, for example, you'll find a gallery of Shape Effects that let you dress up your charts with glow, soft edges, bevels, and shadows. Best of all, perhaps, Excel now shares its charting engine with Word and PowerPoint.

#8: Find new ways to filter

Probably the coolest change to Excel's Filters (which used to be called AutoFilters) is that they recognize the type of data included in a column and offer options appropriate to the data type. If you filter a column of numbers, for example, the options include such things as Top 10 (it's really top or bottom n; you can pluck out the highest three values, lowest 17, or whatever you need), Above Average, and Below Average. For text, you can do Begins With, Ends With, or Includes. And for dates, you get a huge assortment of intelligent filtering choices: Tomorrow, Today, Yesterday, Next Week, This Week, Last Week, Year to Date, and so on.

The second coolest change is that you can filter a column on multiple discrete values. If your column includes North, South, East, and West, for example, you can use check boxes to restrict it to North and South, omitting East and West. Seems like a no-brainer, but there was no straightforward way to do this in Excel 2003.

#9: Make your PivotTables easier to read; build them with less frustration

Microsoft has improved Excel's PivotTable user interface with each new version, in an effort to make this extraordinarily valuable functionality less intimidating. In Excel 2007, you can populate a PivotTable by selecting field-heading check boxes as well as by dragging field headings onto a table layout (or into a task pane). These changes are marginally useful. More important, the 2007 interface includes some new context menus that make it easier to filter and sort (and you can use all the type-specific filtering options mentioned earlier). With the help of PivotTable styles (which appear on the Design tab when a PivotTable is selected), you can apply formatting that remains stable as the table pivots. And new layout options (Compact, Outline, and Tabular) afford greater legibility to complex tables.

#10 Reduce layout aggravations with Page Layout view

Excel 2007 includes a new viewing option that shows page boundaries as you work (click Page Layout on the View tab to get there). This is essentially an enhanced version of the Page Break Preview mode that has been available in earlier versions. The principal difference is that Page Layout view is fully functional and interactive. That is, you can build your worksheet in it, noting how columns and rows will (or won't) fit as you create them. On older hardware, you might notice a small performance degradation if you work this way.

Craig Stinson has been an industry journalist since 1981 and has written more than 20 books for Microsoft Press. Most recently, he's co-authored Windows Vista Inside Out and Microsoft Office Excel 2007 Inside Out.

Additional Office 2007 "10 things" resources

2 comments
pmoseley
pmoseley

We are in the process of upgrading to Office 2007 and were very excited to hear of the new larger worksheets in Excel 2007. We work with various databases and a data warehouse that have tables in excess of 20 million records. However, we've found the new larger worksheets are only as good as your hardware. Our workstations have 1Gb of memory and it just wasn't up to the challenge. We've doubled the memory to 2Gb and it's better but any large files open, close and save VERY slowly. We've also had trouble with it crashing and losing some work. So before you dive in, make sure your hardware is ready.

howard.price
howard.price

I, too, am finding Excel 2007 to be very poor at dealing with large amounts of data in xy scatter charts. Here, I am talking about a few thousand rows of data and pehaps 10 columns, nothing ginormous! I have 4GB of RAM and a quad core processor and this is not the answer. Charts that has instantaneous respnse times in earlier versions of Excel and totally unusable in 2007. Just re-drawing the chart can take 30 seconds. Somebody at MS needs to get a grip on this.

Editor's Picks