Skip the navigation

Excel 2007 Cheat Sheet

By Preston Gralla and Richard Ericson
August 2, 2007 12:00 PM ET
Better sorting and filtering
Sorting options
You can sort by value or the icon used in conditional formatting. Click to view larger image.

Sorting data -- previously limited to three levels -- has been expanded to 64 levels. Best of all, while you can still sort data based on values (to sort a date column chronologically, for instance), you can also sort by font, color or icon used with conditional formatting. Thus, you can display all your green traffic lit cells together, followed by the yellow lights, then the red.

Other visualization tools eliminate the need for complicated macros or formulas. New conditional formatting options let you highlight duplicates, unique values, the top/bottom 10%, values above or below the average, cells less than or greater than a specified value, or cells within a range (highlighting cells containing values between 1 and 10, for example).

Applying filters
The new Filter feature offers check boxes to let you pick which rows you want to see.

If you don't need to see all values, the vastly improved Filter feature puts check boxes (for up to 1,000 values) in a pull-down list, allowing you to easily pick multiple values to display. Likewise, the new Remove Duplicates feature hides rows based on the duplicate values in columns you specify.

Working with PivotTables

Among the notable improvements in Excel are tools to make existing features easier to use. Take PivotTables, for example.

(For the uninitiated, PivotTables allow you to view your data differently -- think "slice and dice." For example, you can summarize sales by agent by month or, with a simple drag-and-drop motion, summarize sales by month and within month by agent.)

In Excel 2007, you still set up PivotTables using a wizard, which is slightly changed from Excel 2003. However, once you have a PivotTable defined, manipulating it is considerably easier.

Instead of dragging and dropping elements within the table itself, you can use the wizard to make choices -- checking boxes to select which fields to display or choose sorting options, for example. Excel 2007 makes it easier to switch columns and rows, filter values, and use or hide field names.

In addition, conditional formatting (those data bars or traffic lights we mentioned) can be applied to cells displayed in PivotTables.

PivotTable panel
PivotTables are easier to use, thanks to a new PivotTable panel. Click to view larger image.
Styles and Themes

One of the promises of the Ribbon interface, according to Microsoft, is that some features are more obvious and usable. That's certainly true of Styles, a formatting tool from previous versions of Excel that is now available using a "gallery" interface introduced in Office 2007.

Styles
Choose a style from the gallery and see it applied to your selection. Click to view larger image.

You can quickly apply a collection of settings, from the font used to the background color and border style to cells, tables and PivotTables. As you mouse over the choices, Excel 2007 applies each style to your selection so you can preview the effect without making the change permanent.



Our Commenting Policies