Anneliese Wirth: Surviving the switch to Excel 2007

 Anneliese Wirth is a Senior Writer in the Office User Assistance group at Microsoft. She currently writes about InfoPath 2007 and Excel 2007 for Office Online. In her spare time, Anneliese enjoys a healthy mix of high and low culture. She also enjoys talking about unicorns, ponies, and rainbows with her four-year-old daughter and debating about who exactly is the boss of whom.

It takes time to get used to a new version of a product, particularly one with a revamped interface. Microsoft employees aren't immune to the productivity hit when called to upgrade. Anneliese Wirth aims to lessen that hit for you by offering tips for surviving the upgrade to Excel 2007.

Is upgrading to Excel 2007 a productivity hit you could do without? Yeah, I felt that way, too (although as a card-carrying member of the Office team, I'm probably not supposed to say so). But hey, I lived through the upgrade and actually became more productive as a result. And I want you to do the same.

This column contains a few tips for getting started with Excel 2007. It's particularly suited for those who are converting from Excel 2003, but a lot of it is just as relevant if you're switching from an earlier version. I found these tips by reviewing the comments you left about various Excel 2007 Help topics. (Yes, we really do read and appreciate your comments, even the ones where you yell at us in ALL CAPS. Heh.)

Tip 1: Add a Get Started tab to the Ribbon

In Excel 2007, toolbars and menus no longer appear along the top of the window. Instead, you'll see the Ribbon, which is part of Microsoft Office Fluent user interface. When you first start using Excel 2007, you'll probably have questions about where to find Excel 2003 commands and toolbar buttons. To help answer those questions, I recommend that you download a free add-in that adds a Get Started tab to the Ribbon.

Get Started tab

You can use the commands on this tab to get to training courses, video demos, newsgroups, and other content designed to help you learn Excel 2007. The most useful tool for those who are new to the Ribbon is the Interactive Guide (the button on the far left), which shows you exactly where to find Excel 2003 buttons and commands in Excel 2007. Trust me, it's a lifesaver.

You can download the Get Started Tab for Excel 2007 from the Microsoft Download Center. If you don't want to add the Get Started tab to Excel 2007, or if your company prohibits you from downloading the add-in, you can access the Excel 2003 to Excel 2007 command reference guide directly.

Top of Page Top of Page

Tip 2: Take advantage of the Quick Access Toolbar

Some of your favorite commands may not appear on the Ribbon by default. Although you can't add commands to the Home, Insert, or other Ribbon tabs, you can customize the Quick Access Toolbar, which appears by default above the Ribbon.

Quick Access Toolbar

To add a new command to the Quick Access Toolbar, click the arrow next to the Quick Access Toolbar, and then click More Commands.

Shortcut menu for Quick Access Toolbar

At this point, the Excel Options dialog box appears. In the Choose commands from box, click Commands Not in the Ribbon to view the commands that didn't make it onto the Ribbon.

Here are the commands I added to my Quick Access Toolbar.


The Print Preview button

Print Preview still exists in Excel 2007, of course, but it isn't as visible as it was in Excel 2003. To bring the Print Preview button back to the forefront, I added it to my Quick Access Toolbar. You can do that, too, or you can simply memorize the keyboard shortcut for Print Preview, which is CTRL+F2.

The Form button

Many of you are frustrated by the fact that the Form button, which is the entry point to the data form feature, is not on the Ribbon. To refresh your memory, a data form is a dialog box in which you can enter or view one complete row of information in a range or table. It looks something like this:

Example of data form

If you use data forms a lot, then by all means, add the Form button to the Quick Access Toolbar.

The Document Location box

Oh, how I love the Document Location box, which displays the path and file name for my workbook. I can't tell you how often I end up copying the information in this box and sending it to colleagues.

The Document Location box on the Quick Access Toolbar

Okay, so this box never appeared on the toolbars by default in Excel 2003 either. No matter. You should add it if you want to see or copy the full path and file name for your workbook.

The Send to Mail Recipient command

From reading your comments, it's apparent that you miss the Send to Mail Recipient command as much as I do. This command lets you quickly send a selection of cells in a workbook in the body of an e-mail message.

I should point out here that you can add any command you want to the Quick Access Toolbar, even ones that are already on the Ribbon. For example, if you want the Filter command to be visible at all times, open the Data tab and right-click the Filter button. When the shortcut menu appears, click Add to Quick Access Toolbar.

Top of Page Top of Page

Tip 3: Minimize the Ribbon, maximize your workspace

This one is short and sweet. If you want more room to work, press CTRL+F1 to go from this:

Ribbon, before minimizing

To this:

Ribbon, after minimizing

Press CTRL+F1 again to maximize the Ribbon.

Top of Page Top of Page

Tip 4: Choose your own default font

In Excel 2003, the default font was trusty Times New Roman. In Excel 2007, the defaults have changed. The font that appears in cells, headers or footers, and text boxes is known in Excel 2007 as the body font.

When you install Excel 2007, the body font for all new workbooks is 11-point Calibri.

Calibri font in Excel 2007

So what's a body font? Well, when you apply a new theme to your workbook, all text that is tagged with the body font changes automatically, without you having to do anything extra. For example, if you change the theme for your workbook from Office to Apex, all text changes from Calibri to Book Antiqua.

If you want, you can use a different font instead of the body font. So, for example, you can choose to use 10-point Arial for all workbooks instead of the default body font. Know that if you do this, however, you lose the ability to easily swap fonts wholesale when you change the theme for a workbook, since themes rely on text being tagged with the "body font" designator. In other words, if you specify Arial as your default font for all new workbooks, that font won't change automatically in response to you applying a theme. If you're okay with that, go ahead and change the default font to whatever works for you.

To change the default font, click the Microsoft Office Button Office button image, and then click Excel Options. In the Popular category, look for the following settings:

Font settings in Excel Options dialog box

In the Use this font and Font size boxes, click the font and font size that you want to use. To begin using the new default font, you need to restart Excel.

Top of Page Top of Page

Tip 5: Restore missing worksheet tabs

I've seen many, many comments indicating that the tabs at the bottom of your worksheets are disappearing into thin air:

  • “Please tell me how to hide/unhide the sheet tabs! Please I'm begging!” “There are no worksheets hidden, but I can't see them, don't know why. There should be 5 of them there, I can't see the tabs, only the first sheet is shown but also without sheet tab. HELLLLP!!!”

This has happened to me before, and it's annoying. The problem generally occurs when you've used the Restore Window button to unhook your workbook window from the Excel window, and then sized the workbook window in such a way that the tabs disappear behind the status bar.

To recover your worksheet tabs, double-click the title bar of your workbook. This action maximizes the window and returns the tabs to their proper spot on the screen.

Title bar in worksheet window

If this doesn't do the trick, see Where are my worksheet tabs?, which contains a list of additional things you can try. If your tabs still don't appear after all that, please let me know by responding to the Was this information helpful? question at the bottom of this column.

Top of Page Top of Page

Tip 6: Freeze panes here, there, and everywhere

When you freeze panes in your workbook, you “lock” specific columns, rows, or both so that they remain visible as you scroll around in your workbook. If you work with large amounts of data, you no doubt recognize the value of this feature. However, judging by your comments, you think we've removed the ability to freeze anything but the top row or left column:

  • “I want to freeze the second row in a worksheet that has my titles in it. However, it will only freeze the top row. How can I freeze the second row?”
  • “Why the heck did you change the functionality of Freeze Pane? I want to freeze my first column and my top row."

Don't panic. We haven't removed this functionality! It's all still there on the View tab, in the Window group.

Freeze Panes options

To freeze a specific range of rows and columns, select a cell immediately below and to the right of the rows and columns that you want to freeze. Then, on the View tab, in the Windows group, click Freeze Panes to open the menu. Click Freeze Panes, and everything above and to the left of the cell you selected will stay in place when you scroll in the worksheet.

To freeze more than just the top row, click the row immediately below the last row you want to freeze. For example, to freeze rows 1, 2, and 3, click row 4, and then click Freeze Panes on the menu.

To freeze more than just the first column, click the column immediately to the right of the last column you want to freeze. For example, to freeze columns A, B, and C, click column D, and then click Freeze Panes on the menu.