Introducing Excel 2007

By David H. Ringstrom, CPA

In your hands, Microsoft Excel is like a carpenter’s well-worn hammer. It’s a tool that’s served you well over the years, but now it’s time to trade it in. Excel 2007 is here, and I think that you’ll be thrilled — after you get over an initial shock.

 

The ribbon? What’s next? Bows?

The most dramatic change you’ll see when you first launch Excel 2007 is that the traditional menu structure is no more. The new Excel interface is known as the ribbon, and it’s initially shocking and disruptive. Don’t scoff and click away from me yet though — keep an open mind. Think of the ribbon as a toolbar on steroids. Just about any task in Excel is now just one or two clicks away, and accompanied by new onscreen documentation. The ribbon is designed to make Excel features more accessible, effective, and efficient. Previously many of Excel’s best features were buried in menus and submenus, so 90 percent of users used 10 percent of its capability, or less. After just a few minutes with the ribbon I think you’ll start to appreciate this new approach.

 

The ribbon has several primary categories:

• Home – As shown in Figure 1, the ribbon weaves the most commonly used features together. Most of what you use on the Standard and Formatting toolbars in earlier versions of Excel are here.

 

Figure 1: Welcome to the future!

 

• Insert – This section simplifies adding pivot tables, charts, drawings, WordArt, and other objects to your spreadsheets.
• Page Layout – Features normally buried under the File, Page Setup or Tools, Options commands are now readily available. 
• Formulas – As shown in Figure 2, highly useful functions that rarely saw the light of day, such as NETWORKDAYS and the new IFERROR are now just a click away.

 

Figure 2: Excel 2007 makes it easy to try new functions with tightly integrated help.

 

Expert tip: The NETWORKDAYS enables you to determine how many workdays are in a date range. For instance, if cell A1 contains 1/1/2007 and cell A2 contains 12/31/2007, the formula =NETWORKDAYS(A1,A2) returns 261, meaning there are 261 work days in 2007. You can optionally include a holiday range as a third argument, which enables you to further refine the day count. In Excel 2003 and prior you can only use this function if you enable the Analysis ToolPak by following these steps:

 

1. Choose Tools and then Add-ins.
2. Select Analysis ToolPak and then click OK.

 

New function: The new IFERROR function takes the form =IFERROR(value,value_if_error). Prior versions of Excel require more complex formulas such as =IF(ISERROR(value),value,value_if_error). The new IFERROR means that you can trap errors without having to duplicate half of your formula within itself.

 

• Data – This section of the ribbon is all about analysis. Sorting, filtering, grouping, linking to Microsoft Access or the Web — it’s all just a click away.
• Review – Routine tasks, such as protecting a worksheet or workbook, adding comments, and infrequent tasks such as foreign language transaction, are also available in one click.

 

Protected Status: If a worksheet is protected, the Protect Sheet button becomes Unprotect Sheet. The dynamic nature of this menu choice within the ribbon enables you to determine at a glance whether a spreadsheet is protected.

 

• View – Previewing, freezing panes, hiding worksheets, and control of screen features such as the worksheet frame are all here.
• Chart Tools – This section appears when needed and offers Design, Layout, and Format ribbons to streamline working with charts.

 

It’s not all different: If a one-click option on the toolbar doesn’t meet your needs, many of the settings dialog boxes that you’re presently comfortable with are still readily available. For instance, the choices Normal, Wide, and Narrow appear when you click the Margins button on the Page Layout ribbon. If these don’t meet your needs, click Custom Margins and you’ll be back on familiar ground.

 

Can Your Excel Version Do This?

 

Roll up your sleeves and get ready to spread your wings: Excel 2007 offers 16,384 columns and 1,048,576 rows. Conversely, Excel 97 through 2003 provide a meager 256 columns and 65,536 rows. Instead of stopping at column IV, you can now keep on scrolling through column XFD.

 

Wide open space: The last cell in a worksheet is now XFD1048576, instead of IV65536.

 

Since you can crunch many more numbers than before, Excel 2007 uses as much RAM as you can stuff in your computer. Previous versions of Excel can only use up to 1 GB of memory, so adding more RAM wouldn’t speed up your spreadsheets. When you order your next computer, be sure to include 2 GB of RAM, or even more if you work with particularly large spreadsheets.

 

There’s much more that you’ll like about Excel 2007:

 

• Sorting: You can sort on up to 64 columns (versus 3 columns in Excel 2003 and prior), and as well as by color or icon. Icons are a new conditional formatting feature that provides yet another way to identify sets of data.
• Filtering: The AutoFilter feature enables you filter up to 64 columns of data (up from 3 columns). Further, you can filter by cell value, font color, cell color, or icon.
• Conditional Formatting: Many users haven’t uncovered the Conditional Formatting feature on the Format menu in previous versions of Excel. Those that have are often frustrated by having only three levels of formatting. Can you guess how many levels you have in Excel 2007? Hint: the same as when you sort and filter.
• Remove Duplicates: A new Remove Duplicates button enables you to eliminate duplicate values from a list in just two clicks. Conversely, users of Excel 2003 and prior have to know about the Advanced Filter feature hidden under Data and then Filter to accomplish the same task.
• Dynamic Worksheet Frame: If you’re working in a table of data and scroll down one screen, the worksheet frame automatically adjusts to show the headings in the first row of your table. As a result you can skip choosing Window, then Freeze Panes so that you can keep track of which column is which.
• Status bar stats: Many users overlook the Sum feature on the status bar in Excel. This feature automatically sums any cells that you highlight on your worksheet. Those who do know about the onscreen Sum often don’t realize that right-clicking that portion of the screen enables them to choose from Min, Max, Averge, Count, and other functions. In Excel 2007 no right-clicking is necessary: all of these functions are displayed all the time across the bottom of your Excel screen.
• Expanded IF functions: Previous versions of Excel only allowed up to seven levels of nested IF statements, while Excel 2007 enables up to 64.

 

Gnarly: If you’re approaching even seven levels of nested IF functions, there’s most likely a better way accomplish your goal. I recommend keeping a copy of Microsoft Office Excel 2007 Inside Out by Mark Dodge and Craig Stinson within an arm’s reach at all times.

 

• XML and PDF formats: You can save Excel 2007 (and Office 2007 documents in general) in a new XML-based format, or you can opt for the traditional Excel 97-2003 format. In addition, you can save spreadsheets directly as PDF files, which eliminates the need to purchase Adobe Acrobat or a third-party PDF software.
• Enhanced right-click menus: Right-click on a worksheet cell in Excel 2003 and prior and you get a few useful commands. Conversely Excel 2007 puts dozens of commands at your fingertips, as shown in Figure 3.

 

Figure 3: Dozens of commands are just a right-click away.

 

• Color your world: No longer will you be confined to 56 colors in your spreadsheet: your spectrum has expanded to 4.3 billion colors. A new Color Scales feature automatically assigns colors based on cell value to group high, middle, and low values. The new Data Bars feature, shown in Figure 4, gives you a visual representation of the underlying numbers. Both Color Scales and Data Bars are available under the Conditional Formatting icon on the Home ribbon.

 

Figure 4: Data bars offer a visual sense of scale.

 

Try before you apply: Some of the conditional formatting choices, such as Color Scales, Data Bars, and Icon Sets enable you to preview their effect before you formally apply the format. Eliminate conditional formatting by choosing Clear Rules after clicking the Conditional Formatting button.

 

• Other improvements: Earlier versions of Excel allow up to 32,000 characters in a worksheet cell, but display only the first 1,000. AutoFilter lists would show only 1,000 items, and formulas had a limit of 1,024 characters. Excel 2007 provides vast improvements on all of these counts: all 32,000 characters will display, up to 10,000 AutoFilter items are shown, and formulas can have up to 8,192 characters.

 

Ready to Rumble?

 

You can test drive Excel 2007, and other Office 2007 applications, free online, right now! Visit www.microsoft.com/excel  and click the Take an online test drive link. After a five step process to install a browser plug-in and enter your Windows Live login you’ll enter a fully functional version of Excel 2007 in Internet Explorer, as shown in Figure 5. The only limitation is that you can’t upload or download spreadsheets into this test environment. Alternatively, the Download a Trial Version enables you to download 60- day trial versions of every Office 2007 product, including the new Office Groove 2007 — a collaboration tool that enables your team to keep the latest copies of documents and data in synch automatically.

 

Figure 5: Explore Excel 2007 from the comfort of your web browser.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.