Skip the navigation

Excel 2007 Cheat Sheet

By Preston Gralla and Richard Ericson
August 2, 2007 12:00 PM ET
5. Use macros.

At first glance, macros -- ingenious shortcuts you can create for performing repetitive tasks -- seem to have been banished from Excel 2007. But they're still there; display the Developer tab, and you'll find them in all their glory. In fact, the Developer toolbar puts the macro tools at easier reach than they were in previous versions of Excel.

macro controls
All your macro controls are in the Code group on the Developer tab.

You'll find everything you want in the Code group on the Developer tab. Record a macro by clicking the Record Macro button, manage your macros by clicking the Macros button and configure security for macros by clicking the Macro Security button.

Any macros you created in previous versions of Excel should work fine in Excel 2007. See Microsoft's Office Online site for more information about working with macros in Excel 2007.

6. Find your old friends.

In Excel 2007, no features or functions are where they used to be. But it's easy to find them. Use our Excel 2007 quick reference charts for an extensive list of where to find your old friends.

New features in Excel 2007

In Excel 2007, more is truly better. Microsoft has increased the number of columns per spreadsheet (and per PivotTable) to 16,384 (up from 256) and the number of rows to 1,048,576 (up from 65,536). Other limits have been also expanded: Text cells can now contain more than 32,000 characters (up from 255).

Chances are you'll never reach other new limits: PivotTables can manipulate more than 16,000 fields (up from an already generous 255), and formulas can now refer to up to 8,000 cells (memory permitting), so it's fortunate that Excel 2007 lets you drag the corner of the formula bar to expand it.

Excel 2007's memory manager can handle 2GB (double the amount in Excel 2003), so calculations execute faster. The new version also takes advantage of dual-core processors and multithreaded chip sets, so if you're lucky enough to be running it on a machine with either feature, expect a noticeable speed boost.

New visualization tools

Charts and graphs now support 16 million colors, and improved color support is evident throughout this version, especially in several new visual tools for highlighting data. For example, in Excel 2007 you can use conditional formatting to set the background color of a cell or use a colored bar (called a data bar) -- the length corresponds to the cell's value.

You can also add icons to cells based on their value, giving your worksheet a dashboard-like quality. For example, assigning traffic-light icons to a range of cells is a snap, and Excel's built-in logic assigns colored circles based on the value of the cell: green for the highest third, yellow for the middle third and red for the bottom third.

Colored bars in charts      Stoplight effect in charts
You can add colored bars to indicate the value in a cell (left) or apply a three-icon set to indicate which third data falls into (right).

Similarly, a four- or five-icon set (such as set of vertical bars similar to what your cell phone uses to indicate signal strength) displays icons based on which quartile or quintile the value falls into.

In all cases, you can control the ranges for each icon in the set -- allowing you, for instance, to use a green traffic light to indicate only the highest 10% of values.

Applying conditional formatting
Conditional formatting rules are easy to adjust. Click to view larger image.


Our Commenting Policies