FIND ICONS ON THE RIBBON

Problem: The new ribbon user interface might be great for people new to Excel, but I knew the old Excel perfectly well. Why did Microsoft put pivot tables on the Insert tab instead of the Data tab, where they belong?

Strategy: You can use one of many available third-party tools to assist with the transition.

At MrExcel.com, I offer a free tip card that maps each item on the Excel 2003 menu to a tab on the Excel 2007 ribbon. If you have a color printer, download the card and print it for free.

Lin Jie was the first to solve the Excel 2007 ribbon confusion. His Classic Excel Menu add-in will add a new menu tab to the Excel 2007 ribbon. This tab replicates the Excel 2003 menu. The add-in sells for $16.95. Versions are also available for Word 2007 and PowerPoint 2007.

The second impressive utility is the Toolbar Toggle add-in. This add-in offers a lite version similar to the Classic Excel Menu add-in and also offers a full-featured utility that brings back the Excel 2003 menu and toolbars, as well as the ability to customize the toolbars. If you were a fan of customizing toolbars in Excel 2003, you will love the functionality of this product.

With both of these add-ins, you can work in the Excel 2003 menu, and then switch over to the Excel 2007 menu when you need to access new features.

Find links to these utilities as well as any others that become available at www.mrexcel.com/excel2007.html.

Summary: You can ease the transition to Excel 2007's ribbon interface by using a third-party solution.

GO WIDE

Problem: I can't find anything on the Excel 2007 ribbon.

Strategy: Invest in a wide-screen monitor. The Office 2007 experience dramatically improves at a 1440x900 resolution.

When you reduce the size of the Excel window, Excel automatically starts consolidating ribbon options into smaller icons and then groups. Figure 5 shows detail of the Home tab of the ribbon at normal size.

If the Excel window gets smaller, more and more icons are grouped into dropdown menus, as shown in Figure 6.

If you open Excel 2007 on a 1440x900 monitor, as shown in Figure 7, you will be able to see more icons, as well as descriptive text for many of the icons. In Figure 7, for example, the Wrap Text icon (which has never existed in previous versions of Excel) is now labeled.

The price of widescreen monitors has dropped in recent years. In the summer of 2007, I found widescreen monitors on sale at the office supply chains for around $159. Purchasing one of these monitors is a worthwhile investment to help make your Office 2007 experience better. Not only does a widescreen monitor make the ribbon easier to work with, but it enables you to see 21 normal-sized worksheet columns.

Additional Details: If you reduce the Excel window down to about four columns wide, Microsoft assumes that you can not possibly be working in something that small, and it hides the ribbon completely. (see Figure 8)

Summary: You can make your Excel window as wide as possible to see the full set of ribbon icons.

MINIMIZE THE RIBBON TO MAKE EXCEL FEEL A BIT MORE LIKE EXCEL 2003

Problem: The ribbon is taking up a lot of real estate at the top of my screen. It distracts me. I spend 99% of my Excel time in the grid, so I don't need to see the ribbon all the time.

Strategy: You can minimize the ribbon, reducing it to a simple line of Home, Insert, Page Layout, Formulas, and so on, as shown in Figure 9.

To minimize the ribbon, you can either press Ctrl+F1 or right-click anywhere on the ribbon and then choose Minimize the Ribbon.

Additional Details: When you either click a ribbon tab with the mouse or use an Excel 2007 shortcut key, the ribbon will temporarily reappear. When you select the command from the ribbon, it will minimize again.

Double-click any ribbon tab to permanently exit minimized mode.

Alternate Strategy: Excel also offers a full screen mode. If you choose View – Full Screen, Excel will hide the ribbon, the ribbon tabs, and the Quick Access toolbar. You can press the Esc key to exit this mode.

Summary: Minimizing the ribbon frees up more space for the grid.

Commands Discussed: Minimize the Ribbon; Ctrl+F1; View – Full Screen

THE OFFICE DEVELOPMENT TEAM LIKES THE ARTIST FORMERLY KNOWN AS PRINCE

Problem: I've searched all the ribbon tabs, but I cannot find a Print icon or a Save icon. What happened to all my File menu favorites?

Strategy: The round circle to the left of the Home tab is what was the File menu in earlier versions of Excel. Many important commands are hidden behind this nameless menu item (see Figure 10).

I had the opportunity to use five different beta versions of Excel 2007. For the first several versions, the round icon was a menu named File. Somewhere along the line, someone at Microsoft decided to replace the word File with an unintelligible symbol.

It wouldn't be so bad if nothing important were behind the symbol. But important tasks such as Save, Print, Close, and Excel Options are in this menu formerly known as File.

I've checked out a few books on Excel 2007. Some are calling this the Orb. Some are calling it File or Start. My rule is to refer to this menu as the Office Icon. In this book, if I say to use Office Icon – Close, I am telling you to use the Close option on the Office Icon menu.

Additional Details: The very first time you open Excel 2007, the Office Icon pulses from white to orange. Did you notice this? This was Microsoft's only indication that there is something important under the icon.

Summary: Important items are under the Office Icon, the menu formerly known as File.

THE PASTE ICON IS REALLY TWO ICONS

Problem: When I click Paste, Excel 2007 does a regular paste instead of offering to paste only values. What's the deal?

Strategy: Many icons on the ribbon have an upper half and a lower half, but you can see the dividing line only if you hover above the icon (see Figure 12).

You can click the upper half of Paste to invoke the Paste command. The lower half leads to a larger menu with various paste options, as shown in Figure 13.

When you need to click the Paste icon, this book says to select Home – Paste. When you need to select something from the Paste dropdown, such as Paste Values, this book tells you to select Home – Paste dropdown – Paste Values.

Additional Details: In addition to Paste, several other icons have an upper (icon) half and a lower (dropdown) half:

• The Insert icon on the Home tab

• The Delete icon on the Home tab

• The Pivot Table icon on the Insert tab

• The AutoSum icon on the Formulas tab

• The Macros icon on the View tab

• The Options icon on the PivotTable Tools Options tab

Ironically, the bottom half of the Options icon leads to a menu where one of the choices is Options. I have to shake my head in Microsoft's direction when I write "Choose Options – Options dropdown – Options."

Additional Details: Excel 2007 introduces a new control called the gallery. The Cell Styles gallery on the Home tab of the ribbon is one example. Many other examples of galleries appear when you are working with charts or SmartArt graphics.

A gallery has a row of thumbnail icons and three arrows along the right edge. The theory is that you can browse the icons one row at a time, using the up and down arrows. However, I almost always tell you to open the gallery. This means to click the bottom arrow to see all the selections.

Gallery controls do not have names, so I use the group name. In Figure 14, for example, the gallery is the only item in the Chart Layouts group. I would say, "Open the Chart Layouts gallery." This means to click the bottom arrow along the right edge of the gallery.

When you click the bottom arrow icon, the entire gallery opens, revealing many more thumbnails (see Figure 15).

Summary: The Excel 2007 interface is made up of many new controls, from the ribbon to detailed dropdowns to complicated dialogs. You can easily navigate this interface when you understand how to read the directions in this book.

USE DIALOG LAUNCHERS TO ACCESS THE EXCEL 2003 DIALOG

Problem: I just want to go back to using the Excel 2003 dialogs.

Strategy: Many groups in the ribbon contain a tiny icons called dialog launchers. You can click an icon to return to the old-style dialogs. Figure 16 shows an example of a dialog launcher.

Additional Details: It is difficult to describe the dialog launcher icon. If you enlarge the icon, you can see that it looks like the top -left corner of a square with an arrow pointing down and to the right. Figure 17 shows the detail of this icon. I am sure there is some artistic rationale why these pixels mean "take me back to the old dialog that I know and love," but I can't figure it out.

Summary: If you want to use the Excel 2003 dialogs in Excel 2007, watch for dialog launcher icons throughout the ribbon groups.

MAKE YOUR MOST-USED ICONS ALWAYS VISIBLE

Problem: With the ribbon, I can only see one-seventh of the icons at any one time. I find that I spend a lot of time on the Data tab, but I annoyingly have to keep switching back to the Home tab. Does Microsoft really think this is better?

Strategy: Microsoft provided the Quick Access toolbar to address this problem. You can add your favorite icons to the Quick Access toolbar and then, because the Quick Access toolbar is always visible, you can invoke your most-used icons without having to switch ribbon tabs so frequently.

The Quick Access toolbar starts out as a small bar with the icons Save, Undo, and Redo. It initially appears above the ribbon, just to the right of the Office Icon, as shown in Figure 18.

If you right-click the Quick Access toolbar, you can choose to move it below the ribbon (see Figure 19). This gets your most used icons closer to the grid and provides room for a few more icons.

When you find yourself using a ribbon icon frequently, you can right-click the icon and choose Add to Quick Access Toolbar, as shown in Figure 20. This is the easiest way to customize the Quick Access toolbar.

Gotcha: Some of the icons look similar to one another when moved to the Quick Access toolbar. For example, both Goal Seek and Scenario Manager use a green crystal ball icon. To figure out which is which, you need to hover over an icon to see its ToolTip (see Figure 21).

Additional Details: You can right-click the Quick Access toolbar and choose Customize Quick Access Toolbar to reach the full -featured dialog shown in Figure 22. The dialog offers a dropdown of categories on the left. Below this dropdown is a list of icons from the category. Here's how you use this dialog:

• You can select an icon on the left and click the Add button to add the icon to the Quick Access toolbar.

• You can select an icon on the right and click the Up or Down buttons to re-sequence the icons on the Quick Access toolbar.

• You can click the Reset button near the bottom to undo all your customizations and restore the Quick Access toolbar to the initial three buttons.

• You can use the top-right dropdown to say that certain icons should be assigned to the current workbook. Most Quick Access toolbar icons apply to every workbook. However, you can have 10 icons for every workbook and then add 3 additional icons for each specific workbook. The 10 global icons appear first, followed by the 3 local icons.

• You can organize your icons into logical groups and then add a separator between groups. To do this, you click the item at the top of the left list and then click Add to add a vertical line between icons.

• You should pay particular attention to the category Commands Not in the Ribbon. If one of your favorite Excel 2003 or earlier commands is in this category, Microsoft completely left it out of the ribbon. The only way to access the command is by adding it to the Quick Access toolbar.

Summary: You can customize the Quick Access toolbar to have your favorite icons always available.

Commands Discussed: Customize the Quick Access Toolbar

THE ALT KEYSTROKES STILL WORK IN 2007 (IF YOU TYPE THEM SLOWLY ENOUGH)

Problem: I can't find anything on the Excel 2007 ribbon. I used to use a lot of keyboard shortcuts. For example, I often used Alt+E+I+J to invoke Edit – Fill – Justify. Microsoft completely eliminated the Edit menu, so what shortcuts do I use now?

Strategy: Your old keystrokes still work; you just have to invoke them a bit more slowly than usual. In Excel 2003, the top-level menus are File, Edit, View, Insert, Format, Tools, Data, Window, and Help, and one letter of each menu item is underlined, indicating the shortcut key to use with Alt. If you press Alt+E, you will open the Edit menu. If you press Alt+I, you will open the Insert menu.

Figure 23 shows the old Insert menu, where you can see that many of the menu items have underlined letters. You can choose a menu item by continuing pressing its underlined letter. For example, you could press Alt+I+E to insert cells. You could press Alt+I+R to insert rows. You could press Alt+I+C to insert columns. You could press Alt+I+N+D to perform Insert – Name – Define.

Summary: You can customize the Quick Access toolbar to have your favorite icons always available.

Commands Discussed: Customize the Quick Access Toolbar

THE ALT KEYSTROKES STILL WORK IN 2007 (IF YOU TYPE THEM SLOWLY ENOUGH)

Problem: I can't find anything on the Excel 2007 ribbon. I used to use a lot of keyboard shortcuts. For example, I often used Alt+E+I+J to invoke Edit – Fill – Justify. Microsoft completely eliminated the Edit menu, so what shortcuts do I use now?

Strategy: Your old keystrokes still work; you just have to invoke them a bit more slowly than usual. In Excel 2003, the top-level menus are File, Edit, View, Insert, Format, Tools, Data, Window, and Help, and one letter of each menu item is underlined, indicating the shortcut key to use with Alt. If you press Alt+E, you will open the Edit menu. If you press Alt+I, you will open the Insert menu.

Figure 23 shows the old Insert menu, where you can see that many of the menu items have underlined letters. You can choose a menu item by continuing pressing its underlined letter. For example, you could press Alt+I+E to insert cells. You could press Alt+I+R to insert rows. You could press Alt+I+C to insert columns. You could press Alt+I+N+D to perform Insert – Name – Define.

Many people who use Excel regularly memorize a few of these keyboard shortcuts. My favorites, for example, are Alt+E+S+V for Edit – Paste Special – Values, Alt+O+C+A for Format – Column – AutoFit Selection, and Alt+E+I+J for Edit – Fill – Justify.

In Excel 2007, any Excel 2003 keyboard shortcuts you memorized between the Edit and Window menus continue to work. A few of the keyboard shortcuts from the File menu still work, but others do not.

To use an Excel 2003 shortcut, you press Alt and the first letter rapidly. If you press Alt and E, V, I, O, T, D, or W, Excel will display a ToolTip above the ribbon that says Office 2003 Access Key. At this point, you can continue typing the rest of the Excel 2003 menu shortcut. In Figure 24, the ToolTip shows that Alt+E+I has been typed, which is two-thirds of the shortcut to reach Edit – Fill – Justify.

When you type the final bit of the shortcut, Excel closes the ToolTip and performs the command.

Gotcha: Excel doesn't provide any feedback about what command you are typing. In Excel 2003, you could look at the Data menu to learn what to do after typing Alt+D, but Excel 2007 doesn't offer this feature.

Gotcha: It takes Excel a fraction of a second to display the ToolTip. I find that I have to pause briefly after typing Alt plus the first keystroke. For example, if I rapidly type Alt+O+C+A to invoke Format – Column – AutoFit Selection, about half the time, Excel thinks that I typed Format – AutoFormat. It seems that while Excel is busy displaying the ToolTip, the fact that I typed C doesn't make it into the keyboard buffer. If you slow down slightly, the Excel 2003 menu keys will work more reliably. (It's ironic that we have to work more slowly in Excel 2007, isn't it?)

Gotcha: The old keyboard shortcut Alt+H to open Help does not work in Excel 2007. Microsoft decided that Alt+H would open the Home tab in all its products, so people who used to use the menu shortcuts for Help are sunk. (Although ... there wasn't that much helpful on the old Help menu. I can't imagine anyone memorizing Alt+HA to open the Help – About dialog.) The F1 keystroke still invokes help.

Gotcha: Only some of the keystrokes from the old File menu continue to work. Alt+F opens the Office Icon, where you are supposed to use the Excel 2007 shortcut keys. The big three continue to work: Alt+F+O is File – Open, Alt+F+N is File – New. Alt+F+C is File – Close. However, beyond that, you will find differences. In Excel 2003, using Alt+F+W would save a workspace. In Excel 2007, the same keystrokes take you to the Print fly-out menu. Go figure.

Additional Details: In addition to the Alt key shortcuts, the Ctrl key combinations from previous versions of Excel still work: Ctrl+B is Bold, Ctrl+I is Italic, Ctrl+U is Underline, Ctrl+C is Copy, Ctrl+X is Cut, Ctrl+V is Paste.

In addition, any keystrokes that you use while working in the grid continue to work. Ctrl+Down Arrow moves to the last row in the current region. Ctrl+* selects the current region, the End+Right Arrow moves to the last column in a contiguous range.

The Function keys continue to work as well. F2 edits the current cell. F4 repeats the last command or adds dollar signs to the last reference when you're entering a formula. F11 continues to create a chart in one click, and the new Alt+F1 will create the same chart as an embedded object.