Microsoft® Excel® Workbook For Dummies®, 2nd Edition
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2022 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: WHILE THE PUBLISHER AND AUTHORS HAVE USED THEIR BEST EFFORTS IN PREPARING THIS WORK, THEY MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES, WRITTEN SALES MATERIALS OR PROMOTIONAL STATEMENTS FOR THIS WORK. THE FACT THAT AN ORGANIZATION, WEBSITE, OR PRODUCT IS REFERRED TO IN THIS WORK AS A CITATION AND/OR POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE PUBLISHER AND AUTHORS ENDORSE THE INFORMATION OR SERVICES THE ORGANIZATION, WEBSITE, OR PRODUCT MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING PROFESSIONAL SERVICES. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A SPECIALIST WHERE APPROPRIATE. FURTHER, READERS SHOULD BE AWARE THAT WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. NEITHER THE PUBLISHER NOR AUTHORS SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2021950990
ISBN: 978-1-119-83215-7 (pbk); ISBN: 978-1-119-83216-4 (ePDF); ISBN: 978-1-119-83217-1 (ePub)
Microsoft Excel is one of the most powerful and sophisticated software programs available today. That power and that sophistication mean Excel can do some extraordinary, just-this-side-of-miraculous things, but they also mean that Excel comes with a learning curve. Sure, you can get productive with Excel amazingly quickly, but if you want to do more than just scratch Excel's surface, then you need to get comfy with many of the program's deeper — and more useful — features.
Sounds like a plan, right? But what's the best way to get to know this wider Excel landscape? Well, consider what you might do when you arrive in a city you've never visited. One way to get the feel for the place would be to wander the streets willy-nilly. Nothing wrong with that, but it will probably mean your knowledge of the city is scattershot and skin-deep. If you want to really get to know the place, then you need a guidebook that tells you what's worth seeing, how to get there, and what to do when you arrive.
Excel is no different. To really get to know the vast “city” of Excel, clicking commands and cells at random won't get the job done. Instead, it's much better to take a few guided tours that tell you what features are worth learning, how to find those features, and how to use them. This gives you the practical, hands-on experience you need to really learn what Excel can do.
As its name suggests, Microsoft Excel Workbook For Dummies is designed to give you the kind of hands-on experience with all the major aspects of the program you need to start using the program for business or home with a degree of confidence and efficiency. As you’d expect from this type of book, the workbook is primarily composed of questions and exercises that give you plenty of opportunities to experience the purpose and benefits of Excel’s many features.
It’s my hope that as a result of doing the exercises in this workbook, you’ll not only be in firm command of the basic skills necessary to work with confidence in Excel, but also have a good idea of the overall power of the program and what you can do with it.
By convention, all the text entries that you type yourself appear in bold. When it comes to instructions in the exercises throughout the workbook, you’ll notice two conventions:
One other convention that you’ll notice used throughout the text is the display of the names for Excel Ribbon commands, Quick Access toolbar buttons, and dialog box options in the title case, wherein all major words are capitalized except for prepositions. The title case is used to make these names stand out from the rest of the text. Often, however, especially in the case of dialog box options, Microsoft does not always follow this convention, preferring to capitalize only the first letter of the option name.
I assume that you’re a new user of Microsoft Excel motivated to learn its essentials either for work or at home.
To complete most of the exercises in this workbook, you only need to have Microsoft Excel installed on a computer running a version of Microsoft Windows 10 or 11. And it doesn't matter whether you obtained Excel with a Microsoft 365 subscription or an Office 2021 purchase; this book covers the core functionality of both flavors of Excel. For some of the printing exercises, you will benefit from having a printer installed on your system (although you can complete most of their steps and get the gist of the lessons without actually printing the sample worksheets).
Some extra content for this book is available on the web. Go online to find the following:
www.wiley.com/go/excelworkbookfd2e
www.dummies.com and type Microsoft Excel Workbook For Dummies Cheat Sheet in the Search box.Icons are sprinkled throughout the text of this workbook in high hopes that they draw your attention to particular features. Some of the icons are of the heads-up type, whereas others are more informational in nature:
This workbook is constructed such that you don’t have to start working through the exercises in Chapter 1 and end with those in Chapter 24. That being said, it's still to your benefit to complete all the exercises within a particular chapter, if not in a single work session, at least in a short time period.
If you’re a real newbie to Excel and have no experience with any of the earlier versions of the program, I urge you to complete the exercises in Part 1, Chapters 1 through 5, before you take off in your own direction. The exercises in this part are truly fundamental and are meant to give you a strong foundation in the basic features that all Excel users need to know.
Please keep in mind that I designed the exercises in this workbook to work with my Excel companion book, Excel All-In-One For Dummies. It can therefore provide you with additional information about the Excel features you’re using either at the time you go through the workbook exercises or afterwards. To facilitate this crossover usage, I have, wherever possible, used the same example files in the exercises of this workbook as you see illustrated and explained at length in the larger book.
Whatever you do next and wherever you go in this workbook, just be sure that you enjoy yourself!
Part 1
IN THIS PART …
Chapter 1
IN THIS CHAPTER
Opening and identifying the different parts of the Excel screen
Selecting commands in the Excel Backstage view
Selecting commands on the Ribbon and Quick Access toolbar
Customizing the Ribbon and Quick Access toolbar
If the proverbial journey of a thousand miles begins with a single step, what should be the first step on your new Excel journey? You can take lots of possible directions, but might I humbly suggest a tour? Nothing exhaustive, mind you; just a quick look at the most basic elements of the Excel program window. Does that sound too elementary? You'd be surprised. One of the secrets of Excel proficiency and efficiency is knowing where to find the command, feature, or setting you want to work with next. Sure, maybe you really want to waste precious time clicking aimlessly around the Excel interface until your blood boils and jets of steam blast out of your ears. Hey, it's your life. But my guess is that doesn't sound appealing to you, not even a little. So: A tour it is, then.
To that end, the exercises in this first chapter are designed to get you familiar with the Excel interface. After doing these exercises, you should be comfortable with all aspects of the Excel window and the command structure and ready to do all the rest of the exercises in this book.
Excel is one of the primary application programs included as part of Microsoft Office. To get proficient with Excel, you need to be familiar with all the various ways of launching the program.
Q. How many ways are there to start Excel?
A. You should be familiar with the two main methods:
Q. Are there quicker ways to launch Excel?
A. Yes, you can use two other methods:
Try It
In Exercise 1-1, you pin Excel both to the Windows Start menu and to the Windows taskbar and then launch Excel using each of these two methods:
Right-click Excel and then click Pin to Start.
Windows adds a pinned tile for Excel to the bottom of the Start menu.
In Windows 11, click Back to return to the main Start menu, right-click the pinned Excel icon, and then click Move to Top.
Windows moves the pinned Excel tile to the top-left corner of the Start menu.
Right-click the pinned Excel tile on the Start menu and then click Pin to Taskbar.
Windows adds a pinned Excel button to the taskbar.
If you don’t like where Windows pinned the Excel button, click and drag it to the left or right to your preferred location.
Windows moves the pinned Excel button along with the mouse pointer and you can drop the button in your preferred position.
Practice launching Excel by clicking the pinned Excel Start menu tile and then exiting the program. Then launch Excel again, this time by clicking the pinned Excel button on the Windows taskbar.
You can leave Excel running for Exercise 1-2.
When you launch Excel by opening the Windows Start menu and clicking Excel, the Home tab of Excel's Backstage view appears. Click the Blank Workbook thumbnail to start a fresh workbook, which contains a single worksheet named Sheet1.
Before you can start using Excel, you must be familiar with its window. Figure 1-1 shows you the Excel window as it appears when you launch a new workbook. Note the names of the different parts of the window before you perform Exercise 1-2.
FIGURE 1-1: The Excel program window as it appears immediately after launching a fresh workbook.
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’s the primary function of the Ribbon in Excel?
A. To group related Excel commands together and give you quick and easy access to these commands.
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.
Clicking the File tab 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 the Backstage view — Home, New, Open, Info, Save, Save As, and so on — panels appear that bring together further related and commonly used options on the left side along with pertinent information on the right.
Try It
In this exercise, you get familiar with Excel's Backstage view and its commands by opening the Backstage view and selecting some of its buttons. Make sure that Excel is running and a blank workbook with its empty Sheet1 worksheet is active on your computer monitor.
Click the Info button on the menu.
The Info screen now appears to the immediate right of the Info button (see Figure 1-2). The Info screen is divided into two areas: the left side offers commands for working with the file, while the right side displays information about the file.
Now, click the Save As button.
You use the Save As screen to save a new workbook or to save an existing workbook with a new name or location.
Click Browse.
Excel opens the Save As dialog box where you can modify the name, location, and type of Excel workbook file before saving a copy of it.
Click the Share button.
Excel closes Backstage view and opens the Share dialog box, which enables you to share workbook files with co-workers and clients.
Press Ctrl+P (the shortcut key for printing in Excel).
Excel opens the Backstage view and displays the Print panel where you can preview the printout (when there’s data in your worksheet that can be printed) and change several print settings. Because you selected the Print panel from an empty worksheet, the message, “We didn't find anything to print” appears on the right panel where the first page of the workbook’s print preview normally appears.
Press Esc 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. 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.
FIGURE 1-2: The Excel Backstage view with the Info button selected.
The Excel Ribbon contains the bulk of all the commands that you use in creating, editing, formatting, and sharing your spreadsheets, charts, and tables. As shown in Figure 1-1, normally the Ribbon is divided into the following tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View. There's also a Help tab for accessing the Excel Help system. If you're using a touchscreen PC, you also see a Draw tab.
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 group.
Try It
In Exercise 1-3, you practice selecting commands from the Ribbon. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
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.
Press the Alt key.
Note the access-key letters that now appear on the File menu, title bar, and the Ribbon tabs.
If you prefer selecting Excel commands from the keyboard, you’ll probably want to memorize the following access keys for selecting these tabs:
Select the Gridlines check box to 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.
Click the Ribbon Display Options button (pointed out earlier in Figure 1-1) and then click Show Tabs Only.
Excel immediately minimizes the Ribbon to display only the tab names. Click a tab to display its buttons. Excel minimizes the Ribbon to its tab names once again after you select a tab’s commands or click outside the Ribbon.
Click Data on the minimized Ribbon.
Excel expands the Ribbon to display all the Data tab buttons.
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'll work with the Ribbon expanded at all times in all remaining exercises in this workbook.
To return to having Excel display the entire Ribbon full-time, click any tab, click the Ribbon Display Options button and then click Always Show Ribbon.
The Ribbon now remains fully displayed at all times as you select any of its tabs and buttons without ever obscuring any part of the worksheet display.
Excel 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
In Exercise 1-4, you practice adding a custom tab to the Ribbon. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
Choose File ⇒ Options (Alt+FT) to open the Excel Options dialog box and then click the Customize Ribbon option.
Alternatively, right-click any part of the Ribbon and then click Customize the Ribbon.
Excel displays the Customize the Ribbon panel in the Excel Options dialog box. This panel is divided into two list boxes: Choose Commands From on the left side and Main Tabs on the right side (see Figure 1-3).
In the Main Tabs list box, click View to select it (be sure to click just the name View and not its check box) and then click New Tab.
Right below the View tab, Excel inserts a new tab with the generic name New Tab (Custom), which includes a new group with the generic name New Group (Custom). This custom tab and group appear between the View tab and the Developer tab in the Main Tabs list box.
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.
Replace New Tab by typing Misc (for Miscellaneous) in the Display Name text box and then click OK.
Misc (Custom) now appears in the Main Tabs list box sandwiched between View and Developer.
FIGURE 1-3: The Excel Options dialog box with the Customize Ribbon option selected.
After you add a custom tab to the Excel 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 to your own custom tab are arranged in groups. When you first create a custom tab, it contains only a single tab — named 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
In Exercise 1-5, you practice adding commands to the custom tab you added to the Ribbon in the Exercise 1-4. 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.
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.
Replace New Group by typing Data Form in the Display Name text box and then clicking 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 when completing some of the exercises in Chapter 17.
Use the Choose Commands From drop-down list to select Commands Not in the Ribbon.
Excel now displays an alphabetical list of commands that are not currently on the Ribbon.
Click the Form button in this Commands Not in the Ribbon list and then click Add.
Excel adds the Form button under the Data Form (Custom) group in the Main Tabs list box.
Click OK to close the Excel Options dialog box.
The custom Misc tab you just created now appears at the end of the Excel Ribbon.
Click the Misc tab.
The Misc tab is selected, displaying its sole Form button in the single Data Form group.
As its name implies, the purpose of the Quick Access toolbar is to give you speedy access to a few Excel tools. That sounds great! So why is the Quick Access toolbar hidden by default in the latest versions of Excel? Good question. The Quick Access toolbar used to appear in the Excel title bar (although you could change that position), so perhaps Microsoft figured the title bar was getting a tad overcrowded. Microsoft did keep the AutoSave switch and the Save button in the title bar, but these are no longer part of the Quick Access toolbar. (Also, Microsoft moved the Undo and Redo buttons to the Home tab.)
If you want to use the Quick Access toolbar, your first chore is to display it.
To get the Quick Access toolbar onscreen, use any one of the following methods:
The Quick Access toolbar appears as a strip below the Ribbon and starts off with just the Customize Quick Access Toolbar button, pointed out in Figure 1-4 that, when clicked, opens a pull-down menu. The options on this pull-down menu enable you to quickly customize the buttons that appear in the Quick Access toolbar. In addition, you can change the placement of the Quick Access toolbar by moving it up so that it appears immediately above the Ribbon.
Try It
In Exercise 1-6, you practice customizing the contents and position of the Quick Access toolbar using options that appear on the Customize Quick Access Toolbar menu. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
Click the Customize Quick Access Toolbar button and then click Show Above the Ribbon.
The Quick Access toolbar with its Customize Quick Access Toolbar button now appears immediately above the Ribbon.
Click the Customize Quick Access Toolbar button and then click Show Below the Ribbon.
The Quick Access toolbar returns to its default position below the Ribbon.
Click the Customize Quick Access Toolbar button and then click New.
Excel adds the New button to the Quick Access toolbar so that you can click it to create a new, blank workbook.
Repeat Step 3 to add the Open, Quick Print, and Spelling buttons to the Quick Access toolbar. If you're using Excel on a touchscreen device, also add the Touch/Mouse Mode button, which enables you to quickly switch into Touch mode for easier touch access to the Ribbon commands.
Use the ScreenTips attached to each button to verify that you’ve correctly added the New, Open, Quick Print, and Spelling buttons (as well as the Touch/Mouse mode button, if applicable) to the Quick Access toolbar, noting the shortcut keys listed.
FIGURE 1-4: Click the Customize Quick Access Toolbar button to open its pull-down menu.
When customizing the command buttons on the Quick Access toolbar, you aren’t limited to the selection of commands that appear on the Customize Quick Access Toolbar pull-down menu. Using command options that appear in the Excel Options dialog box, you can add buttons for any of the commands that appear on the Ribbon as well as some Excel commands that remain completely unavailable until you add them to the Quick Access toolbar.
Try It
In Exercise 1-7, you practice customizing the contents of the Quick Access toolbar using commands that appear in the Excel Options dialog box. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
Click the Customize Quick Access Toolbar button and then click the More Commands option on its menu.
Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected (see Figure 1-5). This dialog box contains two list boxes:
To add a new command to the toolbar, you select it in the Choose Commands From list box and then click the Add button. To reorder the buttons on the toolbar, you click its command button in the Customize Quick Access Toolbar list box and then click the Move Up or Move Down buttons (with the black triangles pointing up and down, respectively) until the selected button is in the desired position.
Click the drop-down button on the Choose Commands From drop-down list box and then click the Commands Not in the Ribbon option on its drop-down menu.
The Choose Command From list box now contains only command buttons that are not found on the various tabs of the Excel Ribbon.
Click the AutoFormat command option in the Choose Commands From list box (the one with the lightning bolt on top of a small table) and then click the Add button.
The AutoFormat command option is now listed at the very bottom of your Customize Quick Access Toolbar list box, indicating that it is now the last button on the Quick Access toolbar.
Repeat Step 3 to add the AutoFilter and Draw Borders command options to the Quick Access toolbar.
Next, you want to modify the order in which the command buttons appear on your customized Quick Access toolbar so that they appear in this order arranged in three groups:
FIGURE 1-5: The Excel Options dialog box with the Quick Access Toolbar option selected.
Use the Move Down button to move the Spelling and Touch/Mouse Mode buttons so that they now appear in the same order below the Draw Borders button.
The command buttons for your customized version of the Quick Access toolbar now appear in the correct order in the Customize Quick Access Toolbar list box in the Excel Options dialog box. The only other thing you need to do is to divide them into groups by adding a vertical bar called a separator.
Click the New File command option in the Customize Quick Access Toolbar list box to select it and then click the Separator option at the very top of the Choose Commands From list box to select this option. Click the Add button.
Excel inserts a separator between the New File and AutoFormat buttons in the Customize Quick Access Toolbar list box.
Use this same technique to add a separator between the Draw Borders and Spelling buttons in the Customize Quick Access Toolbar list box.
Your customized Quick Access toolbar now contains three groups of command buttons created by the two separator options that appear after the New File button and the Draw Borders button.
Click OK to close the Excel Options dialog box.
Check the buttons on your customized toolbar against those shown in the toolbar in Figure 1-6.
FIGURE 1-6: The top part of the Excel window with the customized Quick Access Toolbar.