Microsoft Excel

Step by Step
(Office 2021 and Microsoft 365)

Joan Lambert
Curtis Frye
Microsoft Excel Step by Step (Office 2021 and Microsoft 365) Editor-in-Chief
Published with the authorization of Microsoft Corporation by: Brett Bartow
Pearson Education, Inc.
Executive Editor
Copyright © 2022 by Pearson Education, Inc. Loretta Yates

All rights reserved. This publication is protected by copyright, and permission Sponsoring Editor
must be obtained from the publisher prior to any prohibited reproduction, Charvi Arora
storage in a retrieval system, or transmission in any form or by any means,
electronic, mechanical, photocopying, recording, or likewise. For information Development Editor
regarding permissions, request forms, and the appropriate contacts within Kate Shoup
the Pearson Education Global Rights & Permissions Department, please visit
[Link]/permissions. Managing Editor
Sandra Schroeder
No patent liability is assumed with respect to the use of the information contained
herein. Although every precaution has been taken in the preparation of this book, Senior Project Editor
the publisher and author assume no responsibility for errors or omissions. Nor Tracey Croom
is any liability assumed for damages resulting from the use of the information
contained herein. Project Editor/Copy Editor
Dan Foster
ISBN-13: 978-0-13-756427-9
ISBN-10: 0-13-756427-9 Technical Editor
Laura Acklen
Library of Congress Control Number: 2021949813
Indexer
ScoutAutomatedPrintCode Valerie Haynes Perry

Trademarks Proofreader
Microsoft and the trademarks listed at [Link] on the Susan Festa
“Trademarks” webpage are trademarks of the Microsoft group of companies.
All other marks are property of their respective owners. Editorial Assistant
Cindy Teeters
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as Cover Designer
possible, but no warranty or fitness is implied. The information provided is on Twist Creative, Seattle
an “as is” basis. The author, the publisher, and Microsoft Corporation shall have
neither liability nor responsibility to any person or entity with respect to any loss Compositor
or damages arising from the information contained in this book or from the use of Danielle Foster
the programs accompanying it.

Special Sales
For information about buying this title in bulk quantities, or for special
sales opportunities (which may include electronic versions; custom cover
designs; and content particular to your business, training goals, marketing
focus, or branding interests), please contact our corporate sales department
at corpsales@[Link] or (800) 382-3419.

For government sales inquiries, please contact governmentsales@[Link].

For questions about sales outside the U.S., please contact intlcs@[Link].
Pearson’s Commitment to Diversity, Equity, and Inclusion
Pearson is dedicated to creating bias-free content that reflects the diversity of all
learners. We embrace the many dimensions of diversity, including but not limited
to race, ethnicity, gender, socioeconomic status, ability, age, sexual orientation, and
religious or political beliefs.

Education is a powerful force for equity and change in our world. It has the potential
to deliver opportunities that improve lives and enable economic mobility. As we work
with authors to create content for every product and service, we acknowledge our
responsibility to demonstrate inclusivity and incorporate diverse scholarship so that
everyone can achieve their potential through learning. As the world’s leading learn-
ing company, we have a duty to help drive change and live up to our purpose to help
more people create a better life for themselves and to create a better world.

Our ambition is to purposefully contribute to a world where:

■ Everyone has an equitable and lifelong opportunity to succeed through learning.

■ Our educational products and services are inclusive and represent the rich
diversity of learners.
■ Our educational content accurately reflects the histories and experiences of the
learners we serve.
■ Our educational content prompts deeper discussions with learners and moti-
vates them to expand their own learning (and worldview).

While we work hard to present unbiased content, we want to hear from you about
any concerns or needs with this Pearson product so that we can investigate and
address them.

Please contact us with concerns about any potential bias at

[Link]
Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii
About the author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii

i
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Who this book is for . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
The Step by Step approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Features and conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv
Download the practice files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Sidebar: Adapt exercise steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx
E-book edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii
Get support and give feedback . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii
Errata and support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii
Stay in touch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii

Part 1: Create and format workbooks

1
Set up a workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Create workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Modify workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Modify worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Merge and unmerge cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Customize the Excel app window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Manage the ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Manage the Quick Access Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Customize the status bar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Change the magnification level of a worksheet . . . . . . . . . . . . . . . . . . . . . . . . . 35
Arrange multiple workbook windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36

v
 Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

2
Work with data and Excel tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Enter and revise data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Manage data by using Flash Fill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Move data within a workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Sidebar: Quickly access data-formatting commands . . . . . . . . . . . . . . . . . . . . 52
Find and replace data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Correct and fine-tune data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Define Excel tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

3
Perform calculations on data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Name data ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Sidebar: Operators and precedence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Create formulas to calculate values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Summarize data that meets specific conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Copy and move formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Create array formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Find and correct errors in calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Configure automatic and iterative calculation options . . . . . . . . . . . . . . . . . . . . . . 96
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

vi
 4
Change workbook appearance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .103
Format cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Define and manage cell styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
Apply and modify workbook themes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
Apply and modify table styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
Make numbers easier to read . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .119
Change the appearance of data based on its value . . . . . . . . . . . . . . . . . . . . . . . . 122
Add images to worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133

Part 2: Analyze and present data

5
Manage worksheet data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Filter data ranges and tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Summarize filtered data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Sidebar: Randomly select list rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Enforce data entry criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157

6
Reorder and summarize data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .159
Sort worksheet data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Sort data by using custom lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Outline and subtotal data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173

vii
 7
Combine data from multiple sources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Look up data from other locations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Locate information in the same row (VLOOKUP) . . . . . . . . . . . . . . . . . . . . . . . 177
Locate information in the same column (HLOOKUP) . . . . . . . . . . . . . . . . . . . 179
Locate information anywhere (XLOOKUP) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Link to data in other locations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Consolidate multiple sets of data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .191

8
Analyze alternative data sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .193
Define and display alternative data sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Forecast data by using data tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Identify the input necessary to achieve a specific result . . . . . . . . . . . . . . . . . . . . 202
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205

9
Create charts and graphics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Create standard charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Create combo charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Create specialized charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Hierarchy charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Statistic charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Scatter charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Stock charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Map charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Customize chart appearance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Identify data trends . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Summarize data by using sparklines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Illustrate processes and relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238

viii
 Insert and manage shapes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Sidebar: Insert mathematical equations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250

10
Create PivotTables and PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Analyze data dynamically in PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Filter, show, and hide PivotTable data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Edit PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Format PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Create dynamic PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281

Part 3: Collaborate and share in Excel

11
Print worksheets and charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Add headers and footers to printed pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
Prepare worksheets for printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Fit your worksheet contents to the printed page. . . . . . . . . . . . . . . . . . . . . . . 292
Change page breaks in a worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Change the page printing order for worksheets . . . . . . . . . . . . . . . . . . . . . . . 297
Print worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Print parts of worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Print charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305

ix
 12
Automate tasks and input . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Enable and examine macros. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Set macro security levels in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Examine macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Create and modify macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Run macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Assign a macro to a Quick Access Toolbar button . . . . . . . . . . . . . . . . . . . . . . 318
Assign a macro to a shape . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
Run a macro when a workbook opens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Present information and options as form controls . . . . . . . . . . . . . . . . . . . . . . . . . 322
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333

13
Work with other Microsoft 365 apps . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
Combine Excel, Word, and PowerPoint content. . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Link from Excel to a document or presentation . . . . . . . . . . . . . . . . . . . . . . . . 336
Embed file content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Create hyperlinks from worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Copy or link to charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349

14
Collaborate with colleagues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .351
Manage comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Protect workbooks and worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Finalize workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
Save workbook content as a PDF file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
Create and distribute workbook templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

x
Part 4: Perform advanced analysis

15
Perform business intelligence analysis . . . . . . . . . . . . . . . . . . . . . . . . . . 373
Manage the Excel Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
Sidebar: Power View data visualizations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Define relationships between data sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Manage data by using Power Pivot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Display data on timelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Import data by using Power Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

16
Create forecasts and visualizations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
Create forecast worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .404
Linear forecasting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .404
Exponential smoothing forecasting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .404
Define and manage measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Define and display key performance indicators . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Create 3D data maps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
Skills review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Practice tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422

Appendix Keyboard Shortcuts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425

Excel for Microsoft 365 keyboard shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . 425
Excel function key commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432
Microsoft 365 app keyboard shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434
Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447

xi
Acknowledgments
Every book represents the combined efforts of many individuals. Curt Frye wrote
the original versions of this book and provided a solid starting point for this edition.
I’m thankful to Loretta Yates for the opportunity to provide readers with informa-
tion about Excel 2021 and Excel for Microsoft 365, and to Charvi Arora for keeping
things on track. Kate Shoup and Laura Acklen provided valuable developmental and
technical feedback. It was a pleasure to work once again with Dan Foster, who did so
much more than simply copyedit this book and contributed greatly to the quality of
the content. Thanks also to Danielle Foster for laying out the content, Scout Festa for
proofreading it, and Valerie Haynes Perry for indexing it.

As always, many thanks and all my love to my divine daughter, Trinity Preppernau.

About the author

Joan Lambert has worked closely with Microsoft technologies
since 1986, and in the training and certification industry since 1997,
guiding the translation of technical information and requirements
into useful, relevant, and measurable resources for people seek-
ing certification of their computer skills or who simply want to get
things done efficiently. She has written more than 50 books about
Windows, Office, and SharePoint technologies, including dozens
of Step by Step books and five generations of Microsoft Office Specialist certification
study guides. Students who use the GO! with Microsoft Office textbook products may
overhear her cheerfully demonstrating Office features in the videos that accompany
the series.

A native of the Pacific Northwest, Joan has had the good fortune to live in many parts
of the world. She currently resides with her family—one daughter, two dogs, two cats,
and seven chickens—in the Beehive State, where she enjoys the majestic mountain
views every day…from her office chair.
Introduction i
Welcome! This Step by Step book has been designed so you can read it from the begin-
ning to learn about Excel for Microsoft 365 (or Microsoft Excel 2021) and then build your
skills as you learn to perform increasingly specialized procedures. Or, if you prefer, you
can jump in wherever you need guidance for performing tasks. The how-to steps are
delivered crisply and concisely—just the facts. You’ll also find informative graphics that
support the instructional content.

Who this book is for

Microsoft Excel Step by Step (Office 2021 and Microsoft 365) is designed for use as a learn-
ing and reference resource by people who want to use Excel to manage data, perform
calculations, create useful analyses and visualizations, generate forecasts, and discover
insights into their operations. The book content is designed to be useful for people who
are upgrading from earlier versions of Excel and for people who are discovering Excel for
the first time.

The Step by Step approach

The book’s coverage is divided into parts representing general Excel skill sets. Each part
is divided into chapters representing skill set areas, and each chapter is divided into
topics that group related skills. Each topic includes expository information followed
by generic procedures. At the end of the chapter, you’ll find a series of practice tasks
you can complete on your own by using the skills taught in the chapter. You can use
the practice files available from this book’s website to work through the practice tasks,
or you can use your own files.

xiii
Introduction

Features and conventions

This book has been designed to lead you step by step through tasks you’re likely to
want to perform in Excel. The topics are all self-contained, so you can start at the
beginning and work your way through all the procedures or reference them indepen-
dently. If you have worked with a previous version of Excel, or if you complete all the
exercises and later need help remembering how to perform a procedure, the follow-
ing features of this book will help you locate specific information:

■ Detailed table of contents Browse the listing of the topics, sections, and
sidebars within each chapter.
■ Chapter thumb tabs and running heads Identify the pages of each chapter
by the thumb tabs on the book’s open fore edge. Find a specific chapter by
number or title by looking at the running heads at the top of even-numbered
(verso) pages.
■ Topic-specific running heads Within a chapter, quickly locate the topic you
want by looking at the running heads at the top of odd-numbered (recto) pages.
■ Practice tasks page tabs Easily locate the practice tasks at the end of each
chapter by looking for the full-page stripe on the book’s fore edge.
■ Detailed index Look up coverage of specific tasks and features in the index at
the back of the book.

You can save time when reading this book by understanding how the Step by Step
series provides procedural instructions and auxiliary information and identifies on-
screen and physical elements that you interact with. The following table lists content
formatting conventions used in this book.

Convention Meaning
TIP This reader aid provides a helpful hint or shortcut to simplify a task.

IMPORTANT This reader aid alerts you to a common problem or provides

information necessary to successfully complete a procedure.

SEE ALSO This reader aid directs you to more information about a topic in this
book or elsewhere.

1. Numbered steps Numbered steps guide you through generic procedures in each
topic and hands-on practice tasks at the end of each chapter.

xiv
 Introduction

Convention Meaning
■ Bulleted lists Bulleted lists indicate single-step procedures and sets of multiple
alternative procedures.

Interface objects In procedures and practice tasks, semibold black text indicates
on-screen elements that you should select (click or tap).

User input Light semibold formatting identifies specific information that you
should enter when completing procedures or practice tasks.

Ctrl+P A plus sign between two keys indicates that you must select those
keys at the same time. For example, “press Ctrl+P” directs you to
hold down the Ctrl key while you press the P key.

Emphasis and URLs In expository text, italic formatting identifies web addresses and
words or phrases we want to emphasize.

Download the practice files

Before you can complete the practice tasks in this book, you must download
the book’s practice files to your computer from [Link]/
Excel365stepbystep/downloads. Follow the instructions on the webpage.

IMPORTANT Excel 2021 and other Microsoft 365 apps are not available from the
book’s website. You should install Excel before working through the procedures and
practice tasks in this book.

You can open the files that are supplied for the practice tasks and save the finished
versions of each file. If you want to repeat practice tasks later, you can download the
original practice files again.

SEE ALSO For information about opening and saving files, see “Create workbooks” in
Chapter 1, “Set up a workbook.”

xv
Introduction

The following table lists the files available for use while working through the practice
tasks in this book.

Chapter Folder File

Part 1: Create and format workbooks

1: Set up a workbook Excel365SBS\Ch01 [Link]

[Link]
[Link]
[Link]
[Link]

2: Work with data and Excel tables Excel365SBS\Ch02 [Link]

[Link]
[Link]
[Link]
[Link]
[Link]

3: Perform calculations on data Excel365SBS\Ch03 [Link]

[Link]
[Link]
[Link]
[Link]
[Link]

4: Change workbook appearance Excel365SBS\Ch04 [Link]

[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]

xvi
 Introduction

Chapter Folder File

Part 2: Analyze and present data

5: Manage worksheet data Excel365SBS\Ch05 [Link]

[Link]
[Link]

6: Reorder and summarize data Excel365SBS\Ch06 [Link]

[Link]
[Link]

7: Combine data from multiple Excel365SBS\Ch07 [Link]

sources [Link]
[Link]
[Link]

8: Analyze alternative data sets Excel365SBS\Ch08 [Link]

[Link]
[Link]

9: Create charts and graphics Excel365SBS\Ch09 [Link]

[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]

10: Create PivotTables and Excel365SBS\Ch10 [Link]

PivotCharts [Link]
[Link]
[Link]
[Link]

xvii
Introduction

Chapter Folder File

Part 3: Collaborate and share in Excel

11: Print worksheets and charts Excel365SBS\Ch11 [Link]

[Link]
[Link]
[Link]
[Link]
[Link]

12: Automate tasks and input Excel365SBS\Ch12 [Link]

[Link]
[Link]
[Link]

13: Work with other Microsoft Excel365SBS\Ch13 [Link]

365 apps [Link]
[Link]
[Link]
[Link]
[Link]
[Link]

14: Collaborate with colleagues Excel365SBS\Ch14 [Link]

[Link]
[Link]
[Link]
[Link]

xviii
 Introduction

Chapter Folder File

Part 4: Perform advanced analysis

15: Perform business intelligence Excel365SBS\Ch15 [Link]

analysis [Link]
[Link]
[Link]
[Link]
[Link]

16. Create forecasts and Excel365SBS\Ch16 [Link]

visualizations [Link]
[Link]
[Link]

xix
Introduction

Adapt exercise steps

This book contains many images of the Excel user interface elements (such as the
ribbon and the app window) that you’ll work with while performing tasks in Excel on
a Windows computer. Unless we’re demonstrating an alternative view of content, the
screenshots shown in this book were captured on a horizontally oriented display at a
screen resolution of 1920 × 1080 and a magnification of 100 percent. If your settings
are different, the ribbon on your screen might not look the same as the one shown
in this book. As a result, exercise instructions that involve the ribbon might require a
little adaptation.

Simple procedural instructions use this format:

■ On the Insert tab, in the Illustrations group, select the Chart button.

If the command is in a list, our instructions use this format:

1. On the Home tab, in the Editing group, select the Find arrow and then, in the
Find list, select Go To.

If differences between your display settings and ours cause a button to appear differ-
ently on your screen than it does in this book, you can easily adapt the steps to locate
the command. First select the specified tab, and then locate the specified group. If a
group has been collapsed into a group list or under a group button, select the list or
button to display the group’s commands. If you can’t immediately identify the button
you want, point to likely candidates to display their names in ScreenTips.

xx
 Introduction

Multistep procedural instructions use this format:

1. To select the paragraph that you want to format in columns, triple-click the
paragraph.

2. On the Layout tab, in the Page Setup group, select the Columns button to
display a menu of column layout options.

3. On the Columns menu, select Three.

On subsequent instances of instructions that require you to follow the same process,
the instructions might be simplified in this format because the working location has
already been established:

1. Select the paragraph that you want to format in columns.

2. On the Columns menu, select Three.

The instructions in this book assume that you’re selecting on-screen content and user
interface elements on your computer by clicking (with a mouse, touchpad, or other
hardware device) or tapping a touchpad or the screen (with your finger or a stylus).
Instructions refer to Excel user interface elements that you click or tap on the screen
as buttons, and to physical buttons that you press on a keyboard as keys, to conform
to the standard terminology used in documentation for these products.

When the instructions tell you to enter information, you can do so by typing on a con-
nected external keyboard, tapping an on-screen keyboard, or even speaking aloud,
depending on your computer setup and your personal preferences.

xxi
Introduction

E-book edition
If you’re reading the e-book edition of this book, you can do the following:

■ Search the full text

■ Print
■ Copy and paste

You can purchase and download the e-book edition from the Microsoft Press Store
at [Link]/Excel365stepbystep/detail.

Get support and give feedback

We’ve made every effort to ensure the accuracy of this book and its companion
content. We welcome your feedback.

Errata and support

If you discover an error, please submit it to us at [Link]/
Excel365stepbystep/errata. We’ll investigate all reported issues, update download-
able content if appropriate, and incorporate necessary changes into future editions
of this book.

For additional book support and information, please visit [Link]/

Support.

For assistance with Microsoft software and hardware, visit the Microsoft Support site
at [Link].

Stay in touch
Let’s keep the conversation going! We’re on Twitter at [Link]/MicrosoftPress.

xxii
Perform
calculations
on data
3
Excel workbooks provide an easy interface for storing
In this chapter
and organizing data, but Excel can do so much more
than that. Using the built-in functions, you can easily ■ Name data ranges
perform a variety of calculations—from simple tasks ■ Create formulas to calculate values
such as calculating totals to complex financial calcula-
tions. Excel can report information such as the current
■ Summarize data that meets
date and time, the maximum value or number of blank specific conditions
cells in a data set, and the cells that meet specific condi- ■ Copy and move formulas
tions, and it can use this information when performing
■ Create array formulas
calculations. To simplify the process of referencing cells
or data ranges in your calculations, you can name them. ■ Find and correct errors in
Excel provides guidance for creating formulas to perform calculations
calculations and for identifying and fixing any errors in ■ Configure automatic and iterative
the calculations.
calculation options
This chapter guides you through procedures related
to naming data ranges, creating formulas to calculate
values, summarizing data in one or more cells, copy-
ing and moving formulas, creating array formulas,
troubleshooting issues with formula calculations, and
configuring automatic and iterative calculation options.

71
Chapter 3: Perform calculations on data

Name data ranges

When you work with large amounts of data, it’s often useful to identify groups of cells
that contain related data. For example, you might have a worksheet for a delivery
service in which:

■ Each column of data summarizes the number of packages handled during one
hour of the day.
■ Each row of data represents a region that handled packages.

Worksheets often contain logical groups of data

Instead of specifying a cell or range of cells individually every time you want to refer-
ence the data they contain, you can name the cell or cells—in other words, create a
named range. For example, you could group the packages handled in the Northeast
region during all time periods into a range named Northeast. Whenever you want to
use the contents of that range in a calculation, you can reference Northeast instead
of $C$3:$I$3. That way, you don’t need to remember the cell range or even the work-
sheet it’s on.

Select a group of cells to create a named range

TIP Range names can be simple or complex. In a workbook that contains different
kinds of data, a more descriptive name such as NortheastVolume can help you remem-
ber the data the range includes.

72
 Name data ranges

If you have a range of data with consistent row or column headings, you can create a
series of ranges at one time instead of having to create each individually.

By default, when you create a named range, its scope is the entire workbook. This
means that you can reference the name in a formula on any worksheet in the work-

3
book. If a workbook contains a series of worksheets with the same content—for
example, sales data worksheets for each month of a year—you might want to set the
scope of ranges on those worksheets to the worksheet instead of to the workbook.

After you create a named range, you can edit the name, the cells the range includes,
or the scope in which the range exists, or delete a range you no longer need, in the
Name Manager.

Manage named ranges in the Name Manager

TIP If your workbook contains a lot of named ranges, tables, or other objects, you can
filter the Name Manager list to locate objects more easily.

To create a named range

1. Select the cells you want to include in the named range.

2. In the Name Box, next to the formula bar, enter a name for your named range.

Or

1. Select the cells you want to include in the named range.

2. On the Formulas tab, in the Defined Names group, select Define Name.

73
Chapter 3: Perform calculations on data

3. In the New Name dialog, do the following:

a. In the Name box, enter a name for the range. The name must begin with a
letter or underscore and may not contain spaces.

b. If you want to restrict the range to use on a specific worksheet, select that
worksheet in the Scope list.

c. If you want to provide additional information to help workbook users identify

the range, enter a description of up to 255 characters in the Comment box.

d. Verify that the Refers to box includes the cells you want to include in
the range.

e. Select OK.

To create a series of named ranges from data with headings

1. Select the cells that contain the headings and data you want to include in the
named ranges.

2. On the Formulas tab, in the Defined Names group, select Create from Selection.

3. In the Create Names from Selection dialog, select the checkbox next to the
location of the heading text from which you want to create the range names.

Name ranges by any outer row or

column in the selection

4. Select OK.

To open the Name Manager

■ On the Formulas tab, in the Defined Names group, select Name Manager.

74
 Name data ranges

To change the name of a named range

1. Open the Name Manager.

2. Select the range you want to rename, and then select Edit.

3. In the Edit Name dialog, in the Name box, change the range name, and then
select OK. 3
To change the cells in a named range

1. Open the Name Manager.

2. Select the range you want to edit, and then do either of the following:
In the Refers to box, change the cell range.
Select Edit. In the Edit Name dialog, in the Refers to box, change the cell
range, and then select OK.

To change the scope of a named range

1. Select the range you want to change the scope of and note the range name
shown in the Name Box.

2. On the Formulas tab, in the Defined Names group, select Define Name.

3. In the New Name dialog, do the following:

a. In the Name box, enter the existing range name that you noted in step 1.

b. In the Scope list, select the new scope.

c. If you want to provide additional information to help workbook users iden-

tify the range, enter a description of up to 255 characters in the Comment
box.

d. Verify that the Refers to box includes the cells you want to include in the
range.

e. Select OK.

To delete a named range

1. Open the Name Manager.

2. Select the range you want to delete, and then select Delete.

3. In the Microsoft Excel dialog prompting you to confirm the deletion, select OK.

75
Chapter 3: Perform calculations on data

Create formulas to calculate values

After you enter data on a worksheet and, optionally, define ranges to simplify data
references, you can create formulas to performs calculations on your data. For exam-
ple, you can calculate the total cost of a customer’s shipments, figure the average
number of packages for all Wednesdays in the month of January, or find the highest
and lowest daily package volumes for a week, month, or year.

You can enter a formula directly into a cell or into the formula bar located between
the ribbon and the worksheet area.

Every formula begins with an equal sign (=), which tells Excel to interpret the expres-
sion after the equal sign as a calculation instead of as text. The formula that you enter
after the equal sign can include simple references and mathematical operators, or it
can begin with an Excel function. For example, you can find the sum of the numbers
in cells C2 and C3 by using the formula =C2+C3. You can edit formulas by selecting
the cell and then editing the formula in the cell or in the formula bar.

Operators and precedence

When you create an Excel formula, you use the built-in functions and arith-
metic operators that define operations such as addition and multiplication.
The following table displays the order in which Excel evaluates mathematical
operations.

Precedence Operator Description

1 - Negation

2 % Percentage

3 ^ Exponentiation

4 * and / Multiplication and division

5 + and – Addition and subtraction

6 & Concatenation

76
 Create formulas to calculate values

If two operators at the same level, such as + and –, occur in the same equa-
tion, Excel evaluates them from left to right.

For example, Excel evaluates the operations in the formula = 4 + 8 * 3 – 6 in

this order:
3
1. 8 * 3 = 24

2. 4 + 24, with a result of 28

3. 28 – 6, with a final result of 22

You can control the order in which Excel evaluates operations by using paren-
theses. Excel always evaluates operations in parentheses first.

For example, if the previous equation were rewritten as = (4 + 8) * 3 – 6, Excel

would evaluate the operations in this order:

1. (4 + 8), with a result of 12

2. 12 * 3, with a result of 36

3. 36 – 6, with a final result of 30

In a formula that has multiple levels of parentheses, Excel evaluates the

expressions within the innermost set of parentheses first and works its way
out. As with operations on the same level, expressions at the same parenthet-
ical level are evaluated from left to right.

For example, Excel evaluates the formula = 4 + (3 + 8 * (2 + 5)) – 7 in this order:

1. (2 + 5), with a result of 7

2. 7 * 8, with a result of 56

3. 56 + 3, with a result of 59

4. 4 + 59, with a result of 63

5. 63 – 7, with a final result of 56

77
Chapter 3: Perform calculations on data

You can perform mathematical operations on numbers by using the mathematical

operators for addition (+), subtraction (–), multiplication (*), division (/), negation (-),
and exponentiation (^). You can perform other operations on a range of numbers by
using the following Excel functions:

■ SUM Returns the sum of the numbers.

■ AVERAGE Returns the average of the numbers.
■ COUNT Returns the number of entries in the cell range.
■ MAX Returns the largest number.
■ MIN Returns the smallest number.

These functions are available from the AutoSum list, which is in the Editing group on
the Home tab of the ribbon and in the Function Library group on the Formulas tab.
The Function Library is also where you’ll find the rest of the Excel functions, organized
into categories.

Excel includes a wide variety of functions

The Formula AutoComplete feature simplifies the process of referencing functions,

named ranges, and tables in formulas. It provides a template for you to follow and
suggests entries for each function argument. Here’s how it works:

1. As you begin to enter a function name after the equal sign, Excel displays a list
of functions matching the characters you’ve entered. You can select a function
from the list and then press Tab to enter the function name and the opening
parenthesis in the cell or formula bar.

78
 Create formulas to calculate values

Select a function from the list

2. After the opening parenthesis, Excel displays the arguments that the selected
function accepts. Bold indicates required arguments and square brackets
enclose optional arguments. You can simply follow the prompts to enter or
select the necessary information, and then enter a closing parenthesis to finish
the formula.

Excel prompts you for required and optional information

3. To reference a named range, table, or table element, start entering the name
(or an opening square bracket to indicate a table element) and Excel displays a
list of options to choose from.

TIP You can reference a series of contiguous cells in a formula by entering the
cell range or by dragging through the cells. If the cells aren’t contiguous, hold
down the Ctrl key and select each cell.

79
Chapter 3: Perform calculations on data

Excel displays the available table elements

SEE ALSO For information about using keyboard shortcuts to select cell ranges, see the
appendix, “Keyboard shortcuts.”

If you’re creating a more complex formula and want extra guidance, you can assemble
the formula in the Insert Function dialog. All the Excel functions are available from
within the dialog.

Create formulas in the Insert Function dialog

If you’re uncertain which function to use, you can search for one by entering a simple
description of what you’d like to accomplish. Selecting any function displays the func-
tion’s arguments and description.

80
 Create formulas to calculate values

Activate any field to display a description of the argument

After you select a function, Excel displays an interface in which you can enter all the
function arguments. The complexity of the interface depends on the function.

Whether you enter a formula directly or assemble it in the Insert Function dialog, you
can reference data in cells (A3) or cell ranges (A3:J12), in named ranges (Northeast),
or in table columns (TableName[ColumnName]). For example, if the Northeast range
refers to cells C3:I3, you can calculate the average of cells C3:I3 by using the formula
=AVERAGE(Northeast).

To create a formula manually

1. Select the cell in which you want to create the formula.

2. In the cell or in the formula bar, enter an equal sign (=).

3. If the formula will call a function, enter the function name and an open-
ing parenthesis to begin the formula and display the required and optional
arguments.

4. Enter the remainder of the formula:

Reference cells by entering the cell reference or selecting the cell.

Reference cell ranges by entering the cell range or dragging across the range.

Reference named ranges and tables by entering the range or table name.

Reference table elements by entering [ after the table name, selecting the
element from the list, and then entering ].

81
Chapter 3: Perform calculations on data

5. If the formula includes a function, enter the closing parenthesis to end it.

6. Press Enter to enter the formula in the cell and return the results.

To open the Insert Function dialog

■ On the formula bar, to the left of the text entry box, select the Insert Function
button (fx).
■ On the Formulas tab, in the Function Library group, select Insert Function.
■ Press Shift+F3.

To locate a function in the Insert Function dialog

■ In the Search for a function box, enter a brief description of the operation you
want to perform, and then select Go.

Or

1. In the Or select a category list, select the function category.

2. Scroll down the Select a function list to the function.

To create a formula in the Insert Function dialog

1. Open the Insert Function dialog.

2. Select the function you want to use in the formula, and then select OK.

3. In the Function Arguments dialog, enter the function’s arguments, and then
select OK.

To reference a named range in a formula

■ Enter the range name in place of the cell range.

To reference an Excel table column in a formula

■ Enter the table name followed by an opening bracket ([), the column name, and
a closing bracket (]).

82
 Summarize data that meets specific conditions

Summarize data that meets

specific conditions
Another use for formulas is to display messages when certain conditions are met.
This kind of formula is called a conditional formula. One way to create a conditional
formula in Excel is to use the IF function. Selecting the Insert Function button next to
3
the formula bar and then choosing the IF function displays the Function Arguments
dialog with the fields required to create an IF formula.

The Function Arguments dialog for an IF formula

When you work with an IF function, the Function Arguments dialog displays three
input boxes:

■ Logical_test The condition you want to check.

■ Value_if_true The value to display if the condition is met. This could be a cell
reference, or a number or text enclosed in quotes.
■ Value_if_false The value to display if the condition is not met.

83
Chapter 3: Perform calculations on data

The following table displays other conditional functions you can use to summarize data.

Function Description
AVERAGEIF Finds the average of values within a cell range that meet a
specified criterion

AVERAGEIFS Finds the average of values within a cell range that meet
multiple criteria

COUNT Counts the cells in a range that contain numerical values

COUNTA Counts the cells in a range that are not empty

COUNTBLANK Counts the cells in a range that are empty

COUNTIF Counts the cells in a range that meet a specified criterion

COUNTIFS Counts the cells in a range that meet multiple criteria

IFERROR Displays one value if a formula results in an error and another

if it doesn’t

SUMIF Adds the values in a range that meet a single criterion

SUMIFS Adds the values in a range that meet multiple criteria

To create a formula that uses the AVERAGEIF function, you define the range to be
examined for the criterion, the criterion, and, if required, the range from which to
draw the values. As an example, consider a worksheet that lists each customer’s ID
number, name, state, and total monthly shipping bill. If you want to find the average
order of customers from the state of Washington (abbreviated in the worksheet as
WA), you can create the formula =AVERAGEIF(C3:C6, “WA”, D3:D6).

Sample data that illustrates the preceding example

The AVERAGEIFS, SUMIFS, and COUNTIFS functions extend the capabilities of the
AVERAGEIF, SUMIF, and COUNTIF functions to allow for multiple criteria. For example,
if you want to find the sum of all orders of at least $100,000 placed by companies
in Washington, you can create the formula =SUMIFS(D2:D5, C2:C5, “=WA”, D2:D5,
“>=100000”).

84
 Summarize data that meets specific conditions

The AVERAGEIFS and SUMIFS functions start with a data range that contains values
that the formula summarizes. You then list the data ranges and the criteria to apply to
that range. In generic terms, the syntax is =AVERAGEIFS(data_range, criteria_range1,
criteria1[,criteria_range2, criteria2...]). The part of the syntax in brackets (which aren’t
used when you create the formula) is optional, so an AVERAGEIFS or SUMIFS formula
that contains a single criterion will work. The COUNTIFS function, which doesn’t per-
form any calculations, doesn’t need a data range; you just provide the criteria ranges
3
and criteria. For example, you could find the number of customers from Washington
who were billed at least $100,000 by using the formula =COUNTIFS(C2:C5, “=WA”,
D2:D5, “>=100000”).

You can use the IFERROR function to display a custom error message instead of
relying on the default Excel error messages to explain what happened. For example,
you could create this type of formula to employ the VLOOKUP function to look up
a customer’s name in the second column of a table named Customers based on the
customer identification number entered into cell G8. That formula might look like this:
=IFERROR(VLOOKUP(G8,Customers,2,FALSE),”Customer not found”). If the function
finds a match for the customer ID in cell G8, it displays the customer’s name; if not, it
displays the text “Customer not found.”

TIP The last two arguments in the VLOOKUP function tell the formula to look in the
Customers table’s second column and to require an exact match. For more information
about the VLOOKUP function, see “Look up data from other locations” in Chapter 7, “Combine
data from multiple sources.”

To summarize data by using the IF function

■ Use the syntax =IF(logical_test, value_if_true, value_if_false) where:
logical_test is the logical test to be performed.
value_if_true is the value the formula returns if the test is true.
value_if_false is the value the formula returns if the test is false.

To count cells that contain numbers in a range

■ Use the syntax =COUNT(range), where range is the cell range in which you
want to count cells.

85
Chapter 3: Perform calculations on data

To count cells that are non-blank

■ Use the syntax =COUNTA(range), where range is the cell range in which you
want to count cells.

To count cells that contain a blank value

■ Use the syntax =COUNTBLANK(range), where range is the cell range in which
you want to count cells.

To count cells that meet one condition

■ Use the syntax =COUNTIF(range, criteria) where:
range is the cell range that might contain the criteria value.
criteria is the logical test used to determine whether to count the cell.

To count cells that meet multiple conditions

■ Use the syntax =COUNTIFS(criteria_range1, criteria1, criteria_range2,
criteria2,…) where for each criteria_range and criteria pair:
criteria_range is the cell range that might contain the criteria value.
criteria is the logical test used to determine whether to count the cell.

To find the sum of data that meets one condition

■ Use the syntax =SUMIF(range, criteria, sum_range) where:
range is the cell range that might contain the criteria value.
criteria is the logical test used to determine whether to include the cell.
sum_range is the range that contains the values to be included if the range
cell in the same row meets the criterion.

To find the sum of data that meets multiple conditions

■ Use the syntax =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2,
criteria2,…) where:
sum_range is the range that contains the values to be included if all
criteria_range cells in the same row meet all criteria.
criteria_range is the cell range that might contain the criteria value.
criteria is the logical test used to determine whether to include the cell.

86
 Copy and move formulas

To find the average of data that meets one condition

■ Use the syntax =AVERAGEIF(range, criteria, average_range) where:
range is the cell range that might contain the criteria value.

criteria is the logical test used to determine whether to include the cell.
3

average_range is the range that contains the values to be included if the

range cell in the same row meets the criterion.

To find the average of data that meets multiple conditions

■ Use the syntax =AVERAGEIFS(average_range, criteria_range1, criteria1,
criteria_range2, criteria2,…) where:
average_range is the range that contains the values to be included if all
criteria_range cells in the same row meet all criteria.
criteria_range is the cell range that might contain the criteria value.
criteria is the logical test used to determine whether to include the cell.

To display a custom message if a cell contains an error

■ Use the syntax =IFERROR(value, value_if_error) where:
value is a cell reference or formula.
value_if_error is the value to be displayed if the value argument returns
an error.

Copy and move formulas

After you create a formula, you can copy it and paste it into another cell. When you
do, Excel changes the formula to work in the new cells. For instance, suppose you have
a worksheet in which cell C7 contains the formula =SUM(C2:C6). If you copy cell C7
and paste the copied formula into cell D7, Excel enters =SUM(D2:D6). Excel knows to
change the cells used in the formula because the formula uses a relative reference—a
reference that can change if the formula is copied to another cell. Relative references
are written with just the cell row and column—for example, C14.

87
Chapter 3: Perform calculations on data

Relative references are useful when you summarize rows of data and want to use
the same formula for each row. As an example, suppose you have a worksheet with
two columns of data, labeled Sale Price and Rate, and you want to calculate a sales
representative’s commission by multiplying the two values in a row. To calculate the
commission for the first sale, you would enter the formula =A2*B2 in cell C2.

Use formulas to calculate values such as commissions

Selecting cell C2 and dragging the fill handle down through cell C7 copies the formula
from cell C2 into each of the other cells. Because you created the formula by using
relative references, Excel updates each cell’s formula to reflect its position relative to
the starting cell (in this case, cell C2). The formula in cell C7, for example, is =A7*B7.

Copying formulas to other cells to summarize additional data

When you enter a formula in a cell of an Excel table column, Excel automatically
copies the formula to the rest of the column and updates any relative references in
the formula.

88
 Copy and move formulas

If you want a cell reference to remain constant when you copy a formula to another
cell, use an absolute reference by inserting a dollar sign ($) before the column letter
and row number or a mixed reference by inserting a dollar sign before either the
column letter or row number.

TIP In addition to using an absolute reference, another way to ensure that your cell ref- 3
erences don’t change when you copy a formula to another cell is to select the cell that
contains the formula, copy the formula’s text in the formula bar, press the Esc key to exit cut-
and-copy mode, select the cell where you want to paste the formula, and press Ctrl+V. Excel
doesn’t change the cell references when you copy your formula to another cell in this manner.

One quick way to change a cell reference from relative to absolute is to select the
cell reference in the formula bar and then press F4. Pressing F4 cycles a cell reference
through the four possible types of references:

■ Relative columns and rows (for example, C4)

■ Absolute columns and rows (for example, $C$4)
■ Relative columns and absolute rows (for example, C$4)
■ Absolute columns and relative rows (for example, $C4)

To copy a formula without changing its cell references

1. Select the cell that contains the formula you want to copy.

2. In the formula bar, select the formula text.

3. Press Ctrl+C.

4. Select the cell in which you want to paste the formula.

5. Press Ctrl+V.

6. Press Enter.

To move a formula without changing its cell references

1. Select the cell that contains the formula you want to copy.

2. Point to the edge of the selected cell until the pointer changes to a black four-
headed arrow.

3. Drag the outline to the cell where you want to move the formula.

89
Chapter 3: Perform calculations on data

To copy a formula and change its cell references

1. Select the cell that contains the formula you want to copy.

2. Press Ctrl+C.

3. Select the cell in which you want to paste the formula.

4. Press Ctrl+V.

To create relative and absolute cell references

1. Enter a cell reference into a formula.

2. Do either of the following:

Enter a $ in front of a row or column reference you want to make absolute.
Select within the cell reference, and then press F4 to advance through the four
possible combinations of relative and absolute row and column references.

Create array formulas

Most Excel formulas calculate values to be displayed in a single cell. For example, you
could add the formulas =B1*B4, =B1*B5, and =B1*B6 to consecutive worksheet cells to
calculate shipping insurance costs based on the value of a package’s contents.

A worksheet with data to be summarized by an array formula

Instead of entering the same formula in multiple cells one cell at a time, you can enter
a formula in every cell in the target range at the same time by creating an array for-
mula. To calculate package insurance rates by multiplying the values in the cell range
B4:B6 by the insurance rate in cell B1, you select the target cells (C4:C6) and enter

90
 Create array formulas

the formula =B1*B4:B6. Note that you must select a range of the same shape as the
values you’re using in the calculation. (For example, if the value range is three columns
wide by one row high, the target range must also be three columns wide by one row
high.) If you enter the array formula into a range of the wrong shape, Excel displays
duplicate results, incomplete results, or error messages, depending on how the target
range differs from the value range.
3
When you press Ctrl+Shift+Enter, Excel creates an array formula in the selected cells.
The formula appears within a pair of braces to indicate that it is an array formula.

An array formula calculates multiple results

IMPORTANT You can’t add braces to a formula to make it an array formula. You must
press Ctrl+Shift+Enter to create it.

To create an array formula

1. Select the cells in which you want to display the formula results.

2. In the formula bar, enter the array formula.

3. Press Ctrl+Shift+Enter.

To edit an array formula

1. Select every cell that contains the array formula.

2. In the formula bar, edit the array formula.

3. Press Ctrl+Shift+Enter to re-enter the formula as an array formula.

91
Chapter 3: Perform calculations on data

Find and correct errors in calculations

Including calculations in a worksheet gives you valuable answers to questions about
your data. As is always true, however, it’s possible for errors to creep into your formu-
las. With Excel, you can find the source of errors in your formulas by identifying the
cells used in a specific calculation and describing any errors that have occurred. The
process of examining a worksheet for errors is referred to as auditing.

Excel identifies errors in several ways. The first way is to display an error code in the
cell that contains the formula generating the error.

A warning triangle and pound sign indicate an error

When the active cell generates an error, Excel displays an Error button next to it.
Pointing to the button displays information about the error, and selecting the button
displays a menu of options for handling the error.

The following table explains the most common error codes.

Error code Description

##### The column isn’t wide enough to display the value.

#VALUE! The formula has the wrong type of argument, such as text in a cell where a
numerical value is required.

#NAME? The formula contains text that Excel doesn’t recognize, such as an unknown
named range.

#REF! The formula refers to a cell that doesn’t exist, which can happen whenever
cells are deleted.

#DIV/0! The formula attempts to divide by zero.

92
 Find and correct errors in calculations

Another technique you can use to find the source of formula errors is to ensure that
the appropriate cells are providing values for the formula. You can identify the source
of an error by having Excel trace a cell’s precedents, which are the cells with values
used in the active cell’s formula. You can also audit your worksheet by identifying cells
with formulas that use a value from a particular cell. Cells that use another cell’s value
in their calculations are known as dependents, meaning that they depend on the value
in the other cell to derive their own value. They are identified in Excel by tracer arrows.
3
If the cells identified by the tracer arrows aren’t the correct cells, you can hide the
arrows and correct the formula.

Tracing a cell’s dependents

If you prefer to have the elements of a formula error presented as text in a dialog, you
can use the Error Checking tool to locate errors one after the other. You can choose to
ignore the selected error or move to the next or the previous error.

Identify and manage errors from the Error Checking window

TIP You can have the Error Checking tool ignore formulas that don’t use every cell in
a region (such as a row or column). To do so, on the Formulas tab of the Excel Options
dialog, clear the Formulas Which Omit Cells In A Region checkbox. Excel will no longer mark
these cells as an error.

93
Chapter 3: Perform calculations on data

When you just want to display the results of each step of a formula and don’t need
the full power of the Error Checking tool, you can use the Evaluate Formula dialog to
move through each element of the formula. The Evaluate Formula dialog is particu-
larly useful for examining formulas that don’t produce an error but aren’t generating
the result you expect.

Step through formulas in the Evaluate Formula window

Finally, you can monitor the value in a cell regardless of where you are in your work-
book by opening a Watch Window that displays the value in the cell. For example, if
one of your formulas uses values from cells in other worksheets or even other work-
books, you can set a watch on the cell that contains the formula, and then change
the values in the other cells. As you change the precedent values, the formula result
changes in the Watch Window. When you’re done watching the formula, you can
delete the watch and close the Watch Window.

Monitor formula results in the Watch Window

94
 Find and correct errors in calculations

To display information about a formula error

1. Select the cell that contains the error.

2. Point to the error indicator next to the cell to display information about the error.

3. Select the error indicator to display options for correcting or learning more
about the error. 3
To identify the cells that a formula references

1. Select the cell that contains the formula.

2. On the Formulas tab, in the Formula Auditing group, select Trace Precedents.

To identify formulas that reference a specific cell

1. Select the cell.

2. In the Formula Auditing group, select Trace Dependents.

To remove tracer arrows

■ In the Formula Auditing group, do one of the following:
To remove all the arrows, select the Remove Arrows button (not its arrow).
To remove only precedent or dependent arrows, select the Remove
Arrows arrow, and then select Remove Precedent Arrows or Remove
Dependent Arrows.

To evaluate a formula one calculation at a time

1. Select the cell that contains the formula you want to evaluate.

2. In the Formula Auditing group, select Evaluate Formula.

3. In the Evaluate Formula dialog, select Evaluate. Excel replaces the underlined
calculation with its result.

4. Do either of the following:

Select Step In to move forward by one calculation.
Select Step Out to move backward by one calculation.

5. When you finish, select Close.

95
Chapter 3: Perform calculations on data

To change error display options

1. Display the Formulas page of the Excel Options dialog.

2. In the Error Checking section, select or clear the Enable background error
checking checkbox.

3. Select the Indicate errors using this color button and select a color.

4. Select Reset Ignored Errors to return Excel to its default error indicators.

5. In the Error checking rules section, select or clear the checkboxes next to errors
you want to indicate or ignore, respectively.

To watch the values in a cell range

1. Select the cell range you want to watch.

2. In the Formula Auditing group, select the Watch Window button.

3. In the Watch Window dialog, select Add Watch.

4. In the Add Watch dialog, confirm the cell range, and then select Add.

To delete a watch

1. Select the Watch Window button.

2. In the Watch Window dialog, select the watch you want to delete.

3. Select Delete Watch.

Configure automatic and iterative

calculation options
Excel formulas use values in other cells to calculate their results. If you create a for-
mula that refers to the cell that contains the formula, the result is a circular reference.

Under most circumstances, Excel treats a circular reference as a mistake for two rea-
sons. First, most Excel formulas don’t refer to their own cell, so a circular reference is
unusual enough to be identified as an error. The second, more serious consideration is
that a formula with a circular reference can slow down your workbook. Because Excel
repeats, or iterates, the calculation, you must set limits on how many times the app
repeats the operation.

96
 Configure automatic and iterative calculation options

You can control how often Excel recalculates formulas. Three calculation options are avail-
able from the Formulas tab and from the Formulas page of the Excel Options dialog.

You can modify the iterative calculation options Excel uses

The calculation options work as follows:

■ Automatic recalculates a worksheet whenever a value that affects a formula

changes. This is the default setting.
■ Automatic Except for Data Tables recalculates a worksheet whenever a value
changes but doesn’t recalculate data tables.
■ Manual recalculates formulas only when you tell Excel to do so.

You can also use options in the Calculation Options section to allow or disallow itera-
tive calculations (repeating calculations of formulas that contain circular references).
The default values (a maximum of 100 iterations and a maximum change per iteration
of 0.001) are appropriate for all but the most unusual circumstances.

To manually recalculate the active workbook

■ On the Formulas tab, in the Calculation group, select Calculate Now.
■ Press F9.

To manually recalculate the active worksheet

■ In the Calculation group, select the Calculate Sheet button.

97
Chapter 3: Perform calculations on data

To set worksheet calculation options

■ Display the worksheet whose calculation options you want to set.
■ On the Formulas tab, in the Calculation group, select Calculation Options, and
then select Automatic, Automatic Except for Data Tables, or Manual.

To enable iterative calculations

1. Open the Excel Options dialog and display the Formulas page.

2. In the Calculation options section, select the Enable iterative calculation

checkbox.

3. In the Maximum Iterations box, enter the maximum iterations allowed for
a calculation.

4. In the Maximum Change box, enter the maximum change allowed for
each iteration.

5. Select OK.

98
 Skills review

Skills review
In this chapter, you learned how to:
■ Name data ranges
■ Create formulas to calculate values
3
■ Summarize data that meets specific conditions
■ Copy and move formulas
■ Create array formulas
■ Find and correct errors in calculations
■ Configure automatic and iterative calculation options

99
 Chapter 3

Practice tasks
Before you can complete these tasks, you must copy the book’s
practice files to your computer. The practice files for these tasks are in
the Excel365SBS\Ch03 folder. You can save the results of the tasks in the
same folder.

Name data ranges

Open the NameRanges workbook in Excel, and then perform the following tasks:

1. Create a named range named Monday for the V_101 through V_109 values
(found in cells C4:C12) for that weekday.

2. Edit the Monday named range to include the V_110 value for that column.

3. Select cells B4:H13 and create a batch of named ranges for V_101 through V_110,
using the row headings as the range names.

4. Delete the Monday named range.

Create formulas to calculate values

Open the BuildFormulas workbook in Excel, and then perform the following tasks:

1. On the Summary worksheet, in cell F9, create a formula that displays the value
from cell C4.

2. Edit the formula in cell F9 so it uses the SUM function to find the total of values
in cells C3:C8.

3. In cell F10, create a formula that finds the total expenses for desktop software
and server software.

4. Edit the formula in F10 so the cell references are absolute references.

5. On the JuneLabor worksheet, in cell F13, create a SUM formula that finds the
total of values in the JuneSummary table’s Labor Expense column.

100
 Practice tasks

Summarize data that meets specific conditions

Open the CreateConditionalFormulas workbook in Excel, and then perform the fol-
lowing tasks:

1. In cell G3, create an IF formula that tests whether the value in F3 is greater
than or equal to 35,000. If it is, display Request discount; if not, display
No discount available.

2. Copy the formula from cell G3 to the range G4:G14.

3. In cell I3, create a formula that finds the average cost of all expenses in cells
F3:F14 where the Type column contains the value Box.

4. In cell I6, create a formula that finds the sum of all expenses in cells F3:F14
where the Type column contains the value Envelope, and the Destination
column contains the value International.

Create array formulas

Open the CreateArrayFormulas workbook in Excel, and then perform the
following tasks:

1. On the Fuel worksheet, in cells C11:F11, enter the array formula =C3*C9:F9.

2. Edit the array formula you just created to read =C3*C10:F10.

3. On the Volume worksheet, in cells D4:D7, create the array formula

=B4:B7*C4:C7.

Find and correct errors in calculations

Open the AuditFormulas workbook in Excel, and then perform the following tasks:

1. Set a watch on the value in cell C19.

2. Display the precedents for the formula in cell C7.

3. Hide the tracer arrows.

4. Use the Error Checking dialog to identify the error in cell C20.

5. Show the tracer arrows for the error.

6. Hide the arrows, and then change the formula in cell C20 to =C12/D20.

101
 Chapter 3

7. Use the Evaluate Formula dialog to step through the formula in cell C20.

8. Delete the watch you created in step 1.

Configure automatic and iterative calculation options

Open the SetIterativeOptions workbook in Excel, and then perform the following tasks:

1. On the Formulas tab, in the Calculation group, select the Calculation Options
button, and then select Manual.

2. In cell B6, enter the formula =B7*B9, and then press Enter.

3. Note that this result is incorrect because the Gross Savings value minus the
Savings Incentive value should equal the Net Savings value, which it does not.

4. Press F9 to recalculate the workbook and read the message box indicating that
you have created a circular reference.

5. Select OK.

6. Use options in the Excel Options dialog to enable iterative calculation.

7. Close the Excel Options dialog and recalculate the worksheet.

8. Change the workbook’s calculation options back to Automatic.

102
Index
Numbers aspect ratio, explained, 441
auditing, explained, 441
2-D map charts, 223 AutoCalculate feature, 143–144, 148, 441. See also
2-D pie charts, 210 calculations
3-D data maps, creating, 416–421, 424 AutoComplete feature, 43–44, 46, 441
3-D pie charts, 210 AutoFill feature, 44–46, 441
3-D references, 186, 441 AutoRepublish feature, explained, 441
3-D shading, applying to form controls, 327 AutoSum feature, 78, 411
AVERAGE function, 78, 143
AVERAGEIF function, 84, 87
Symbols AVERAGEIFS function, 84–85, 87
##### error code, 92
+ (addition) operator, precedence of, 76, 78
[] (brackets), using with columns in formulas, 82 B
& (concatenation) operator, precedence of, 76 Backstage view
/ (division) operator, precedence of, 76, 78 explained, 441
^ (exponentiation) operator, precedence of, 76, 78 Microsoft 365 app keyboard shortcuts, 436
* (multiplication) operator, precedence of, 76, 78 using, 5–7
- negation operator, precedence of, 76, 78 using for printed worksheets, 292
% (percentage) operator, precedence of, 76 bin, explained, 441
‘ (single quote), using with workbook names, 186 Bing search engine, 58
– (subtraction) operator, precedence of, 76, 78 blank worksheets, creating, 12. See also worksheets
Body and Heading fonts, changing, 109
bold formatting, 117, 426, 430
A borders, applying, 105, 107–108, 426, 431
absolute references, 89, 441. See also relative box-and-whisker charts, 219, 441
references brackets ([]), using with columns in formulas, 82
actions, undoing and restoring, 59 browser, explained, 441
active cell. See also cells bubble charts, 220
designating, 48 Button form control, 323
editing, 432 buttons
explained, 441 activating, 427
active tab, selecting, 427 adding to Quick Access Toolbar, 28–29
add-ins, 376, 441 explained, 441
addition (+) operator, precedence of, 76, 78 macro access through, 328–329
AGGREGATE function, 143–144, 147, 149 reordering on Quick Access Toolbar, 32
alignment, explained, 441
Alt key. See keyboard shortcuts
animating geographic data, 419 C
arguments. See also Function Arguments dialog calculated columns. See also columns
displaying descriptions of, 81 adding to data sources, 388
explained, 441 creating, 411
using with functions, 79 calculating
array formulas, creating, 90–91, 101. See also formulas values, 76–82
Arrow keys. See keyboard shortcuts worksheets, 433
ascending order, sorting values in, 386

447
Index

calculation options, configuring, 96–98, 102 scatter, 220–221

calculations, finding and correcting errors in, statistic, 218–220
92–96. See also AutoCalculate feature stock, 221–222
category axis, explained, 441 charts. See also dynamic PivotCharts
cell background color, applying or changing, 106 accessing, 53
cell border, applying and removing, 107–108 color palettes, 228
cell data, picking from lists, 46 color schemes, 231
cell notes, adding and editing, 432 copying and pasting, 346–347, 350
cell ranges creating, 208–215, 250–251, 433
converting tables to, 66 displaying and hiding elements in, 227
copying to Clipboard, 51 dual axis, 215–216
cutting to Clipboard, 51 explained, 441
explained, 441 formatting, 227
moving, 50 layouts, 210
pasting, 51–52 linking to, 346–347, 350
previewing before pasting, 52 moving in worksheets, 214
selecting, 429 printing, 302–303, 306
cell references, 185–188, 441 resizing, 214
cell styles, defining and managing, 110–113, 133 selecting, 229
cells. See also active cell; header cells trendlines, 234–235
changing attributes of, 106 Check Box form control, 323, 326, 329–330
copying, 430 checkbox, explained, 442
counting, 85–86 circular reference, explained, 442
dependents, 93 Clipboard, cutting and copying data to, 49
editing contents of, 55 closing
entering values in, 43, 45–46 Excel, 432
excluding from tables, 62–63 workbooks, 9
explained, 441 color of background, applying or changing, 106.
formatting, 104–109, 133, 430–431 See also fill color
inserting, 19 color of fonts, changing, 105
locking and unlocking, 358, 360 color of text, changing, 107
merging and unmerging, 20–22, 40 color palettes, selecting for charts, 228
moving, 20, 430 color scales, 123, 442
navigating, 428–429 color schemes, charts, 231
referencing in formulas, 79 colors, selecting for themes, 114
selecting, 79 colors for tabs, changing for worksheets, 14
tracing precedents of, 93 column and row values, switching in charts, 213
cells and cell ranges, selecting, 50 column width, modifying, 15–18
cells styles, explained, 441 columns. See also calculated columns
chart appearance, customizing, 226–233, 251 adding to tables, 65
chart elements deleting, 18, 389
displaying and hiding, 231 explained, 442
selecting and formatting, 231 filtering, 139, 386–387
Chart Filters, 229, 232 hiding and unhiding, 19
chart layouts, applying, 230 inserting, 18
chart styles, applying, 228–230 referencing in formulas, 82
chart templates, applying, 232–233 reformatting, 387–388
chart types renaming, 388
hierarchy, 217–218 repeating on printed pages, 302
map, 222–226 selecting, 51
overview, 216

448
 Index

columns and rows D

deleting, 16
inserting, 16 data. See also worksheet data
COM add-in, disabling, 378 changing appearance of, 122–129, 134
Combo Box form control, 323, 327, 442 correcting and fine-tuning, 57–61, 69
combo charts, creating, 215–216, 250, 442. displaying on timelines, 391–394, 402
See also charts entering and revising, 42–46, 68
command buttons, assigning macros to, 325 finding and replacing, 53–57, 69
commands importing using Power Query, 395–400
adding to Quick Access Toolbar, 31 integrating into Excel Data Model, 379
controlling display of, 29 linking to, 185–188, 191
moving to, 427 looking up, 176–184, 191
opening lists for, 427 managing with Flash Fill, 46–47, 68
removing from Quick Access Toolbar, 33 managing with Power Pivot, 384–390, 401–402
repeating, 432 moving in workbooks, 48–53, 69
comments, managing, 352–355, 369 organizing for PivotTables, 260
concatenation (&) operator, precedence of, 76 organizing into levels, 170–171
conditional formatting, 53, 123–129, 274–276, 442. outlining, 168–172, 174
See also formatting plotting in charts, 211, 213
conditional functions, using to summarize data, 84. subtotaling, 168–172, 174
See also functions summarizing, 83–87, 101
confidence interval, 409, 442 summarizing in tables, 63
consolidating sets of data, 188–190, 192 summarizing to meet conditions, 83–87, 101
context menus, 112, 426, 442 summarizing using sparklines, 235–238, 251
control group, 442 data analysis. See PivotTables
copies, printing, 299 data bars, 123, 442
Copy Cells option, 45 data connections, creating with Power Query,
copying 395–400
cell ranges to Clipboard, 51 data consolidation, 442
cells, 430 data entry
formulas, 430 canceling, 42
items, 48–49 enforcing criteria, 150–156, 158
or linking to charts, 346–347, 350 techniques, 43–44
and moving formulas, 87–90 Data Model. See Excel Data Model
and moving worksheets, 11 data ranges
and pasting charts, 346–347, 350 filtering, 138–143, 157
sorting levels, 165 finding values in, 143
worksheets, 13 naming, 72–75, 100
COUNT function, 78, 84–85, 143, 146 data series, controlling, 43
COUNTA function, 84, 86, 146 data sets
COUNTBLANK function, 84, 86 consolidating, 188–190
COUNTIF function, 85–86 defining and displaying, 194–198, 205
COUNTIFS function, 84–85 data sources
Ctrl key. See keyboard shortcuts calculated columns, 388
Ctrl+Enter, using to enter values in cells, 43–44 defining relationships between, 380–384, 401
Currency format, 431 data tables
custom lists, sorting data by, 166–167. See also explained, 442
list rows forecasting data with, 199–202, 205
cutting and copying data to Clipboard, 49 data trends, identifying, 233–235, 251
Cycle SmartArt graphics, 239 data-formatting commands, accessing, 52–53
data validation, 150–156

449
Index

date and time, entering, 430–431 E

deleting
3D maps, 420 email messages, creating hyperlinks for, 344–345
cell borders, 108 embed, explained, 442
cell formatting, 108–109 embedding file content, 338–340
columns, 16, 18, 389, 426 equations, building, 248
comments, managing, 354 error alerts, configuring, 155
conditional formatting, 129 Error Checking window, 93–94, 433
custom cell styles, 113 error code, explained, 442
custom table styles, 119 errors in calculations, finding and correcting, 92–96,
custom themes, 115 101–102
fields from PivotTables, 261 Evaluate Formula window, 94
form controls, 332 even and odd pages, headers and footers on, 290
images, 132 Excel
KPIs (key performance indicators), 415 closing, 432
macros, 317 combining Word and PowerPoint content,
measures, 412 336–340, 349
named ranges, 75 incorporating earlier versions of, 11
page breaks, 296 Excel app window
passwords, 359–361 arranging workbook windows, 36–38
personal information, 363 customizing, 40
queries in Power Query, 400 customizing status bar, 33–34
relationships between tables, 384 magnification level of worksheets, 35–36
rows, 18, 426 managing Quick Access Toolbar, 28–33
shapes, 249 managing ribbon, 23–28
sorting rules, 165 Excel Data Model
timelines, 394 adding tables to, 378
tracer arrows, 95 creating PivotTables from, 389–390
validating circles, 156 deleting tables from, 389
watches, 96 displaying, 379
worksheets, 15 managing, 374–380, 401
delimiter, explained, 442 views, 382
dependents, explained, 442 Excel Help task pane, 432
descending order, sorting values in, 386 Excel Options dialog, opening, 25, 109, 311
Developer tab, displaying on ribbon, 313, 326 Excel tables. See tables
dictionary, adding words to, 61 exponential smoothing forecasting, 403–410, 442
digital certificate, 442 exponentiation (^) operator, precedence of, 76, 78
digital signature, 442 extend mode, 433
distribute, explained, 442
#DIV/0! error code, 92
division (/) operator, precedence of, 76, 78
F
Document Inspector, 362, 442 F key commands, 432. See also keyboard shortcuts
document properties, defining values for, 8. fields, explained, 442
See also properties file content, embedding, 338–340
documents, linking to, 336–338 files
doughnut charts, 210 linking to, 337–338
Drop-Down list, picking from, 43–44 saving, 5
dynamic PivotCharts, creating, 277–280, 282, 444. fill color, choosing, 107, 426. See also color of
See also charts background
Fill Days, Weekdays options, 45
Fill Down command, 431

450
 Index

Fill Formatting Only option, 45 Formula bar

fill handle explained, 443
explained, 442 hiding and unhiding, 28
using, 43, 45 formulas. See also array formulas
Fill Series option, 44–45, 443 copying and moving, 87–90, 430
Fill Without Formatting option, 45 creating, 80–82
filtered data, summarizing, 143–150 creating to calculate values, 76–82, 100
filtering. See also Sort & Filter menu explained, 443
data ranges and tables, 138–143 hiding, 360
PivotTables, 262–268, 281 monitoring results of, 94
values in columns, 386–387 recalculating, 97–98
filters rechecking, 433
clearing, 142 stepping through, 94
explained, 443 Function Arguments dialog, 83. See also arguments
finalizing workbooks, 362–363 Function key commands, 432. See also keyboard
Find & Select menu, displaying, 55 shortcuts
finding. See also searching information functions. See also conditional functions; Insert
and correcting errors in calculations, 101–102 Function dialog
and replacing data, 53–57 accessing, 78, 82
Flash Fill option, 45–47 AGGREGATE, 143–144, 146–147, 149
focus, moving to commands on ribbon, 427 AVERAGE, 78, 143
font color, changing, 105 AVERAGEIF function, 84, 87
fonts, changing, 109 AVERAGEIFS function, 84–85, 87
footers and headers, adding, 286–291, 305, 443 COUNT, 78, 84–85, 143, 146
FORECAST functions, 404–410 COUNTA, 84, 86, 146
forecast values, calculating, 409 COUNTBLANK, 84, 86
forecast worksheets, creating, 404–409, 422 COUNTIF, 85–86
forecasting data, 199–202, 205 COUNTIFS, 84–85
foreign key, 443 explained, 443
form controls [Link](), 404–410
aligning, 331 [Link](), 404–410
alternative text for, 332 [Link](), 404–410
controlling, 332 [Link](), 404
deleting, 332 HLOOKUP, 176, 179–182
grouping, 331 IF, 83, 85
moving, 330 IFERROR, 84–85, 87
resizing, 332 inserting, 432
selecting, 330 MAX, 146
types, 323 MEDIAN, 146
using, 322–332, 334 MIN, 146
format, explained, 443 [Link], 146
Format Control dialog, 324 PRODUCT, 146
formatting. See also conditional formatting RAND, 149
applying, 52–53 RANDBETWEEN, 149
cells, 104–109, 133, 430–431 STDEV.P, 146
chart elements, 227, 231 STDEV.S, 146
finding and replacing, 57 SUBTOTAL, 143–144, 146–147
PivotTables, 273–277, 282 SUM, 78, 143–144, 146
formatting controls, displaying, 105 SUMIF, 85–86
Formula AutoComplete feature, 78–80, 443 SUMIFS, 84–86

451
Index

functions I
VAR.P, 146
VAR.S, 146 icon sets, 123, 443
VLOOKUP, 176–179 iconography, adding to worksheets, 131
XLOOKUP, 85, 176–179, 182–184 icons, assigning macros to, 320–321
funnel charts, 222 IF function, working with, 83, 85
IFERROR function, 84–85, 87
images
G adding to worksheets, 129–132, 134
editing in headers and footers, 289–290
General number format, 431
importing
geographic data
data using Power Query, 395–400, 402
3D mapping, 416–421
Quick Access toolbar, 33
charting, 223
Insert dialog, opening, 16, 20, 430
Go To actions, 427, 432
Insert Function dialog, 80, 82. See also functions
Goal Seek, 443
inserting
graphs. See charts
cells, 19
gridlines, hiding and unhiding, 28, 324
columns and rows, 18
Group Box form control, 323–324
Intelligent Services feature, 58
groups of cells, connecting, 50
italicizing text, 430

H K
header cells, 145, 443. See also cells
keyboard shortcuts. See also F key commands;
headers and footers, adding, 286–291, 305
Microsoft 365 app keyboard shortcuts
Heading and Body fonts, changing, 109
array formulas, 91
Help feature, 432
bold formatting, 426
hiding
borders, 426
command labels on Quick Access Toolbar, 31
cell ranges, 429
ribbon tab, 26
center-align cell contents, 426
worksheets, 14
closing workbooks, 9, 426
hiding and unhiding
context menus, 426
columns and rows, 19, 426
Copy, 51, 89–90, 426
formula bar, 28
Cut, 51, 426
gridlines, 28
Delete columns, 426
row and column headings, 28
Delete rows, 426
hierarchy charts, 217–218
entering values in cells, 43–44
Hierarchy SmartArt graphics, 239
fill color, 426
histograms
Find, 56
combining with line charts, 218–219
formatting cells, 430–431
explained, 443
Go To actions, 427
HLOOKUP function, 176, 179–182
Hide columns, 426
Home key. See keyboard shortcuts
Hide rows, 426
HTML (Hypertext Markup Language), 443
hyperlinks, 341
hyperlinks
Insert Function dialog, 82
creating from worksheets, 340–346, 349–350
Macro dialog, 313
displaying targets of, 345
navigating worksheet cells, 428–429
editing, 346
opening workbooks, 426
explained, 443
Paste, 51, 89–90, 426, 430
inserting, 341, 431

452
 Index

Print, 294 M
Redo, 426
Replace, 56 Macro dialog, 313, 433
restoring actions, 59 macros
ribbon, 427 accessing through buttons, 328
Save, 363, 426 assigning, 325
Save As dialog, 7 assigning to icons, 320–321
spelling checker, 59–60 assigning to Quick Access Toolbar buttons,
tables with default formatting, 65 318–319
Undo, 426 assigning to shapes, 320–321
undoing actions, 59 creating and modifying, 315–317, 333
Visual Basic Editor, 313–314 deleting, 317
Zoom in and out, 426 editing, 317
KPIs (key performance indicators) enabling and examining, 333
defining and displaying, 412–415, 423–424 examining, 312–315
explained, 443 explained, 444
using, 403 recording, 315
running, 317–322, 333–334
security levels, 309–312
L security settings, 311
stepping through, 313–314
Label form control, 323
using, 308–309
labels
magnification level, changing for worksheets,
setting apart, 104
35–36
using with worksheets, 21
mailto hyperlink, 444
landscape mode, 443
map charts, 222–226
languages, translating words in, 58–59, 61
mathematical equations, inserting, 248
LARGE operation, 146–147
mathematical operations, 76–78
line charts, 218–219, 233
Matrix SmartArt graphics, 239
linear forecasting, 404, 407
MAX function, 78, 146
linking
maximizing windows, 433
to charts, 346–347, 350
measures, defining and managing, 410–412, 423, 444
to data, 185–188, 191
MEDIAN function, 146
to documents or presentations, 336–338
menus, opening, 427
to files, 337–338
Merge and Center, 444
links, explained, 443
merging and unmerging cells, 20–22, 40, 112
list box, explained, 443
Microsoft 365 app keyboard shortcuts. See also
List Box form control, 323, 326–327
keyboard shortcuts
list rows, selecting randomly, 149. See also custom
actions, 439
lists
Backstage view, 436
List SmartArt graphics, 239
dialogs, 435–436
Live Preview feature, 49, 443
edit boxes within dialogs, 435–436
location data, tagging entries as, 224–225
finding and replacing, 439
location statistics, adding, 225–226
help, 440
locked cell, explained, 444
keyboard focus, 437–438
locking and unlocking cells, 358, 360
Open and Save As dialogs, 436
looking up data, 176–184, 191
panes and galleries, 438–439
ribbon, 437
tables, 438
windows, 434

453
Index

MIN function, 78, 146 Pareto charts, 218–219, 444

minimizing workbooks, 433 passwords, using, 356–359
[Link] function, 146 Paste gallery, 49
Move command, 432 Paste Name dialog, 432
moving Paste Options, 49, 444
cell ranges, 50 Paste Special dialog, 49, 52
cells, 430 pasting cell ranges from Clipboard, 51
and copying worksheets, 11 pasting content, 430
data in workbooks, 48–53 PDF files, saving workbook content as, 364–365
in the ribbon, 427 percentage (%) operator, precedence of, 76
shapes, 245 Percentage format, 431
multiplication (*) operator, precedence of, 76, 78 [Link] operation, 146–147
[Link] operation, 146–147
personal information, removing, 363
N Pick From List, 444
#NAME? error code, 92 Picture SmartArt graphics, 239
Name Manager, 73, 75 pie charts, 210
named range, explained, 444 pivot, explained, 444
named ranges PivotCharts, creating, 277–280, 282, 444. See also
creating, 72–75 charts
referencing in formulas, 82 PivotTable data
navigating worksheet cells, 428–429 showing and hiding, 262–270, 281
negation operator (-), precedence of, 76, 78 using in formulas, 273
Normal font, changing, 109 PivotTables. See also summarizing
notes, inserting, 430, 432 activating for editing, 261
number formats, 431 adding fields to, 261
numbers, improving readability of, 119–122, 134 banded rows, 277
numeric options, displaying in spin boxes, 328 conditional formatting, 274–276
numerical values, summarizing, 140 creating, 256–257, 260–261
creating from Data Model, 389–390
cross-tabular format, 259
O custom styles, 275
objects, hiding and displaying, 431 deferring updates, 262
odd and even pages, headers and footers on, 290 displaying and hiding, 261
[Link] SmartArt graphics, 239 editing, 270–273, 282
OneDrive service, 444 explained, 444
opening workbooks, 8, 426 features, 254–255
operators and precedence, 76–77 filtering, 262–268, 281
Option Button control, 323, 326, 330, 444 formatting, 273–277, 282
orientation, specifying for printing, 294 grand totals, 272
outline border, applying, 431 KPIs (key performance indicators), 415
outline symbols, displaying and hiding, 431 levels of detail, 268
outlining data, 168–172, 174 number formats, 275
organizing data for use in, 260
and PivotCharts, 278–280
P pivoting, 261
placing fields in, 257–258
page breaks, changing in worksheets, 295–296
removing fields from, 261
page printing order, changing, 297
renaming, 272
Page Setup dialog, opening, 297
reorganizing, 256, 259

454
 Index

slicers, 269–270 Q
styles, 276–277
subtotals, 272 [Link] operation, 146–147
using, 281 [Link] operation, 146–147
Value Field Settings dialog, 272–273 queries. See Power Query
plotting data in charts, 211, 213 Quick Access Toolbar
portrait mode, 444 adding macros to, 318–319
Power BI Desktop, 375 explained, 444
Power Pivot exporting to files, 33
clearing filters from, 387 managing, 28–33
closing, 380 resetting, 33
features, 374 Quick Analysis feature, 52–53, 431, 444
managing data with, 384–390
switching to Excel, 379
using, 376–377, 401–402
R
using to create relationships, 381 RAND function, 149
Power Query RANDBETWEEN function, 149
features, 374 range, explained, 444
importing data with, 395–400 recommended chart, 444
using, 402 recommended PivotTable, 444
Power View data analysis tool, 375 Record Macro dialog, opening, 315
PowerPoint, combining with Excel and Word Redo action, 426
content, 336–340, 349 #REF! error code, 92
precedent, explained, 444 refresh, explained, 444
presentations, linking to, 336–338 Relationship SmartArt graphics, 239
primary key, 176, 380–384, 444 relationships
print area, defining and adding, 301 creating between tables, 380–384
print preview, displaying, 298 explained, 444
printed pages, fitting worksheet contents to, illustrating, 238–244, 252
292–294 relative references, 87–88, 445. See also absolute
printing references
charts, 302–303, 306 removing. See deleting
copies, 299 renaming
orientation, 294 columns, 388
parts of worksheets, 300–301, 306 PivotTables, 272
preparing worksheets for, 291–297, 305 ribbon elements, 27
worksheet errors, 299 tables, 66, 389
worksheets, 297–299, 306 worksheets, 12
printing order, changing, 297 replacing and finding data, 53–57
Process SmartArt graphics, 239 resizing
processes and relationships, illustrating, 238–244, 252 charts, 214
PRODUCT function, 146 headers and footers, 291
properties. See also document properties images, 131–132
assigning, 6 shapes, 246–247
customizing, 9 tables, 63
explained, 444 timelines, 394
Protect Sheet dialog, 357–358 results, specifying, 202–203, 206
Pyramid SmartArt graphics, 239

455
Index

ribbon search filter

accessing and managing, 427 applying, 140, 142
adding commands to, 27 explained, 445
collapsing, 26 searching information, 58. See also finding
controlling display of, 23–25 seasonally repetitive patterns, calculating, 410
customizing, 27 selecting cell ranges, 429
displaying and hiding, 432 selection filter, explained, 445
expanding, 26, 427 series axis, explained, 445
explained, 445 shapes
hiding, 26 adding text to, 245, 247
managing, 23–28 assigning macros to, 320–321
navigating, 427 deleting, 249
removing elements from, 28 inserting and managing, 244–249
reordering command groups on, 26 moving, 245
reordering tabs on, 25 using in SmartArt graphics, 241–244
resetting, 28 sharing, explained, 445
ribbon configuration sheet tab, explained, 445
exporting to files, 33 Shift key. See keyboard shortcuts
importing from files, 33 shortcut menu, displaying, 433
ribbon elements, renaming, 27 single quote (‘), using with workbook names, 186
row and column headings, hiding and unhiding, 28 Size command, 433
row and column values, switching in charts, 213 Slicer, explained, 445
row height, modifying, 15–17 slides, inserting content in, 340
rows SMALL operation, 146–147
adding to tables, 65 Smart Lookup feature, 58, 61
deleting, 18 SmartArt graphics, 238–244
explained, 445 Solver, explained, 445
hiding and unhiding, 19 sort, explained, 445
inserting, 18 Sort & Filter menu, displaying, 162
maximum number of, 53 sorting. See also filtering
repeating in printed pages, 302 using custom lists, 166–167, 173–174
selecting, 50 values, 386
rows and columns worksheet data, 160–165, 167, 173
deleting, 16 sorting levels, copying, 165
inserting, 16 sorting rules, deleting, 165
spaces, including in workbook names, 186
sparklines
S accessing, 53
Save As dialog, 7, 433 explained, 445
saving summarizing data with, 235–238, 251
content as PDF files, 364–365, 370 spelling checker, 59–60, 431–432
copies of workbooks, 7–8 spin box
files, 5 displaying numeric options in, 328
workbooks, 363, 426 explained, 445
scale, explained, 445 form control, 323–325
scatter charts, 220–221 Start screen, 4–5
scenarios, applying, 194–198, 445 statistic charts, 218–220
schema, explained, 445 status bar, customizing, 33–34
scientific number format, 431 STDEV functions, 146
Scroll Bar form control, 323 stock charts, 221–222
stock images, adding to worksheets, 131

456
 Index

strikethrough formatting, applying and removing, 431 themes, applying and modifying, 113–115, 133, 445
submenus, moving to, 427 thesaurus, looking up words in, 61
SUBTOTAL function, 143–145, 146–147, 149 threaded comments, inserting, 430
subtotaling data, 168–172, 174 time and date, entering, 430–431
subtotals, explained, 445 timelines, displaying data on, 391–394, 402, 445
subtraction (–) operator, precedence of, 76, 78 tips, turning on and off, 433
SUM function, 78, 143–144, 146 tool tab, explained, 445
SUMIF function, 85–86 tool tabs, 27
SUMIFS function, 84–86 Top 10 filter, explained, 445
summarizing. See also PivotTables Total row, adding to tables, 63–65
data, 63, 101 Totals, accessing, 53
data to meet conditions, 83–87, 101 tracer arrows
data using sparklines, 235–238, 251 explained, 446
filtered data, 143–150, 157 removing, 95
numerical values, 140 translating text, 61
sunburst charts, 217, 445 treemap charts, 217, 446
trendlines
explained, 446
T using with charts, 234–235
tab colors, changing for worksheets, 14 Trust Center dialog, opening, 311
Tab key. See keyboard shortcuts Tufte, Edward, 236
Table AutoExpansion feature, 63
table styles, applying and modifying, 115–119, 134
tables U
accessing, 53 underlining text, 430
adding columns and rows to, 65 Undo action, 426
adding to Excel Data Model, 378 Undo Table AutoExpansion feature, 63
converting to cell ranges, 66
creating, 64–65, 431
creating relationships between, 380–384 V
with default formatting, 65 validating data, 150–156
defining, 62–66, 70 validation rule, explained, 446
excluding cells from, 62–63 value axis, explained, 446
expanding and contracting, 65 #VALUE! error code, 92
explained, 442 values
filtering, 138–143, 157 calculating, 76–82, 100
renaming, 66, 389 entering in cells, 43, 45–46
resizing, 63 extending series of, 45
summarizing data in, 63 finding in worksheets, 56
Table row, 63–64 replacing with values, 56
Total row, 65 variables, using with data tables, 199–202
templates VAR functions, 146
basing workbooks on, 7 VBA (Visual Basic for Applications), 433, 446
creating and distributing, 365–368, 370 Visual Basic Editor, accessing, 313–314
explained, 445 VLOOKUP function, 85, 176–179, 182–184
text
adding to shapes, 245, 247
changing color, 107 W
changing font, 106 Watch Window, using with formulas, 94, 96
changing size, 106 watches, explained, 446
italicizing, 430 waterfall charts, 221, 446

457
Index

webpages, creating hyperlinks to, 342–343 worksheets. See also blank worksheets
What-If Analysis adding images to, 129–132, 134
explained, 446 adding to workbooks, 10
selecting, 201 calculating, 433
window size, restoring, 432 changing magnification levels, 35–36
windows changing tab colors, 14
maximizing, 433 copying, 13
opening in workbooks, 37–38 Create A Copy checkbox, 11
switching between, 432 deleting, 15
Word, combining with Excel and PowerPoint explained, 446
content, 336–340, 349 finding values in, 56
words hiding and unhiding, 14
adding to dictionary, 61 hyperlinking, 340–346, 349–350
looking up in thesaurus, 61 inserting, 432–433
translating into languages, 58–59 modifying, 15–20, 39–40
workbook display, changing, 38 moving to workbooks, 13
workbook names, using single quotes (‘) with, 186 moving within workbooks, 12–13
Workbook Statistics dialog, 431 page breaks, 295–296
workbook themes, applying and modifying, page printing order, 297
113–115, 133, 445 preparing for printing, 291–297, 305
workbook views, displaying, 37 printing, 297–299, 306
workbook windows, arranging, 36–38 printing parts of, 300–301, 306
workbooks protecting, 356–361, 369
adding worksheets to, 10 scaling for printing, 294
closing, 9 zooming in and out of, 35–36
creating, 4–9, 39
displaying, 12
finalizing, 362–363, 370 X
minimizing, 433 x-axis, explained, 446
modifying, 10–15, 39 XLOOKUP function, 85, 176–179, 182–184
moving and copying, 11 .xlsb extension and macro, 308
moving data in, 48–53 .xlsm extension and macro, 308
opening, 8, 426 .xlsx extension and macro, 308
opening windows in, 37–38 .xltm extension and macro, 309
protecting, 356–361, 369 .xltx extension and macro, 309
renaming, 12 XML (Extensible Markup Language), 446
repositioning, 11
saving, 363, 426
saving copies of, 7–8 Y
switching to, 37 y-axis, explained, 446
worksheet area, maximizing, 26
worksheet data, sorting, 160–165, 167, 173.
See also data Z
worksheet errors, printing, 299
z-axis, explained, 446
zoom in and out, 35–36, 426

458