O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The Modern Face of Excel

Although Microsoft is reluctant to admit it, most of Excel's core features were completed nearly 10 years ago. So what has Microsoft been doing ever since? The answer, at least in part, is spending millions of dollars on usability tests, which are aimed at figuring out how easy—or difficult—a program is to use. In a typical usability test, Microsoft gathers a group of spreadsheet novices, watches them fumble around with the latest version of Excel, and then tweaks the program to make it more intuitive.

After producing Excel 2003, Microsoft finally decided that minor tune-ups couldn't fix Excel's overly complex, button-heavy toolbars. So they decided to undertake a radical redesign to create a user interface that actually makes sense. The centerpiece of this redesign is the super-toolbar called the ribbon.

The Ribbon

Everything you'll ever want to do in Excel—from picking a fancy background color to pulling information out of a database—is packed into the ribbon. To accommodate all these buttons without becoming an over-stuffed turkey, the ribbon uses tabs. Excel starts out with seven tabs in the ribbon. When you click one of these tabs, you see a whole new collection of buttons (Figure 3).

Note

Wondering what each tab holds? You'll take a tab tour in Chapter 1 on A Tour of the Excel Window.

The ribbon is the best thing to hit the Excel scene in years. The ribbon makes it easier to find features and remember where they are, because each feature is grouped into a logically related tab. Even better, once you find the button you need, you can often find other, associated commands by looking at the section where the button is placed. In other words, the ribbon isn't just a convenient tool—it's also a great way to explore Excel.

When you launch Excel, you start at the Home tab. But here's what happens when you click the Page Layout tab. Now, you have a slew of options for tasks like adjusting paper size and making a decent printout. The buttons in a tab are grouped into smaller boxes for clearer organization.

Figure 3. When you launch Excel, you start at the Home tab. But here's what happens when you click the Page Layout tab. Now, you have a slew of options for tasks like adjusting paper size and making a decent printout. The buttons in a tab are grouped into smaller boxes for clearer organization.

The ribbon is full of craftsman-like detail. For example, when you hover over a button, you don't see a paltry two- or three-word description in a yellow box. Instead, you see a friendly pop-up box with a complete mini-description and a shortcut that lets you trigger this command from the keyboard. Another nice detail is the way you can jump through the tabs at high velocity by positioning the mouse pointer over the ribbon and rolling the scroll wheel (if your mouse has a scroll wheel). And you're sure to notice the way the ribbon rearranges itself when you change the size of the Excel window (see Figure 4).

Using the Ribbon with the Keyboard

If you're an unredeemed keyboard lover, you'll be happy to hear that you can trigger ribbon commands with the keyboard. The trick is using keyboard accelerators, a series of keystrokes that starts with the Alt key (the same key you used to use to get to a menu). When using a keyboard accelerator, you don't hold down all the keys at the same time. (As you'll soon see, some of these keystrokes contain so many letters that you'd be playing Finger Twister if you tried holding them all down simultaneously.) Instead, you hit the keys one after the other.

Top: A large Excel window gives you plenty of room to play. The ribbon uses the space effectively, making the most important buttons bigger.Bottom: When you shrink the Excel window, the ribbon rearranges its buttons and makes some smaller (by shrinking the button's icon or leaving out the title). Shrink small enough, and you might run out of space for a section altogether. In that case, you get a single button (like the Number, Styles, and Cells sections in this example) for an entire section. Click this button and the missing commands appear in a drop-down panel.

Figure 4. Top: A large Excel window gives you plenty of room to play. The ribbon uses the space effectively, making the most important buttons bigger. Bottom: When you shrink the Excel window, the ribbon rearranges its buttons and makes some smaller (by shrinking the button's icon or leaving out the title). Shrink small enough, and you might run out of space for a section altogether. In that case, you get a single button (like the Number, Styles, and Cells sections in this example) for an entire section. Click this button and the missing commands appear in a drop-down panel.

The trick to using keyboard accelerators is to understand that once you hit the Alt key, there are two things you do, in this order:

  1. Pick the ribbon tab you want.

  2. Choose a command in that tab.

Before you can trigger a specific command, you must select the correct tab (even if it's already displayed). Every accelerator requires at least two key presses after you hit the Alt key. You need even more if you need to dig through a submenu.

By now, this whole process probably seems hopelessly impractical. Are you really expected to memorize dozens of different accelerator key combinations?

Fortunately, Excel is ready to help you out with a feature called KeyTips. Here's how it works. Once you press the Alt key, letters magically appear over every tab in the ribbon. Once you hit a key to pick a tab, letters appear over every button in that tab (Figure 5). You can then press the corresponding key to trigger the command (Figure 6).

When you press Alt, Excel helps you out with KeyTips next to every tab, over the File menu, and over the buttons in the Quick Access toolbar. If you follow up with M (for the Formulas tab), you'll see letters next to every command in that tab, as shown in Figure I-6.

Figure 5. When you press Alt, Excel helps you out with KeyTips next to every tab, over the File menu, and over the buttons in the Quick Access toolbar. If you follow up with M (for the Formulas tab), you'll see letters next to every command in that tab, as shown in Figure I-6.

You can now follow up with F to trigger the Insert Function button, U to get to the AutoSum feature, and so on. Don't bother trying to match letters with tab or button names—there are so many features packed into the ribbon that in many cases the letters don't mean anything at all.

Figure 6. You can now follow up with F to trigger the Insert Function button, U to get to the AutoSum feature, and so on. Don't bother trying to match letters with tab or button names—there are so many features packed into the ribbon that in many cases the letters don't mean anything at all.

In some cases, a command might have two letters, in which case you need to press both keys, one after the other. (For example, the Find & Select button on the Home tab has the letters FD. To trigger it, press Alt, then H, then F, and then D.)

Tip

You can back out of KeyTips mode without triggering a command at any time by pressing the Alt key again.

There are other shortcut keys that don't use the ribbon. These are key combinations that start with the Ctrl key. For example, Ctrl+C copies highlighted text and Ctrl+S saves your work. Usually, you find out about a shortcut key by hovering over a command with the mouse. For example, hover over the Paste button in the ribbon's Home tab, and you see a tooltip that tells you its timesaving shortcut key is Ctrl+V. And if you've worked with a previous version of Excel, you'll find that Excel 2010 keeps all of the same shortcut keys.

By pressing Alt+E, you've triggered the "imaginary" Edit menu from Excel 2003 and earlier versions. You can't actually see it (because in Excel 2010 this menu doesn't exist). However, the tooltip lets you know that Excel is paying attention. You can now complete your action by pressing the next key for the menu command you're nostalgic for.

Figure 7. By pressing Alt+E, you've triggered the "imaginary" Edit menu from Excel 2003 and earlier versions. You can't actually see it (because in Excel 2010 this menu doesn't exist). However, the tooltip lets you know that Excel is paying attention. You can now complete your action by pressing the next key for the menu command you're nostalgic for.

Backstage View

Excel 2010 doesn't introduce anything earth-shattering as the ribbon. However, it does have another not-so-small change to the way the program operates. Instead of sending you to an ordinary menu to open files, create them, and print your work, it devotes the entire window to these tasks—once you switch into a mode called backstage view.

To switch to backstage view, click the File button that appears just to the left of the Home tab in the ribbon. (The name of this button is a nod to Excel 2003 and other older, more traditional Windows programs, which group many of these tasks together in a menu named File.) To get out of backstage view, just click File again or press the Esc key.

Backstage view is split into two parts. On the left is a narrow strip of different commands. You click one of these to show a page for a different task. Depending on what you click, Excel may show additional options and information on the right, as shown in Figure 8.

To create a new Excel workbook, start by clicking the File tab (1) and then the New command on the left (2). Excel uses its big backstage view to show you a slew of options. For a no-fuss blank workbook, just leave "Blank workbook" selected and click the Create button on the right (3). Or, if you want to get a head start with a premade template, choose one of the many other options underneath. (Chapter 16 tackles templates in detail.)

Figure 8. To create a new Excel workbook, start by clicking the File tab (1) and then the New command on the left (2). Excel uses its big backstage view to show you a slew of options. For a no-fuss blank workbook, just leave "Blank workbook" selected and click the Create button on the right (3). Or, if you want to get a head start with a premade template, choose one of the many other options underneath. (Chapter 16 tackles templates in detail.)

Here are some of the things you'll do in Excel's backstage view:

  • Work with files (creating, opening, closing, and saving them). You'll do plenty of this in Chapter 1.

  • Print your work (Chapter 7) and send it off to other people by email (Chapter 25).

  • Prepare a workbook to be shared with other people. For example, you can check its compatibility with old versions of Excel (Chapter 1) and use document protection to prevent other people from changing your numbers (Chapter 24).

  • Configure how Excel behaves. Once you're in backstage view, just click the Options command to get to the Excel Options dialog box, an all-in-one place for configuring Excel (Excel Options).

The Quick Access Toolbar

Keen eyes will have noticed the tiny bit of screen real estate on the right side of the Office button, just above the ribbon. It holds a series of tiny icons, like the toolbars in older versions of Excel (Figure 9). This is the Quick Access toolbar (or QAT, to Excel nerds).

The Quick Access toolbar puts the Save, Undo, and Redo command right at your fingertips. These commands are singled out because most people use them more frequently than any other commands. But as you'll learn in the Appendix, you can add anything you want here.

Figure 9. The Quick Access toolbar puts the Save, Undo, and Redo command right at your fingertips. These commands are singled out because most people use them more frequently than any other commands. But as you'll learn in the Appendix, you can add anything you want here.

If the Quick Access toolbar were nothing but a specialized shortcut for three commands, it wouldn't be worth the bother. However, the nifty thing about the Quick Access toolbar is that you can customize it. In other words, you can remove commands you don't use and add your own favorites. The appendix of this book shows how.

Microsoft has deliberately kept the Quick Access toolbar very small. It's designed to provide a carefully controlled outlet for those customization urges. Even if you go wild stocking the Quick Access toolbar with your own commands, the rest of the ribbon remains unchanged. (And that means a co-worker or spouse can still use your computer without suffering a migraine.)

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required