Acerca del autor

Greg Harvey, PhD, is an experienced educator and the bestselling author of numerous Excel books.

De la contraportada

Get hands-on practice and learn to use all the features of Excel 2010!

Using Excel as nothing more than an electronic version of an accountant's spreadsheet would be like using a super-modified race car just to drive the kids to school. Excel lets you design forms, interact with corporate databases, interpret and present sales figures, and perform dozens of other tasks. The exercises in this workbook let you practice the techniques and learn by doing. Then you can reinforce your knowledge by viewing the feature demos of various Excel tasks on the CD.

See how to

• Create, format, modify, and print spreadsheets

• Build and use formulas

• Work with charts and graphics

• Manage and analyze data

• Customize Excel with macros and VBA

On the CD, you'll find:

• Demo files showing you various features of Excel 2010

• Sample workbook files and completed worksheets for practice exercises

See the CD Appendix for complete system requirements.

• Practice creating spreadsheets and working with formulas and functions

• Learn to depict your data with graphics

• Plan common or complex Excel tasks

• View feature demos on the CD

Fragmento. © Reproducción autorizada. Todos los derechos reservados.

Excel 2010 Workbook For Dummies

John Wiley & Sons

Chapter One

In This Chapter

* Identifying the different parts of the Excel display screen

* Selecting commands in the Excel Backstage View

* Selecting commands on the Ribbon and Quick Access toolbar

* Customizing the Ribbon and Quick Access toolbar

The Excel 2010 interface relies primarily on the Ribbon, a block of commands displayed at the top of the screen and divided into distinct blocks called tabs. The single vestige of the old pull-down menus from versions prior to Excel 2007 is the File menu that's displayed along the left side of the brand new Backstage View screen. The File menu contains all the File commands. You open the Backstage View that contains the menu by clicking the File menu (which looks just like the Ribbon tabs in the worksheet view except that it's the only one that's green). Also, in place of the many toolbars in the pre-Excel-2007 versions, Excel 2010 offers a single toolbar called the Quick Access toolbar.

The exercises in this first chapter are designed to get you familiar with the Excel 2010 interface. As a result of doing these exercises, you should be comfortable with all aspects of the display screen and the command structure and ready to do all the rest of the exercises in this book.

Identifying the Parts of the Excel Display Screen

Before you can start using Excel 2010, you have to be familiar with its display screen. Figure 1-1 shows you the Excel 2010 display screen as it first appears when you launch the program. Note the names of the different parts of the display screen before you perform Exercise 1-1.

EXAMPLE

Q. What are the primary functions of the commands located on the File menu in the Excel Backstage View?

A. To open, close, save, print, and share your Excel workbook files as well as to modify the Excel program options.

Q. What is the primary function of the Quick Access toolbar?

A. To enable you to quickly select Excel commands that you use all the time without having to open the File menu or use the Ribbon commands.

Q. What's the primary function of the Ribbon in Excel 2010?

A. To group related Excel commands together and give you quick and easy access to these commands.

Selecting Commands on the File Menu in the Backstage View

Clicking the File menu, the green tab-looking thingy at the far left of the Ribbon, opens the Excel Backstage View with a menu of options that appears down the left side. Almost all the commands on this menu are related to actions that affect the entire file, such as saving and printing. If you prefer, you can open this view and access the menu by pressing Alt+F (F for File) instead of clicking the File menu.

When you select any of the major options on this menu — Info, Recent, New, Print, Save & Send, or Help — panels appear that bring together further related and commonly used menu options on the left side along with pertinent information and, in the case, of the Info and Print options, document previews, on the right side.

WATCH THE VIDEO

Before you attempt the exercises in this chapter, you may want to play the Using the Excel 2010 Ribbon and Backstage.exe demo located in the Excel Feature Demo folder on your workbook CD-ROM. This video demonstrates selecting commands from the File menu as well as on the Ribbon and Quick Access toolbar.

Try It

Exercise 1-1: Opening the Backstage View and Selecting Its Menu Commands In this exercise, you get familiar with the new Excel Backstage View and the commands on its File menu as you practice opening the Backstage View and selecting some of its menu commands. Make sure that Excel 2010 is running and an empty Sheet1 worksheet is active on your computer monitor (see Chapter 2 if you need information on launching Excel).

1. Click the File menu to switch to the Excel Backstage View and display the list of commands (Info through Exit) on the left side of this screen.

REMEMBER

Keep in mind that when you switch to the Backstage View when no workbook is open for editing in Excel 2010, the program automatically selects the Recent menu command that displays a Recent Workbooks pane on the left with a list of recently opened workbook files and a Recent Places pane on the right with a list of file folders recently opened. When you switch to the Backstage View when a workbook file is open for editing, Excel selects the Info option at the top of the menu instead, displaying a panel with a thumbnail of the current worksheet and a list of properties describing aspects of the file.

2. Click the Info option on the menu.

Note that an Information about Book1 panel now appears attached to the immediate right of the Info option and that all of the suboptions, Save through Close above Info, are indented as a group (see Figure 1-2).

3. Now, highlight and click the Save As command to select it.

Excel opens the Save As dialog box in the regular worksheet view where you can modify the name, location, and type of Excel workbook file before saving a copy of it.

4. Press the Esc (Escape) key on your keyboard to close the Save As dialog box.

5. Press Alt+F to open the Backstage View with its File menu again, this time from the keyboard.

This time, small letters appear on each command in the Backstage View menu. These are the access keys that you can type to select a menu option rather than clicking its name or button.

6. Type D to select the Save & Send option and display the Save & Send panel, which contains a number of options for sharing workbook files with coworkers and clients.

7. Click the File menu at the top of the menu, immediately above the Save option.

8. Press Ctrl+P (the shortcut key for printing in Excel).

Excel selects the Print command on the menu in the Backstage View and displays a Print panel where you can preview the printout (when there's data in your worksheet that can be printed) and change simple print settings. Because you selected the Print panel from an empty worksheet, the message, "Microsoft Excel did not find anything to print" appears on the right panel where the first page of the workbook's print preview normally appears.

9. Press the Escape key to return to the normal worksheet view and then press Alt+FT.

Doing this selects the Options command in the Backstage View, which in turn, opens the Excel Options dialog box in the regular worksheet view. This dialog box contains all the options for changing the Excel program and worksheet options. These options are divided into categories General through Trust Center.

10. Click the Advanced button in the left pane to display all the Advanced options in the right pane. Next, scroll down to the Display Options for This Worksheet section and click the Show Page Breaks check box to remove its check mark before you click the OK command button to close the dialog box.

Note that deselecting the Show Page Breaks option in the Excel Options dialog box removes all the dashed page break lines from the Sheet1 worksheet.

Selecting Commands from the Ribbon

The Excel Ribbon contains the bulk of all the commands that you use in creating, editing, formatting, and sharing your spreadsheets, charts, and data lists. As shown in Figure 1-2, normally the Ribbon is divided into seven tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View. The commands that appear on each tab are then further divided into Groups, containing related command buttons. Also, many of these groups contain a Dialog Box Launcher button that appears in the lower-right corner of the Group. Clicking this button opens a dialog box of further options related to the particular Group.

Try It

Exercise 1-2: Selecting Commands from the Ribbon

In Exercise 1-2, you get practice selecting commands from the Ribbon. Make sure that Excel 2010 is running and an empty Sheet1 worksheet is active on your computer monitor.

1. Click the Formulas tab to displays its commands.

Note that the commands on the Formulas tab are divided into four Groups: Function Library, Defined Names, Formula Auditing, and Calculation.

2. Press the Alt key.

Note the access-key letters that now appear on the File menu, Quick Access toolbar options, and the Ribbon tabs.

TIP

If you prefer selecting Excel commands from the keyboard, you'll probably want to memorize the following access keys for selecting the seven tabs:

• Home tab: Alt+H

• Insert tab: Alt+N

• Page Layout tab: Alt+P

• Formulas tab: Alt+M

• Data tab: Alt+A

• Review tab: Alt+R

• View tab: Alt+W

3. Type W to display the contents of the View tab and then type VG to remove the check mark from the Gridlines check box in the Show Group.

4. Click the Gridlines check box to select it again and redisplay the gridlines in the worksheet.

As you may have noticed, the Ribbon takes up quite of bit of screen space that is otherwise used to display worksheet data. You can take care of this by setting Excel to minimize the Ribbon each time you select one of its commands to display only the tab names.

5. Click the Minimize the Ribbon button (the one with the caret symbol to the immediate left of the help button with the question mark icon).

Excel immediately minimizes the Ribbon to display only the seven tab names and the Minimize the Ribbon button changes to the Expand the Ribbon button (indicated by the caret symbol pointing downward). Excel continues to reduce the Ribbon to its tab names any time after you select one its commands until you click the Expand the Ribbon button or press Ctrl+F1.

REMEMBER

Keep in mind that you can expand the Ribbon to display all the command buttons on the currently selected tab any time that the Ribbon is minimized simply by double-clicking the tab or pressing Ctrl+F1 (which acts like a toggle switch for altering between a minimized and fully maximized Ribbon).

6. Click Data on the minimized Ribbon.

Excel expands the Ribbon to display all of the Data tab command buttons.

7. Click anywhere in the worksheet area to minimize the Ribbon once again.

The only problem with this minimized Ribbon arrangement is that the temporarily expanded Ribbon covers the first three rows of the worksheet. This makes it very difficult to work with data at the top of the worksheet. For that reason, as well as to help you get comfortable with unfamiliar Ribbon commands, you will work with the Ribbon expanded at all times in all remaining exercises in this workbook.

8. Press Ctrl+F1 to maximize the Ribbon and then click the Home tab to displays its command buttons.

The Ribbon now remains fully displayed at all times as you select any of its tabs and command buttons without ever obscuring any part of the worksheet display.

Adding a custom tab to the Excel Ribbon

Excel 2010 enables you to customize the Ribbon by creating a custom tab to which you can then add your own groups of commands. When you create a custom tab, Excel automatically assigns an available hot key to it.

Try It

Exercise 1-3: Adding a Custom Tab to the Excel Ribbon

In Exercise 1-3, you get practice adding a custom tab to the Ribbon. Make sure that Excel 2010 is running and an empty Sheet1 worksheet is active on your computer monitor (see Chapter 2 for information on launching Excel).

1. Select File.Options (Alt+FT) to open the Excel Options dialog box and then click the Customize Ribbon option.

Excel displays the Customize the Ribbon panel in the Excel Options dialog box. This panel is divided into two list boxes: Popular Commands on the left side and Main Tabs on the right side (see Figure 1-3).

2. Click the View tab check box in the Main Tabs list box to select it and then click the New Tab button.

Excel inserts a generic New Tab (Custom) with a generic New Group (Custom) right below it. This custom tab and group appear between the View tab and the Developer tab in the Main Tabs list box.

3. Click New Tab (Custom) in the Main Tabs list box to select it and then click the Rename command button.

Excel opens the Rename dialog box where you can replace the generic New Tab display name with a descriptive name.

4. Replace New Tab by typing Misc (for Miscellaneous) in the Display Name text box and then select OK.

Misc (Custom) now appears in the Main Tabs list box sandwiched between View and Developer.

Adding commands to groups on your custom tab

After you add a custom tab to the Excel 2010 Ribbon, you can then start adding the commands you want to appear on this tab. Just as with the standard Ribbon tabs, the commands you add on your own custom tab are arranged in groups. When you first create a custom tab, it contains only a single, generic New Group (Custom) into which to add your commands. You can, however, add other groups to the custom tab using the New Group command button as well as give these groups their own descriptive names using the Rename command button.

Try It

Exercise 1-4: Adding Commands to a Custom Tab

In Exercise 1-4, you get practice adding commands to the custom tab you added to the Ribbon in Exercise 1-3. Before you start this exercise, make sure that the Excel Options dialog box is still open with a Misc (Custom) tab appearing in the Main Tabs list box between View and Developer.

1. Click the New Group (Custom) listing under Misc (Custom) in the Main Tabs list box to select it and then click the Rename button.

Excel opens the Rename dialog box where you can replace the generic New Group name with your own descriptive name.

2. Replace New Group by typing Data Form in Display Name text box and then selecting OK.

Data Form (Custom) now appears as the sole group on the Misc custom tab in the Main Tabs list box. Now, you're ready to add the Form command button to the Data Form group that you can use later on when completing some of the exercises in Chapter 17.

3. Click the drop-down button to the right of Popular Commands in the Choose Commands From drop-down list box at the top of the left side of the Customize the Ribbon panel and then click Commands Not in the Ribbon.

Excel now displays an alphabetical list of commands that are not currently on the Ribbon in the list box on the left.

4. Click the Form button in this Commands Not in the Ribbon list box and then click the Add command button.

Excel adds the Form button under the Data Form (Custom) group in the Main Tabs list box.

5. Click the OK button to close the Excel Options dialog box. The custom Misc tab you just created now appears at the end of the Excel 2010 Ribbon.

6. Click the Misc tab or press Alt+Y to select this tab.

The Misc tab is selected, displaying its sole Form button in the single Data Form group.

7. Click the Home tab to select it.

Selecting Commands on the Quick Access Toolbar

When you first start working with Excel 2010, the Quick Access toolbar contains only the following three simple command buttons: