Excel® All-in-One For Dummies®

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

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: 2021948861

ISBN: 978-1-119-83072-6 (pbk); ISBN: 978-1-119-83075-7 (ePDF); ISBN: 978-1-119-83074-0 (ePub)

Excel® All-in-One For Dummies®

To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel All-in-One For Dummies Cheat Sheet” in the Search box.

Table of Contents

Cover

Title Page

Introduction

About This Book
Foolish Assumptions
Conventions Used in This Book
Icons Used in This Book
Beyond the Book
Where to Go from Here

Book 1: Excel Basics

Chapter 1: Excel: The 50-Cent Tour
1. A Bird's-Eye View of Excel
2. Checking Out Excel’s Start Screen
3. Getting Comfy with the Excel User Interface
4. Getting Help
5. Launching and Quitting Excel
Chapter 2: Customizing Excel
1. Tailoring the Quick Access Toolbar to Your Tastes
2. Exercising Your Options
3. Using Office Add-ins
4. Using Excel’s Own Add-ins

Book 2: Worksheet Design

Chapter 1: Building Worksheets
1. Creating Fresh Workbooks
2. Getting to Know Excel's Data Types
3. Data Entry 101
4. Linking to Other Documents
5. Saving the Data
6. Document Recovery to the Rescue
Chapter 2: Formatting Worksheets
1. Selecting Cells and Ranges
2. Adjusting Columns and Rows
3. Formatting Tables from the Ribbon
4. Formatting Tables with the Quick Analysis Tool
5. Formatting Cells from the Ribbon
6. Formatting Cell Ranges with the Mini-Toolbar
7. Using the Format Cells Dialog Box
8. Hiring Out the Format Painter
9. Easier Formatting with Cell Styles
10. Analyzing Data with Conditional Formatting
Chapter 3: Editing and Proofing Worksheets
1. Opening a Workbook
2. Editing a Cell
3. A Worksheet with a View
4. Copying and Moving Stuff Around
5. Finding and Replacing Worksheet Data
6. Getting It Right: Spell Checking Your Worksheets
7. Looking Up and Translating Stuff
8. Marking Invalid Data
Chapter 4: Managing Worksheets
1. Reorganizing the Worksheet
2. Reorganizing the Workbook
3. Working with Multiple Workbooks
4. Consolidating Worksheets
Chapter 5: Printing Worksheets
1. Printing from the Backstage View
2. Quick Printing the Worksheet
3. Working with the Page Setup Options
4. Headers and Footers
5. Solving Page Break Problems
6. Printing the Formulas in a Report

Book 3: Formulas and Functions

Chapter 1: Building Basic Formulas
1. Getting Started with Formulas
2. Augmenting Formulas with Worksheet Functions
3. Copying Formulas
4. Adding Array Formulas
5. Naming Cells and Ranges
6. Adding Linking Formulas
7. Controlling Formula Recalculation
8. Circular References
Chapter 2: Logical Functions and Error Trapping
1. Understanding Error Values
2. Using Logical Functions
3. Error-Trapping Formulas
4. Whiting-Out Errors with Conditional Formatting
5. Checking It Twice: Auditing Your Formulas
6. Removing Errors from the Printout
Chapter 3: Date and Time Formulas
1. Understanding Dates and Times
2. Using Date Functions
3. Using Time Functions
Chapter 4: Finagling Financial Formulas
1. Some Financial Function Basics Before You Begin
2. The PV, NPV, and FV Functions
3. The PMT Function
4. Depreciation Functions
5. Excel's Advanced Financial Functions
Chapter 5: Math and Statistical Formulas
1. Math & Trig Functions
2. Statistical Functions
Chapter 6: Lookup, Information, and Text Formulas
1. Looking Up Stuff with the Lookup Functions
2. Getting Cell Reference Info with the Reference Functions
3. Interrogating Excel
4. Working with Text Functions and Formulas

Book 4: Worksheet Collaboration and Review

Chapter 1: Protecting Workbooks and Worksheet Data
1. Password-Protecting the Workbook
2. Setting Up Worksheet Data Protection
3. Protecting the Workbook Structure
Chapter 2: Preparing a Workbook for Distribution
1. Getting Your Workbook Ready for Review
2. Annotating Workbooks
Chapter 3: Sharing Workbooks and Worksheet Data
1. Sharing Your Workbooks Online
2. Sharing Your Workbooks via Email
3. Sharing Excel Data with Office Programs
4. Making Shared Workbooks Accessible
5. Exporting Workbooks to Other Usable File Formats
6. Collaborating Conversationally with Comments

Book 5: Charts and Graphics

Chapter 1: Charting Worksheet Data
1. Worksheet Charting 101
2. Adding Sparkline Graphics to a Worksheet
3. Adding Infographics to a Worksheet
4. Printing Charts
Chapter 2: Adding Graphic Objects
1. Going Graphical: The Basics of Working with Graphic Objects
2. Inserting Different Types of Graphics
3. Making Changes to Graphics
4. Drawing Graphics
5. Adding Screenshots of the Windows Desktop
6. Using Themes

Book 6: Data Management

Chapter 1: Building and Maintaining Tables
1. Table Basics
2. Sorting Table Data
3. Subtotaling Table Data
Chapter 2: Filtering and Querying a Table
1. Filtering Data
2. Using the Database Functions
3. Querying External Data

Book 7: Data Analysis

Chapter 1: Performing What-If Analysis
1. Using Data Tables
2. Exploring Different Scenarios
3. Hide and Goal Seeking
4. Using Solver
Chapter 2: Performing Large-Scale Data Analysis
1. Creating PivotTables
2. Formatting a PivotTable
3. Sorting and Filtering the PivotTable Data
4. Modifying the PivotTable
5. Creating PivotCharts
6. Using the Power Pivot Add-in
7. Using the 3D Maps Feature
8. Creating Forecast Worksheets

Book 8: Macros and VBA

Chapter 1: Recording and Running Macros
1. Macro Basics
2. Assigning Macros to the Ribbon and the Quick Access Toolbar
3. Macro Security
Chapter 2: VBA Programming
1. Using the Visual Basic Editor
2. Creating Custom Excel Functions

Index

About the Authors

Connect with Dummies

End User License Agreement

List of Tables

Book 1 Chapter 1

TABLE 1-1 Keystrokes for Moving the Cell Pointer

Book 1 Chapter 2

TABLE 2-1 Advanced Excel Options

Book 2 Chapter 1

TABLE 1-1 Common Ways to Enter Dates and Times
TABLE 1-2 Keys Used in Completing Data Entry
TABLE 1-3 Keystrokes for Moving Within a Selection

Book 2 Chapter 2

TABLE 2-1 The Formatting Command Buttons in the Home Tab's Font, Alignment, and ...

Book 2 Chapter 3

TABLE 3-1 The Paste Special Dialog Box Options

Book 2 Chapter 4

TABLE 4-1 Outline Buttons

Book 3 Chapter 1

TABLE 1-1 The Different Types of Operators in Excel
TABLE 1-2 Natural Order of Operator Precedence in Formulas
TABLE 1-3 The Calculation Options in Excel

Book 3 Chapter 2

TABLE 2-1 Error Values in Excel

Book 3 Chapter 4

TABLE 4-1 Excel's Advanced Financial Functions

Book 3 Chapter 6

TABLE 6-1 The CELL Functions info_type Arguments
TABLE 6-2 Text Values Returned by the “format” info_type

Book 5 Chapter 1

TABLE 1-1 Parts of a Typical Chart
TABLE 1-2 Format Tab Options

Book 6 Chapter 1

TABLE 1-1 Techniques for Navigating the Data Form

Book 6 Chapter 2

TABLE 2-1 The Comparative Operators in the Selection Criteria
TABLE 2-2 The Database Functions in Excel

Book 7 Chapter 1

TABLE 1-1 Solver Option Settings

List of Illustrations

Book 1 Chapter 1

FIGURE 1-1: The Excel Start screen with the Home tab selected that appears imme...
FIGURE 1-2: The Excel program window as it appears after first opening a blank ...
FIGURE 1-3: The Excel Backstage view displaying the Info screen with permission...
FIGURE 1-4: Excel’s Ribbon consists of a series of tabs containing command butt...
FIGURE 1-5: When you select a Ribbon tab by pressing Alt and the KeyTip letter ...
FIGURE 1-6: The Excel Status bar.
FIGURE 1-7: The Excel Help task pane.
FIGURE 1-8: Launching Excel from the Windows Start screen.

Book 1 Chapter 2

FIGURE 2-1: Click Customize Quick Access Toolbar to see a few useful commands t...
FIGURE 2-2: Use the buttons on the Quick Access Toolbar tab of the Excel Option...
FIGURE 2-3: The General tab’s options enable you to change many global Excel se...
FIGURE 2-4: The Formulas tab’s options enable you to change how Excel works wit...
FIGURE 2-5: The Proofing tab’s options enable you to change AutoCorrect and spe...
FIGURE 2-6: The Save tab’s options enable you to change the automatic save and ...
FIGURE 2-7: The Editing and Cut, Copy, and Paste options on the Advanced tab co...
FIGURE 2-8: The display options in the center of the Advanced tab control what’...
FIGURE 2-9: The Advanced tab options control calculation, general, and 1-2-3 co...
FIGURE 2-10: The Customize Ribbon tab options enable you to control which tabs ...
FIGURE 2-11: Inserting the Excel Mini Calendar and Date Picker add-in into an E...
FIGURE 2-12: Activating built-in Excel Add-ins in the Add-Ins dialog box.

Book 2 Chapter 1

FIGURE 1-1: The New screen is home to a fistful of templates you can use to get...
FIGURE 1-2: Searching for finance-related templates.
FIGURE 1-3: When you click a template thumbnail, Excel displays more details ab...
FIGURE 1-4: The downloaded template now appears in the New screen.
FIGURE 1-5: Click the alert indicator to open the menu attached to the Number S...
FIGURE 1-6: One way to complete a cell entry is to click the Enter button in th...
FIGURE 1-7: A Windows touchscreen after displaying and undocking the standard T...
FIGURE 1-8: You can add your own automated replacements to the AutoCorrect tab.
FIGURE 1-9: Dragging the Fill handle to fill in a series with the twelve months...
FIGURE 1-10: The series of monthly column headings with the AutoFill Options dr...
FIGURE 1-11: Some series created with AutoFill by using custom increments.
FIGURE 1-12: Creating a custom list of cities for AutoFill.
FIGURE 1-13: New data table containing full names that need to be split up in s...
FIGURE 1-14: Creating a custom drop-down list in the Data Validation dialog box...
FIGURE 1-15: Selecting a city from the custom drop-down list.
FIGURE 1-16: Getting an error message after trying to input a city that’s not o...
FIGURE 1-17: Click the lower half of the Link button to see a list of recent do...
FIGURE 1-18: Use the Insert Hyperlink dialog box to insert a link in a workshee...
FIGURE 1-19: The Excel title bar comes with a Save icon.
FIGURE 1-20: Use the Save As screen to specify where you want to save a new wor...
FIGURE 1-21: Specifying the folder and filename prior to saving the new workboo...

Book 2 Chapter 2

FIGURE 2-1: Worksheet with a nonadjacent cell selection made up of several diff...
FIGURE 2-2: Selecting the cells in the first column of the table with AutoSelec...
FIGURE 2-3: Selecting all the remaining columns of the table with AutoSelect. (...
FIGURE 2-4: Selecting a cell range with the Go To dialog box.
FIGURE 2-5: Selecting a format for the new table in the Table Styles gallery.
FIGURE 2-6: Indicating the range of the table in the Create Table dialog box af...
FIGURE 2-7: After you select an initial table format, the Table Design tab appe...
FIGURE 2-8: Previewing the selected data formatted as a table with the Quick An...
FIGURE 2-9: Right-click your cell selection to display its shortcut menu along ...
FIGURE 2-10: Options for customizing the formatting assigned by the Currency nu...
FIGURE 2-11: You can create your own number format using the Custom category in...
FIGURE 2-12: Worksheet with long text entries that spill over into blank cells ...
FIGURE 2-13: Worksheet after wrapping long text entries in their cells, increas...
FIGURE 2-14: Worksheet after rotating a table’s column headings up 90 degrees. ...
FIGURE 2-15: You can assign new fonts, font sizes, attributes, and colors on th...
FIGURE 2-16: The border options available on the Home tab’s Borders button’s dr...
FIGURE 2-17: The border options available on the Border tab of the Format Cells...
FIGURE 2-18: Using the options on the Fill tab to select a new fill color, grad...
FIGURE 2-19: Selecting a new gradient for your cell selection in the Fill Effec...
FIGURE 2-20: You can apply multiple styles to a cell selection using the Cell S...
FIGURE 2-21: The initial Data Bars gradient fill applied to some worksheet data...
FIGURE 2-22: You can use the Quick Analysis tool’s Formatting option to apply c...
FIGURE 2-23: Define a new conditional formatting rule using the options in the ...

Book 2 Chapter 3

FIGURE 3-1: Use the Open screen to find and open a workbook for editing.
FIGURE 3-2: Select a location to see its folders.
FIGURE 3-3: You can use the Open dialog box to select the workbook file you wan...
FIGURE 3-4: When you select Delete Cells, Excel asks how you want the remaining...
FIGURE 3-5: Worksheet after deleting the cell with the duplicate entry. (Sample...
FIGURE 3-6: Inserting three blank cells for missing entries in two columns of a...
FIGURE 3-7: The worksheet table after entering the missing entries in the newly...
FIGURE 3-8: The Income Analysis worksheet after zooming out to a 50% magnificat...
FIGURE 3-9: Worksheet at 232% magnification after zooming in on the cell range ...
FIGURE 3-10: The income worksheet after freezing column A and rows 1:2 in the w...
FIGURE 3-11: The income worksheet after zooming in on the cell range J20:L25 af...
FIGURE 3-12: The custom views you create appear in the Custom Views dialog box.
FIGURE 3-13: The Paste Option button’s palette with the option buttons that com...
FIGURE 3-14: The paste options in the Paste Special dialog box give you control...
FIGURE 3-15: Transposing a copy of the production schedule table so that dates ...
FIGURE 3-16: Finding a value in a worksheet by using the options on the Find ta...
FIGURE 3-17: Updating dates in a worksheet using the Find and Replace feature.
FIGURE 3-18: Spell checking a worksheet with the Spelling dialog box.
FIGURE 3-19: Using Data Validation to mark entries below a specified threshold ...

Book 2 Chapter 4

FIGURE 4-1: The Regional Income worksheet with the window divided into two hori...
FIGURE 4-2: The Regional Income worksheet with the window divided into two vert...
FIGURE 4-3: Splitting the worksheet window into four panes: two horizontal and ...
FIGURE 4-4: Automatic outline applied to the CG Music sales data with three lev...
FIGURE 4-5: The outline after applying automatic styles with the Settings dialo...
FIGURE 4-6: Collapsed worksheet outline showing first- and secondary-level summ...
FIGURE 4-7: Totally collapsed worksheet outline showing only the first-level su...
FIGURE 4-8: Worksheet outline expanded to show only details for Download sales ...
FIGURE 4-9: Manually adjusting the level 1 rows in the worksheet outline. (Samp...
FIGURE 4-10: Collapsing the adjusted worksheet outline to the first level summa...
FIGURE 4-11: Use Excel's View Side by Side command to compare data stored on tw...
FIGURE 4-12: The Workbook Statistics command tells you a few vital statistics f...
FIGURE 4-13: Using the Consolidate dialog box to total sales data for three yea...
FIGURE 4-14: The Consolidated worksheet after having Excel total sales from the...

Book 2 Chapter 5

FIGURE 5-1: You can use the Print screen in the Backstage view to customize you...
FIGURE 5-2: Viewing a worksheet in Page Layout view. (Sample workbook: Income A...
FIGURE 5-3: Page 1 of a ten-page report in Print Preview. (Sample workbook: Inc...
FIGURE 5-4: Page 1 of a ten-page report after selecting the Zoom to Page button...
FIGURE 5-5: You can adjust your report margins from the Margins tab in the Page...
FIGURE 5-6: Drag a marker to adjust its margin in the Page Preview window when ...
FIGURE 5-7: A landscape mode report in Page Layout view. (Sample workbook: Inco...
FIGURE 5-8: Specify the rows and columns to use as print titles on the Sheet ta...
FIGURE 5-9: Page 2 of a sample report in Print Preview with defined print title...
FIGURE 5-10: Clicking inside the header (or footer) displays the Header & Foote...
FIGURE 5-11: The first page of a report in Page Layout view shows you how the h...
FIGURE 5-12: A worksheet in Page Layout view showing the custom footer. (Sample...
FIGURE 5-13: Preview page breaks in a report in Page Break Preview. (Sample wor...
FIGURE 5-14: Page 1 of the report in the Print Preview window after adjusting t...

Book 3 Chapter 1

FIGURE 1-1: The formula result appears in the cell, while the formula itself ap...
FIGURE 1-2: You can use cell addresses as formula operands.
FIGURE 1-3: Using AutoSum to create a SUM formula that totals a column of numbe...
FIGURE 1-4: Using the AutoSum feature to create a SUM formula that totals a row...
FIGURE 1-5: Adding a row of totals with the Quick Analysis tool’s Sum button. (...
FIGURE 1-6: Use the Insert Function dialog box to, you know, insert a function.
FIGURE 1-7: Use the Function Arguments dialog box to specify the arguments for ...
FIGURE 1-8: Type the function name followed by the left parenthesis to see a Sc...
FIGURE 1-9: The Function Arguments dialog box shows the function syntax.
FIGURE 1-10: An original formula copied with the Fill handle across the last ro...
FIGURE 1-11: An original formula copied with the Fill handle down the last colu...
FIGURE 1-12: Copying the formula in cell B9 across produces #DIV/0! errors in t...
FIGURE 1-13: Using an absolute cell reference in the divisor gets rid of the er...
FIGURE 1-14: A worksheet with two different sizes of arrays.
FIGURE 1-15: The goal: to calculate each employee's hourly wages for the first ...
FIGURE 1-16: An array formula makes short work of hourly wage calculation. (Sam...
FIGURE 1-17: You can define a name using the Formula bar's Name box. (Sample wo...
FIGURE 1-18: You can use the New Name dialog box to define a new name.
FIGURE 1-19: In the Use in Formula list, click the name you want to paste into ...
FIGURE 1-20: Creating names from the row and column headings in a worksheet dat...
FIGURE 1-21: The Name Manager lists all names defined in the workbook.
FIGURE 1-22: Income statement with a resolvable circular reference.

Book 3 Chapter 2

FIGURE 2-1: Using the IF function to determine whether to charge tax on an item...
FIGURE 2-2: Using the IFS function to test multiple conditions in a single form...
FIGURE 2-3: Using the SWITCH function to match days of the week against a serie...
FIGURE 2-4: Blank 2023 Production Schedule spreadsheet that’s full of #DIV/0! e...
FIGURE 2-5: 2023 Production Schedule spreadsheet after trapping all the #DIV/0!...
FIGURE 2-6: Clicking the Trace Precedents command button shows the direct prece...
FIGURE 2-7: Clicking the Trace Precedents command button again shows the indire...
FIGURE 2-8: Clicking the Trace Precedents command button a third time shows a p...
FIGURE 2-9: Double-clicking the dotted tracer arrow opens the Go To dialog box ...
FIGURE 2-10: Clicking the Trace Dependents command button shows all the depende...
FIGURE 2-11: Flagging an error value in a worksheet in the Error Checking dialo...
FIGURE 2-12: Using the Trace Error option to show the precedents and dependents...
FIGURE 2-13: Calculating each part of a formula in the Evaluate Formula dialog ...

Book 3 Chapter 3

FIGURE 3-1: Using the DATE function to combine separate date information into a...
FIGURE 3-2: Using the NETWORKDAYS function to find the number of workdays betwe...

Book 3 Chapter 4

FIGURE 4-1: Using the PV function to calculate the present value of various inv...
FIGURE 4-2: Using the NPV function to calculate the net present value of an inv...
FIGURE 4-3: Loan Payments table using the PMT function to calculate various loa...
FIGURE 4-4: A Depreciation table showing 10-year depreciation of an asset using...

Book 3 Chapter 5

FIGURE 5-1: Rounding off the value of pi with the ROUND, ROUNDUP, and ROUNDDOWN...
FIGURE 5-2: Using SUMIF to total sales by items sold. (Sample workbook: Chris C...
FIGURE 5-3: Using SUMIFS to total sales by location as well as the items sold. ...
FIGURE 5-4: Home sales worksheet using common statistical functions. (Sample wo...
FIGURE 5-5: A version of the home sales worksheet with various counting formula...
FIGURE 5-6: Selecting a statistical analysis tool added by the Analysis ToolPak...

Book 3 Chapter 6

FIGURE 6-1: The XLOOKUP formula in cell F4 returns sales based on the country e...
FIGURE 6-2: The XLOOKUP formula in cell F4 returns the country based on the sal...
FIGURE 6-3: A formula with nested XLOOKUP functions to return the number of uni...
FIGURE 6-4: Using the VLOOKUP function to return the amount of the tip to add f...
FIGURE 6-5: Using the HLOOKUP function to return the price of a bakery item fro...
FIGURE 6-6: Doing a two-way lookup in the Production Schedule table. (Sample wo...
FIGURE 6-7: Using the TRANSPOSE function to change the orientation of a simple ...
FIGURE 6-8: Using the PROPER function to convert names in all uppercase letters...
FIGURE 6-9: Worksheet after replacing names in all uppercase letters with prope...
FIGURE 6-10: Using the TEXTJOIN function to create a CSV file for export where ...
FIGURE 6-11: Worksheet after concatenating the first and last names in column C...

Book 4 Chapter 1

FIGURE 1-1: Setting a password to open and modify the workbook in the General O...
FIGURE 1-2: Entering the password required to open a protected workbook file.
FIGURE 1-3: Use the Protect Sheet dialog box to select your worksheet protectio...
FIGURE 1-4: Designating the range to be unlocked by a password in a protected w...
FIGURE 1-5: Assigning the range title, address, and password in the New Range d...
FIGURE 1-6: Setting the permissions for each user in the Permissions dialog box...
FIGURE 1-7: Getting ready to protect the sheet in the Allow Users to Edit Range...
FIGURE 1-8: Using the Tab key to move from unlocked cell to unlocked cell in a ...
FIGURE 1-9: Protecting a workbook in the Protect Structure and Windows dialog b...

Book 4 Chapter 2

FIGURE 2-1: Checking a workbook’s properties in its Info screen in the Excel Ba...
FIGURE 2-2: Filling out the signing information in the Signature Setup dialog b...
FIGURE 2-3: Filling in the signature in the Sign dialog box.
FIGURE 2-4: The workbook after adding the digital signature line graphic with t...
FIGURE 2-5: Adding notes to various cells of a worksheet. (Sample workbook: Inc...
FIGURE 2-6: Adding inked notes to a worksheet with my finger on a Windows table...

Book 4 Chapter 3

FIGURE 3-1: Use the Send Link dialog box to invite coworkers or clients to shar...
FIGURE 3-2: Use the Link Settings dialog box to customize the link you're shari...
FIGURE 3-3: When you click Copy Link, Excel copies the link address to the Clip...
FIGURE 3-4: Dragging the cell range A1:B23 from the Historical Sales worksheet ...
FIGURE 3-5: Word memo after copying the worksheet data.
FIGURE 3-6: With the copy-and-paste method, use the Paste Special dialog box to...
FIGURE 3-7: Editing the embedded worksheet sales data from within the Word memo...
FIGURE 3-8: To link data, select the Paste Link option in Word’s Paste Special ...
FIGURE 3-9: A linked chart pasted into the Word memo.
FIGURE 3-10: Editing an embedded Excel chart in a PowerPoint slide.
FIGURE 3-11: Use the Alt Text pane to compose your alternative text for a visua...
FIGURE 3-12: If the Accessibility Checker discovers one or more ways you can ma...
FIGURE 3-13: Use this dialog box to type and post your comment.
FIGURE 3-14: Use this dialog box to type and post a comment reply.

Book 5 Chapter 1

FIGURE 1-1: A typical clustered column chart containing a variety of standard c...
FIGURE 1-2: Insert Chart dialog box with the Recommended Charts tab selected. (...
FIGURE 1-3: Open the Insert Chart dialog box and then use the All Charts tab to...
FIGURE 1-4: Previewing the embedded chart to insert from the Quick Analysis too...
FIGURE 1-5: Clustered column chart created on its own chart sheet. (Sample work...
FIGURE 1-6: Clustered column chart on its own chart sheet after selecting a new...
FIGURE 1-7: The clustered column chart after switching the columns and rows. (S...
FIGURE 1-8: Using the Select Data Source dialog box to remove the empty Genre l...
FIGURE 1-9: Repositioning the chart legend in the example clustered column usin...
FIGURE 1-10: Clustered column chart on its own Chart sheet with data table and ...
FIGURE 1-11: Formatting the Chart Title with the options in the Format Chart Ti...
FIGURE 1-12: Formatting the Vertical (Value) Axis with the options in the Forma...
FIGURE 1-13: Sparklines graphics representing the variation in the data in a wo...
FIGURE 1-14: Adding a Bing Maps infographic to illustrate the 2021 population b...
FIGURE 1-15: Adding a People Graph infographic to illustrate the 2021 populatio...
FIGURE 1-16: Adding a Filled Map chart infographic to illustrate the 2021 popul...

Book 5 Chapter 2

FIGURE 2-1: When you click a graphic object to select it, the rotation and circ...
FIGURE 2-2: Graphic objects on top obscure any worksheet data and parts of othe...
FIGURE 2-3: The worksheet after moving the hundred-dollar bill graphic to the t...
FIGURE 2-4: Grouping the hundred-dollar bill photo and its caption to turn them...
FIGURE 2-5: Selecting a web image to download into your worksheet.
FIGURE 2-6: Selecting an online 3-D model to insert into your worksheet.
FIGURE 2-7: Selecting a local image to insert into your worksheet.
FIGURE 2-8: Use this box to select from a large collection of ready-to-use imag...
FIGURE 2-9: Selecting the text style for the new WordArt text from its drop-dow...
FIGURE 2-10: Select the SmartArt list, diagram, or picture to insert in the wor...
FIGURE 2-11: Adding text for a new organizational chart in the SmartArt text pa...

Book 6 Chapter 1

FIGURE 1-1: Creating an employee table with the row of field names and first da...
FIGURE 1-2: Opening the data form in the new table to add a new record. (Sample...
FIGURE 1-3: Using the Remove Duplicates dialog box to remove duplicate records ...
FIGURE 1-4: Set up to sort records alphabetically by location and salary. (Samp...
FIGURE 1-5: Employee table sorted by location and salary. (Sample workbook: Emp...
FIGURE 1-6: The Sort dialog box with five levels of sorting keys for sorting th...
FIGURE 1-7: The Personnel table after sorting by department, supervisor, last, ...
FIGURE 1-8: Using the Subtotal dialog box to subtotal the salaries for each dep...
FIGURE 1-9: Bottom of the table showing the subtotals and grand total for depar...

Book 6 Chapter 2

FIGURE 2-1: The employee table after filtering the Dept and Location fields. (S...
FIGURE 2-2: Using the Top 10 Items AutoFilter to filter out all records except ...
FIGURE 2-3: Using Custom AutoFilter to filter out records except for those with...
FIGURE 2-4: Using Advanced Filter to copy records that meet the criteria in the...
FIGURE 2-5: Copied records where the location is Boston and the date hired is p...
FIGURE 2-6: Copied records where the location is Boston and the date hired prio...
FIGURE 2-7: Copied records extracted from the table for employees whose salarie...
FIGURE 2-8: Using DSUM to total the salaries over $55,000 in the Employee table...
FIGURE 2-9: Using the Navigator to select which data tables and queries from th...
FIGURE 2-10: Customers worksheet with the data imported from the Access Custome...
FIGURE 2-11: Orders worksheet with the data imported from the Orders data table...
FIGURE 2-12: Importing the data in a tab-delimited text file into a worksheet u...
FIGURE 2-13: Creating a new query in the Power Query Editor using a data table ...
FIGURE 2-14: Setting the filtering and sorting criteria for the new data query ...
FIGURE 2-15: The Contoso Stores data queried in the Power Query Editor after lo...

Book 7 Chapter 1

FIGURE 1-1: Creating a one-variable data table. (Sample workbook: Data Table 1 ...
FIGURE 1-2: The completed one-variable data table. (Sample workbook: Data Table...
FIGURE 1-3: Creating a two-variable data table. (Sample workbook: Data Table 2 ...
FIGURE 1-4: The completed two-variable data table. (Sample workbook: Data Table...
FIGURE 1-5: Adding a new Most Likely scenario for the sales forecast. (Sample w...
FIGURE 1-6: Specifying the changing values in the Scenario Values dialog box. (...
FIGURE 1-7: Worksheet after showing the Worst Case scenario. (Sample workbook: ...
FIGURE 1-8: Designating the result cells in the Scenario Summary dialog box. (S...
FIGURE 1-9: A Scenario Summary report for the scenarios in the Sales Forecast t...
FIGURE 1-10: Using goal seeking to find out how much sales must increase to rea...
FIGURE 1-11: A worksheet showing a goal-seeking solution and the Goal Seek Stat...
FIGURE 1-12: Specifying the parameters to apply to the model in Solver Paramete...
FIGURE 1-13: The Solver Results dialog box showing that Solver found a solution...
FIGURE 1-14: Modifying the solution options in the Options dialog box.

Book 7 Chapter 2

FIGURE 2-1: Previewing the PivotTable created from the selected data in the Qui...
FIGURE 2-2: Previewed PivotTable created on a new worksheet with the Quick Anal...
FIGURE 2-3: Creating a new PivotTable from the sample PivotTables displayed in ...
FIGURE 2-4: Indicate the data source and PivotTable location in the Create Pivo...
FIGURE 2-5: A new PivotTable displaying the blank table grid and the PivotTable...
FIGURE 2-6: A completed PivotTable after adding the fields from the employee ta...
FIGURE 2-7: The revised PivotTable in the Outline Form with an extra blank row ...
FIGURE 2-8: The PivotTable after filtering the Gender Filter Report field and t...
FIGURE 2-9: Employee PivotTable showing the men’s salaries in the Engineering d...
FIGURE 2-10: Employee PivotTable using a timeline filter to show the salaries f...
FIGURE 2-11: Pivoting the table so that Dept is now the Column Labels Field and...
FIGURE 2-12: The PivotTable after adding Profit Sharing as another Report Filte...
FIGURE 2-13: Creating a calculated field for a PivotTable.
FIGURE 2-14: Modifying the PivotTable options in the PivotTable Options dialog ...
FIGURE 2-15: Clustered column PivotChart moved to its own PivotChart sheet. (Sa...
FIGURE 2-16: The Power Pivot window with tabs for all the data tables imported ...
FIGURE 2-17: Switching from Data View to Diagram View in the Power Pivot for Ex...
FIGURE 2-18: 3D Maps window with new Clustered Column 3-D animation for visuali...
FIGURE 2-19: Defining the options for a new line chart that shows historical an...
FIGURE 2-20: Worksheet with embedded line chart depicting historical and foreca...

Book 8 Chapter 1

FIGURE 1-1: Getting ready to record the CompanyName macro in the Record Macro d...
FIGURE 1-2: Selecting the macro to run in the Macro dialog box.

Book 8 Chapter 2

FIGURE 2-1: The Visual Basic Editor window as it normally appears when editing ...
FIGURE 2-2: The buttons on the Visual Basic Editor’s Standard toolbar.
FIGURE 2-3: Using the Find feature to locate the code to edit in the Code windo...
FIGURE 2-4: The CompanyName Code window after adding variables and the InputBox...
FIGURE 2-5: The Worksheet Title dialog box that appears when you run the Compan...
FIGURE 2-6: Entering the Commission user-defined function in the Personal Macro...
FIGURE 2-7: Adding a description for the Commission user-defined function.
FIGURE 2-8: Selecting a cell in the RV Sales worksheet into which to enter the ...
FIGURE 2-9: Specifying the TotalSales and ItemsSold arguments in the RV Sales w...
FIGURE 2-10: Completed worksheet for all salespeople computed with the Commissi...
FIGURE 2-11: Protecting the VBA project so that its user-defined functions can’...
FIGURE 2-12: Activating the new add-in file in the Add-Ins dialog box.

Introduction

Excel All-in-One For Dummies brings together plain and simple information on using all aspects of Microsoft Excel. It’s designed to be of help no matter how much or how little experience you have with the program. As the preeminent spreadsheet and data analysis software for all sorts of computing devices running Windows (desktops, laptops, tablets, and even smartphones), Excel offers its users seemingly unlimited capabilities too often masked in technical jargon and obscured by explanations only a software engineer could love. On top of that, many of the publications that purport to give you the lowdown on using Excel are quite clear on how to use particular features without giving you a clue as to why you would want to go to all the trouble.

The truth is that understanding how to use the abundance of features offered by Excel is only half the battle, at best. The other half of the battle is to understand how these features can benefit you in your work; in other words, “what’s in it for you.” I have endeavored to cover both the “how to” and “so what” aspects in all my discussions of Excel features, being as clear as possible and using a minimum of tech-speak.

Fortunately, Excel is well worth the effort to get to know because it’s definitely one of the best data-processing and analysis tools that has ever come along. From let-Excel-do-all-the-work features such as Flash Fill and Analyze Data to eyebrow-raisingly powerful tools such as PivotTables and Solver, Excel has whatever you need to get your numbers crunched. Excel is a blast to use when you know what you’re doing, and my great hope is that this “fun” aspect of using the program comes through on every page (or, at least, every other page).

About This Book

As the name states, Excel All-in-One For Dummies is a reference (whether you keep it on your desk or use it to prop up your desk is your business). This means that although the chapters in each book are laid out in a logical order, each stands on its own, ready for you to dig into the information at any point.

As much as possible, I have endeavored to make the topics within each book and chapter stand on their own. When there’s just no way around relying on some information that’s discussed elsewhere, I include a cross-reference that gives you the chapter and verse (actually the book and chapter) for where you can find that related information if you’re of a mind to.

Use the full Table of Contents and Index to look up the topic of the hour and find out exactly where it is in this compilation of Excel information. You’ll find that although most topics are introduced in a conversational manner, I don’t waste much time cutting to the chase by laying down the main principles at work (usually in bulleted form) followed by the hard reality of how you do the deed (as numbered steps).

Foolish Assumptions

I’m only going to make one foolish assumption about you, and that is that you have some need to use Microsoft Excel under Windows in your work or studies. If pushed, I further guess that you aren’t particularly interested in knowing Excel at an expert level but are terribly motivated to find out how to do the stuff you need to get done. If that’s the case, this is definitely the book for you. Fortunately, even if you happen to be one of those newcomers who’s highly motivated to become the company’s resident spreadsheet guru, you’ve still come to the right place.

As far as your hardware and software go, I’m assuming that you already have Excel installed on your computing device running under Windows. 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.

I’m not assuming, however, that when you’re using Excel that you’re sitting in front of a large screen monitor and making cell entries and command selections with a physical keyboard or connected mouse. With the introduction of Microsoft’s Surface 4 tablet and the support for a whole slew of different Windows tablets, you may well be entering data and selecting commands with your finger or stylus using the Windows Touch keyboard and Touch pointer.

To deal with the differences between using Excel on a standard desktop or laptop computer with access only to a physical keyboard and mouse and a touchscreen tablet or smartphone environment with access only to the virtual Touch keyboard, I’ve outlined the touchscreen equivalents to common commands you find throughout the text, such as “click,” “double-click,” “drag,” and so forth, in the section that explains selecting by touch in Book 1, Chapter 1.

Warning: Because of the diversity of the devices that Excel runs on and the places where its files can be saved and used, if you’re using Excel 2007 or Excel 2010 for Windows, much of the file-related information in this book may only confuse and confound you. If you’re still using a version prior to Excel 2007, which introduced the Ribbon interface, this edition will be of no use to you because your version of the program works nothing like what this book describes.

Conventions Used in This Book

This book follows a number of different conventions modeled primarily after those used by Microsoft in its various online articles and help materials. These conventions deal primarily with Ribbon command sequences and shortcut or hot key sequences that you encounter.

Excel is a sophisticated program that uses the Ribbon interface first introduced in Excel 2007. In Chapter 1, I explain all about this Ribbon interface and how to get comfortable with its command structure. Throughout the book, you may find Ribbon command sequences using the shorthand developed by Microsoft whereby the name on the tab on the Ribbon and the command button you select are separated by arrows, as in

Home ⇒ Copy

This is shorthand for the Ribbon command that copies whatever cells or graphics are currently selected to the Windows Clipboard. It means that you click the Home tab on the Ribbon (if it’s not already displayed) and then click the Copy button, which sports the traditional side-by-side page icon.

Some of the Ribbon command sequences involve not only selecting a command button on a tab but then also selecting an item on a drop-down menu. In this case, the drop-down menu command follows the name of the tab and command button, all separated by arrows, as in

Formulas ⇒ Calculation Options ⇒ Manual

This is shorthand for the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the Formulas tab (if it’s not already displayed) and then click the Calculation Options command button followed by the Manual drop-down menu option.

The book occasionally encourages you to type something specific into a specific cell in the worksheet. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letter-number combos), and a right parenthesis. You then, of course, still have to press the Enter key or click the Enter button on the Formula bar to make the entry stick.

When Excel isn’t talking to you by popping up message boxes, it displays highly informative messages in the Status bar at the bottom of the screen. This book renders messages that you see onscreen like this:

CALCULATE

This is the message that tells you that Excel is in manual recalculation mode (after using the earlier Ribbon command sequence) and that one or more of the formulas in your worksheet are not up to date and are in sore need of recalculation.

Occasionally I give you a shortcut key combination that you can press to choose a command from the keyboard rather than clicking buttons on the Ribbon with the mouse. Shortcut key combinations are written like this: Alt+FS or Ctrl+S. (Both of these shortcut key combos save workbook changes.)

With the Alt key combos, you press the Alt key until the shortcut key letters appear in little squares all along the Ribbon. At that point, you can release the Alt key and start pressing the shortcut key letters. (By the way, these are all lowercase shortcut key letters — I only put them in caps to make them stand out in the text.)

Shortcut key combos that use the Ctrl key are of an older vintage, and they work a little bit differently because, on a physical keyboard, you have to hold down the Ctrl key as you press the shortcut key letter. (Again, unless you see the Shift key in the sequence as in Ctrl+Shift+C, don’t worry about uppercase letters.)

Finally, if you're really observant, you may notice a discrepancy between the capitalization of the names of dialog box options (such as headings, option buttons, and check boxes) as they appear in the book and how they actually appear in Excel on your computer screen. I intentionally use the convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.

Icons Used in This Book

The following icons are strategically placed in the margins throughout all eight books in this volume. Their purpose is to get your attention, and each has its own way of doing that.

This icon denotes some really cool information (in my humble opinion) that will pay off by making your work a lot more enjoyable or productive (or both).

This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you may end up taking a detour that wastes valuable time.

This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you’ll be sorry. I reserve this icon for those times when you can lose data and otherwise screw up your spreadsheet.

This icon denotes a tidbit that makes free use of (oh no!) technical jargon. You may want to skip these sections (or, at least, read them when no one else is around).

Beyond the Book

Examples: This book’s sample Excel workbooks can be found at www.wiley.com/go/excel2022aiofd or at my website (www.paulmcfedries.com). The filenames of the workbooks used in the book's examples are given in the text.
Cheat Sheet: To locate this book's Cheat Sheet, go to www.dummies.com and search for Excel All-in-One For Dummies. See the Cheat Sheet for info on Excel shortcut keys, keyboard cell-selection techniques, setting regional dates, and sharing workbooks from OneDrive.

Where to Go from Here

The question of where to go from here couldn’t be simpler: Go to Book 1, Chapter 1 and find out what you’re dealing with. Which book you go to after that is a matter of personal interest and need. Just go for the gold and don’t forget to have some fun while you’re digging!

Book 1

Excel Basics

Contents at a Glance

Chapter 1: Excel: The 50-Cent Tour
1. A Bird's-Eye View of Excel
2. Checking Out Excel’s Start Screen
3. Getting Comfy with the Excel User Interface
4. Getting Help
5. Launching and Quitting Excel
Chapter 2: Customizing Excel
1. Tailoring the Quick Access Toolbar to Your Tastes
2. Exercising Your Options
3. Using Office Add-ins
4. Using Excel’s Own Add-ins