Tips For Finding Commands on the Excel 2010 Ribbon

You've just upgraded from Excel 2003 to Excel 2010 and your Accounting Department can't find anything. Send them a link to these tips.

As an Excel pro, I could use the old Excel 2003 menus with my eyes closed. I knew where commands were located. I knew the Alt shortcut keys for the commands that I used frequently. Alt+E+S+V was Edit, Paste Special, Values. Alt+E+I+J was Edit, Fill, Justify. Alt+D+L was Data, Validation. Your accounting department is full of people who use Excel a minimum of 40 hours a week. Those power users could make Excel 2003 sing and they will be horribly frustrated when they switch over to the new ribbon in Excel 2010. These tips will help them get up the learning curve.

First, everything below the Formula Bar is the same

When you are working in the Excel grid, all of the navigation and formula entry keystrokes are identical to the old Excel that you knew and loved. The only problem happens when you need to reach up to use a command from the menu or toolbars.

Look on the Home Tab first

The Home tab was engineered to have 80% of the commands that you will need in an Excel session. Everything from the old Standard and Formatting Toolbars is here. When you are hunting for a command, start at the Home tab. If you are a power Exceller, you would probably rather have the Pivot Table, Filter, and Subtotal commands out on the right side of the Home tab instead of Conditional Formatting and Cell Styles. You can now use the Excel 2010 Customize Ribbon dialog to build your own tab that marries the left side of the Home tab with the right side of the Data tab.

The old CTRL key shortcuts haven't changed

Ctrl+B for bold. Ctrl+I for italics. Ctrl+P to Print. Ctrl+S to Save. Ctrl+5 for Strikethrough. All of those are the same.

The old ALT key shortcuts still work! (almost)

In Excel 2003, Alt+I+R would invoke the Insert Rows command. It still does this in Excel 2010, even though the Insert Rows command is no longer on the Insert tab. 95% of the Alt key shortcuts continue to work. A few on the File menu and a few on the Help menu are different now. But here is the annoying part about the Alt shortcut keys: when you press Alt+I to access the old Insert menu, Excel pauses for a fraction of a second while the Office Access Key box is displayed. For whatever reason, the keystrokes that you perform during this short delay are lost! This is hugely annoying. I would frequently type Alt+O+C+A for Format, Column, AutoFit. For some reason, Excel 2007 and Excel 2010 don't catch that I've typed the C and instead, Excel thinks that I wanted to do Alt+O+A which is Format, AutoFormat. So, to avoid that annoyance, just slow down a tiny bit between the first letter and the second letter of the command sequence. As an aside, this was fixed in the 2010 beta, then broken in the final version :-(

Some Commands Just Aren't Where they Belong

There are a few commands that are clearly out of place. Learn these few and life will be easier:

* Pivot Tables are on the Insert tab instead of the Data tab

* All of the good stuff from the old Insert menu is now on the right side of the Home tab instead of on the Insert tab. If you need to insert rows, columns, or worksheets, head to Home instead of Insert.

* Commands on the old Format menu are on the right side of the Home tab.

* Help is now the blue circle and question mark above the right side of the ribbon

Many of the old Dialog Boxes are still there, if you know the secret

Each ribbon tab is divided into rectangular groups of icons. For example, the Home tab starts with groups for Clipboard, Font, Alignment, and Number. In the lower right corner of each group is a tiny symbol. If you look closely, it is the top left corner of a square with an arrow pointing down and to the right. While Microsoft calls these "dialog launchers", I call them "Please Just Take Me Back to the Old Way". Click that symbol and the old Excel 2003 dialog box appears.

If you still can't find the command

Try one of these methods:

* Open Excel 2003, look at the command sequence to see which letters are underlined. Come back to Excel 2010, press Alt and those letters.

* Use the Microsoft interactive tool located at http://office.microsoft.com/en-us/outlook-help/learn-where-menu-and-toolbar-commands-are-in-office-2010-and-related-products-HA101794130.aspx

* Download my full color menu-to-ribbon map at http://www.mrexcel.com/excel2007tipcard.html

If none of those work, try this: Right-click the ribbon and choose Customize Quick Access Toolbar. Change the left dropdown list from "Popular Commands" to "All Commands". You now have an alphabetical list of 1200 commands in Excel 2010. Because it is alphabetical, you have some chance of finding the desired command in the list. When you find it, hover the mouse over the command. Excel will pop up a tooltip identifying the Ribbon tab and group where the command can be found. Sometimes, the tooltip will indicate that the command is part of the dreaded "Commands Not In the Ribbon" group, which means you will have to add the command to the Quick Access Toolbar.

Once you get beyond hating the Ribbon, try these

There are a few new command sequences that you will love in Excel 2010. Do you frequently need to convert formulas to values? Instead of using Paste Special, Values, OK, you can now achieve this in a few keystrokes on most keyboards: Type Ctrl+c to copy Press the "Application" key and then V (The application key is the key with a mouse pointing to a menu. It is usually located between the Alt and Ctrl keys to the right of the spacebar.)

If you have 150,000 rows of data and you've just entered a new formula in cell P2: Copy that formula down to all rows in the dataset by using these steps: 1) Select cell P2 2) Double-click the Fill Handle in the lower right corner of cell P2. It used to be that this trick would be fooled by a rogue blank cell in the adjacent column. Starting in Excel 2010, the algorithm is vastly improved and will usually shoot the formula down to the bottom of the data set.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Related:

Copyright © 2010 IDG Communications, Inc.

IT Salary Survey: The results are in