Resouce Guides / Microsoft Office 2007

Microsoft Excel 2007 Review

Excel has long been known for its computational might, but in Excel 2007, the focus is on making the software, and the results of your calculations, easier to understand.

No doubt the first thing you'll notice is the revised interface. As with Word, Access, PowerPoint, and Outlook, Excel's new interface overhaul is designed to put more features at your fingertips, bringing commands that were buried three levels down in dialog boxes and menus up front to the ribbon interface. Like its siblings, drop-down galleries and quick preview help you can see your formatting changes immediately, and the wide variety of designer-quality formatting options give your charts and tables a very professional look. That's to be expected - many of the Excel improvements Microsoft touts have to do with visualizing your data.

You can now use Office's Styles feature to format Excel cells. Microsoft calls this visual annotation.

On the other hand, some features have been annoyingly shuttled off to the side. For example, to use the macro recorder, you have to explicitly turn on the Developer tab, something I had to probe the Help system to figure out.

More is better, of course, and in Excel 2007 you'll get more - much more. Excel will now support 1,048,576 rows and 16,384 columns. Dozens of other limits have been removed (you can use an unlimited number of format types in a single workbook - up from an already huge 4,000), and a formula can now refer to as many cells as your system's memory can accommodate (up from 8,000). Memory management has been doubled to 2 GB, which should increase computational speed for larger worksheets.

There's more help with data manipulation, too. For example, you are no longer limited to three levels of sort - you can now sort by up to 64 levels, and sorts can be performed based on cell color, font color, or icon, in addition to the traditional cell contents. AutoFilter, useful for selecting rows that meet specific criteria, can now display more than 1,000 items in its drop-down list, and you can select multiple items to filter just by clicking on them. If you have duplicate rows, a new Remove Duplicates feature lets you remove rows containing duplicates based on the column(s) you specify.

Better Looking Graphics, Tables
Themes - a collection of colors, fonts, fill effects, and other visual properties - are shared with other Office applications, so a chart you create in Excel and paste into Word will have the same visual properties as other images in Word. Themes are reflected in tables, charts, shapes, SmartArt diagrams, and even PivotTables.

Styles, familiar to Word users, now come to Excel in a big way. Styles are used to format cells, controlling the font, font size, and background. You can also use conditional formatting to apply a special kind of style that defines cell backgrounds and icons. Microsoft calls this visual annotation - it's just another technique to indicate a cell's value with an icon, color, or bar.

With Excel 2007's new user interface you can quickly create, format, and expand an Excel table to organize the data. Table formatting is easier, too. What I really like is how Excel now replaces column headings (A, B, C, etc.) with the header row - so as you scroll through a long table, the column headings are replaced with the column headings from the table's header row. It's a nice alternative to having to freeze a row as you scroll through a table, then unfreeze it when you're done.

Also new in tables are calculated columns, which are similar to array formulas. Add a table, choose a cell in a column, and enter a formula, and the formula is automatically copied to all cells in the same column - no Fill or Copy command needed. In addition, adding a Total Row, then specifying what each column's total should be (sum, average, etc. or your own formula), is incredibly easy to set up.

In Excel 2003 you could apply table formats, such as alternating row colors, but once you added a row, the color patterns were destroyed; you had to reapply the AutoFormat. In Excel 2007, formatting is maintained when you update a table. Add, remove, or move rows, including filtering or hiding rows or columns, and the alternate coloring is - at long last - preserved.

Charts, Graphs, And PivotTables
One of Excel's strengths is its charting ability, and the new layouts bring the charting look into the 21st century. There are subtle changes, such as shadows and bevels, plus new color combinations that finally give your data the professional look they deserve.

While many elements in a graph are easier to control - you can quickly change colors or apply a theme, add 3D effects, insert a legend, and superimpose a trend line - charting is still not as simple as it should be. Some tasks remain mind-numbingly difficult, such as adding a secondary vertical axis. (A wizard or a new chart type would be so much simpler.) The good news is that once you have a chart formatted the way you want, you can save it as a chart template and apply it to other charts.

Graphs are now easier to create and control, and you can see how your changes will look on the fly by hovering your cursor over the formatting command.

PivotTables " that powerful analysis tool that is little understood and thus underused - gets a makeover. The new interface lets you check boxes in addition to dragging and dropping fields within the task pane, making things a bit easier. You can add computations (sums, averages, etc.), sort data, and filter entries more directly, too.

Staying Compatible
A compatibility checker tells you if your workbook contains features that previous versions of Excel won't support. But be careful - you'll need to remember to save a document in Excel 2003 format to maintain compatibility with other users until the new 2007 file format becomes the standard. While Microsoft has released a converter to read 2007 files in earlier versions, don't rely on your colleagues to have it installed.

If you're connected to a SharePoint server, you can save portions of a worksheet or an entire worksheet to the server, and your users can view only or change values at your direction. Likewise, you can save a worksheet file so colleagues can be sure they're updating the most recent version of the file.

Despite all that's new, many things haven't changed. Apart from expanding the size of the formula bar (it grows as your formula grows), there's not much to help you write formulas. The ribbon bar contains icons that segregate formulas (date and time, financial, logical, etc.) but there's no new help to explain the intricacies of some functions.

Even so, it's likely that many of the features you've always wanted but never knew Excel had will finally surface, thanks to the new interface.

Contact Us | Authors | Subject Index | RSS Feeds

Copyright ©2007