21,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Make Excel work for you with this brand new guide to spreadsheet essentials
Knowing a little about Excel is essential for almost every profession. Knowing a lot about Excel makes you one of the most valuable people in the office. Microsoft 365 Excel For Dummies helps you build your spreadsheet skills as it walks you through the basics of creating a spreadsheet, organizing data, performing calculations, and creating charts and graphs in Microsoft's powerful spreadsheet software. This brand new version of the book, written by an Excel guru and expert trainer, helps you develop the skills you need to impress your boss—or just to get things done faster. You'll also get ideas for using Excel to make daily life easier. Ready to become a number cruncher?
- Create your first spreadsheet and learn how to enter data
- Import data from other sources, sort tables, and organize information
- Run basic calculations and get more advanced with functions
- Build data visualizations, customize the look of your spreadsheets, and beyond
These days, almost everyone who works with computers needs to have basic knowledge of Excel—and more is usually better. Microsoft 365 Excel For Dummies will help you get there.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 649
Veröffentlichungsjahr: 2025
Ähnliche
Microsoft® 365 Excel® For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Microsoft 365 Excel For Dummies Cheat Sheet” in the Search box.
Table of Contents
Cover
Title Page
Copyright
Introduction
About This Book
Foolish Assumptions
Icons Used in This Book
Beyond the Book
Where to Go from Here
Part 1: Explaining Excel Fundamentals
Chapter 1: Getting Started with Spreadsheets
Exploring Excel’s User Interface
Entering and Editing Data
Handling Workbook Operations
Managing Worksheet Tasks
Collaborating with Others
Chapter 2: Carrying Out Basic Calculations
Understanding Operators
Exploring the Order of Operations
Contrasting Cell References
Replicating Formulas
Calculating Sums
Analyzing Data with Statistical Functions
Chapter 3: Formatting Cells and Worksheets
Applying Basic Formatting Commands
Exploring the Format Cells Dialog Box
Applying Cell Styles
Merging Cells
Wrapping Text
Tapping into Text Boxes
Inserting Images
Enhancing Accessibility
Adjusting Row Heights and Column Widths
Hiding and Unhiding Rows and Columns
Hiding and Unhiding Worksheets
Hiding and Unhiding Workbooks
Modifying Page Setup
Chapter 4: Sifting Through Data
Freezing Panes
Utilizing the Sort Feature
Removing Duplicates
Finessing the Filter Feature
Calculating with SUBTOTAL
Using the AGGREGATE Function
Summarizing Lists with the Subtotal Feature
Jumpstarting Insights with Quick Analysis
Chapter 5: Tackling Tables
Comparing Data Ranges to Excel Tables
Creating Excel Tables
Automating Formulas and Features
Unpacking Table Quirks
Troubleshooting Tables
Converting a Table to a Data Range
Part 2: Mastering Formulas and Functions
Chapter 6: Leveraging Lookup Formulas
Navigating with VLOOKUP
Hoisting HLOOKUP
Exploring MATCH and INDEX
Cruising with XLOOKUP
Troubleshooting Lookup Formulas
Chapter 7: Transforming Text and Numbers
Reworking Text without Retyping
Extracting or Splitting Text
Combining LEFT and FIND
Tinkering with TEXTBEFORE and TEXTAFTER
Swapping out Text and Formulas
Chapter 8: Harnessing Logic Functions
Making Basic Formulaic Decisions
Handling Error Values
Evaluating Data Based upon Criteria
Refining Advanced Formulaic Decisions
Chapter 9: Carving Data with Dynamic Arrays
Sifting Through Data Dynamically
Managing Spilled Ranges
Chapter 10: Tracing and Debugging Formulas
Tracking Precedent and Dependent Cells
Evaluating Formulas
Revealing Formula Underpinnings
Investigating Issues with Error Checking
Cleaning Up Circular References
Monitoring Changes via the Watch Window
Controlling When Excel Calculates
Part 3: Expanding Beyond the Basics
Chapter 11: Navigating Through Worksheets and Workbooks
Finding and Replacing Data within Cells
Exploring with the Navigation Task Pane
Jumping to Locations with Go To
Targeting Specific Cells with Go To Special
Navigating with the Name Box
Activating Worksheets
Reordering Worksheets
Splitting Worksheet Windows
Viewing Two or More Worksheets at Once
Applying Custom Views
Zooming In and Out
Chapter 12: Visualizing and Summarizing Data
Introducing Excel charts
Deconstructing and Customizing Charts
Spotlighting Trends with Sparklines
Unleashing the Magic of PivotTables
Chapter 13: Contemplating Conditional Formatting
Formatting with Quick Analysis
Decoding Conditional Formatting Options
Highlighting Cells Based on Value
Applying Top or Bottom Rules
Displaying Data Bars
Grading with Color Scales
Symbolizing with Icon Sets
Crafting Custom Rules
Removing Conditional Formatting
Managing Conditional Formatting Rules
Chapter 14: User-Proofing Excel Spreadsheets
Unlocking and Protecting Worksheet Cells
Safeguarding Workbook Structure
Ensuring Accuracy with Data Validation
Putting Data Entry Rules into Action
Protecting Data Validation Cells
Identifying Invalid Inputs
Removing Data Validation
Part 4: Automating Analysis
Chapter 15: Accessing Artificial Intelligence
Automating with Flash Fill
Exploring Insights with Analyze Data
Introducing Copilot Pro
Working with the Copilot Pro Task Pane
Utilizing Data Types
Extracting Data from Pictures
Interacting with ChatGPT
Chapter 16: Recording Macros
Contrasting Code Versus Automation
Getting Started with the Macro Recorder
Managing Macro Security
Maintaining Macros
Streamlining Repetitive Tasks
Taming the Macro Reference Beast
Unpacking the Personal Macro Workbook
Recovering Discarded Macros
Part 5: The Part of Tens
Chapter 17: Ten Timesaving Keyboard Shortcuts
Undoing Actions
Repeating and Reversing Commands
Saving Smarter
Closing Workbooks Efficiently
Selecting Data Quickly
Duplicating Data Instantly
Jumping to the Start of a Workbook
Switching Between Enter and Edit Modes
Toggling Cell Reference Types
Breaking Up Long Formulas and Text
Chapter 18: Ten Disaster Recovery Techniques
Deleting Temporary Files
Repairing Damaged Workbooks
Checking for Updates
Tracking Modifications with Version Numbers
Backing Up to Cloud-Based Drives
Ensuring Safety with Always Create Backup
Recovering Lost Work with AutoRecover
Securing Workbooks with Passwords
Auditing Edits with Show Changes
Storing Inputs with Scenario Manager
Index
About the Author
Connect with Dummies
End User License Agreement
List of Illustrations
Chapter 1
FIGURE 1-1: Microsoft Excel's user interface.
FIGURE 1-2: The Activate dialog box.
FIGURE 1-3: The Status Bar.
FIGURE 1-4: Use the Fill Handle.
FIGURE 1-5: The Insert dialog box.
FIGURE 1-6: The Move or Copy dialog box.
Chapter 2
FIGURE 2-1: Context menu options for the Fill Handle.
Chapter 3
FIGURE 3-1: The Format Cells dialog box.
FIGURE 3-2: The Cell Styles gallery.
FIGURE 3-3: The dreaded Page Layout View/Freeze Panes conflict prompt.
Chapter 4
FIGURE 4-1: The merged cells need to be the same size.
FIGURE 4-2: The Sort dialog box.
FIGURE 4-3: Sort results with the months arranged alphabetically, instead of se...
FIGURE 4-4: The Custom Lists dialog box.
FIGURE 4-5: The Sort Options dialog box.
FIGURE 4-6: The Remove Duplicates dialog box.
FIGURE 4-7: The SUM function versus the SUBTOTAL function.
Chapter 5
FIGURE 5-1: The Create Table dialog box.
FIGURE 5-2: The Go To dialog box.
FIGURE 5-3: The Modify Table Style dialog box.
FIGURE 5-4: The structured reference version of the Formula AutoComplete list.
FIGURE 5-5: A Clustered Column bar chart.
Chapter 6
FIGURE 6-1: The Function Arguments dialog box.
FIGURE 6-2: The Custom category Number tab in the Format Cells dialog box.
FIGURE 6-3: The AutoComplete list for structured reference formulas.
Chapter 7
FIGURE 7-1: The delimited version of Step 2 of the Text to Columns wizard gone ...
FIGURE 7-2: The fixed-width version of Step 2 of the Text to Columns wizard.
FIGURE 7-3: Examples of TEXTSPLIT in action.
Chapter 8
FIGURE 8-1: SUMIFS formula results based upon two criteria.
Chapter 9
FIGURE 9-1: The Table Design tab and Table Name field.
FIGURE 9-2: The AutoComplete list for a structured reference formula.
FIGURE 9-3: Dynamic array functions and formulas return unformatted cell conten...
FIGURE 9-4: Dynamic array amortization table adjusted to show one year.
Chapter 10
FIGURE 10-1: Tracer arrows identify precedent cells.
FIGURE 10-2: An indication that no cells refer to the currently selected cell.
FIGURE 10-3: The Go To Special dialog box.
FIGURE 10-4: The Evaluate Formula dialog box.
FIGURE 10-5: Error Checking Options menu.
FIGURE 10-6: The Error Checking dialog box.
FIGURE 10-7: Circular reference warning prompt.
FIGURE 10-8: The Watch Window dialog box.
FIGURE 10-9: Docked version of the Watch Window dialog box.
Chapter 11
FIGURE 11-1: The Find and Replace dialog box.
FIGURE 11-2: The Navigation task pane.
FIGURE 11-3: The Go To Special dialog box.
Chapter 12
FIGURE 12-1: The Recommended Charts tab of the Insert Chart dialog box.
FIGURE 12-2: The All Charts tab of the Insert Chart dialog box.
FIGURE 12-3: A standard Clustered Columns chart.
FIGURE 12-4: The Select Data Source dialog box.
FIGURE 12-5: The Move Chart dialog box.
FIGURE 12-6: The abbreviated version of the Paste Special dialog box.
FIGURE 12-7: The Create Sparklines dialog box.
FIGURE 12-8: The Recommended PivotTables task pane.
FIGURE 12-9: A PivotTable and the PivotTable Fields task pane.
FIGURE 12-10: An updated PivotTable.
Chapter 13
FIGURE 13-1: The Quick Analysis tool in Excel for Windows.
FIGURE 13-2: Borders tab of the Conditional Formatting version of the Format Ce...
FIGURE 13-3: The Greater Than dialog box.
FIGURE 13-4: The Data Bars submenu.
FIGURE 13-5: Data Bar settings in the New Formatting Rule dialog box.
FIGURE 13-6: The Color Scales submenu.
FIGURE 13-7: 2-Color Scale settings in the New Formatting Rule dialog box.
FIGURE 13-8: The Icon Sets submenu.
FIGURE 13-9: The New Formatting Rule dialog box.
FIGURE 13-10: Use a Formula to Determine Which Cells to Format settings in the ...
FIGURE 13-11: The Conditional Formatting Rules Manager.
Chapter 14
FIGURE 14-1: The sample worksheet to use with this chapter.
FIGURE 14-2: The Protection tab of the Format Cells dialog box.
FIGURE 14-3: The Protect Sheet dialog box in Excel for Windows.
FIGURE 14-4: The Unlock Range dialog box.
FIGURE 14-5: The Protect Structure and Windows dialog box.
FIGURE 14-6: The extend data validation prompt.
FIGURE 14-7: The Input Message tab of the Data Validation dialog box.
FIGURE 14-8: The Error Alert tab of the Data Validation dialog box.
FIGURE 14-9: The Stop style error alert dialog box.
FIGURE 14-10: The Warning style error alert dialog box.
FIGURE 14-11: The Information style error alert dialog box.
FIGURE 14-12: The multiple types of validation prompt.
Chapter 15
FIGURE 15-1: The Analyze Data task pane.
FIGURE 15-2: Data Type cell icon and the Insert Data field list.
FIGURE 15-3: The Data from Picture task pane.
Chapter 16
FIGURE 16-1: The Record Macro dialog box.
FIGURE 16-2: A macro-related security prompt in Excel's Message Bar.
FIGURE 16-3: The Macro Settings section of the Trust Center dialog box.
FIGURE 16-4: The blocked macros message bar prompt.
FIGURE 16-5: The Macro Options dialog box.
FIGURE 16-6: The confirmation prompt for replacing an existing macro.
FIGURE 16-7: The save prompt for the Personal Macro Workbook.
FIGURE 16-8: The prompt that asks if you want to throw your macro away.
Chapter 18
FIGURE 18-1: The General Options dialog box.
FIGURE 18-2: Excel treats workbook backups as potentially malicious.
Guide
Cover
Table of Contents
Title Page
Copyright
Begin Reading
Index
About the Author
Pages
iii
iv
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
365
366
367
368
369
370
371
372
373
374
375
376
377
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
Microsoft® 365 Excel® For Dummies®
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.
Media and software compilation copyright © 2025 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2025934653
ISBN 978-1-394-31710-3 (pbk); ISBN 978-1-394-31727-1 (ebk); ISBN 978-1-394- 31726-4 (ebk)
Introduction
Welcome to Microsoft 365 Excel! My goal in this book is to help you build efficient spreadsheets while maintaining data integrity, whether you're new to Excel or already have experience. For beginners, I guide you through getting oriented, and for experienced users, I aim to help you close any gaps in your knowledge. In my webinars, I often say, “Either you work Excel, or it works you!” Unfortunately, many users learn just enough to use Excel like a blunt instrument, so I chose the topics in this book specifically to help you move beyond that.
Microsoft 365 Excel is an application you install on your desktop computer. In contrast, Excel for the Web (available at www.office.com/launch/Excel/) runs directly in your browser, and Excel Mobile is designed for use on phones or tablets. While there is significant overlap between these versions, this book focuses specifically on the Windows and macOS versions of Excel.
Microsoft Excel was first released for the Apple Macintosh on September 30, 1985. The AI-infused application we use today only faintly resembles that early version. While it's highly unlikely that anyone still uses a nearly 40-year-old version of Excel, you may still be working with Excel 2021, Excel 2019, or an even earlier version. Much of what I cover in this book applies to those versions, but in some chapters, you may find yourself reading about features you'll have access to only after you upgrade to a newer version of Excel.
About This Book
Microsoft Excel remained relatively static until 2010, when the Office 365 platform was first introduced, later rebranded as Microsoft 365 in 2017. Before then, new features were introduced in Excel every two to three years, a long and often frustrating wait (at least for me). Those days are behind us, as Microsoft 365 now provides ongoing updates with new features and improvements. However, not everyone receives updates at the same time, as users are divided into different channels:
Current Channel:
Users receive monthly updates, with occasional out-of-band updates for critical fixes or security patches.
Monthly Enterprise Channel:
Like the Current Channel, this provides monthly updates for business users.
Semi-Annual Enterprise Channel (Preview):
This offers first access to features that will later be included in the Semi-Annual Enterprise Channel.
Semi-Annual Enterprise Channel:
Updates are released twice a year, ensuring that only thoroughly evaluated, stable features are introduced.
Beta Channel:
The cutting-edge channel where users who opt into the Microsoft 365 Insider program get early access to features still in development and testing.
Current Channel (Preview):
A more stable version of the Beta channel — for users that are edgy, but not quite that edgy.
To check which channel you're in:
Windows:
Go to File ⇒ Account — your channel and build appears next to the About Excel button.
macOS:
Choose Help ⇒ Check for Updates, then click Advanced. Your channel (but not build number) appears in the Preferences dialog box.
As you can see, Microsoft 365 Excel is a constantly evolving program. My editors and I have done our best to keep up with these changes and describe what, at times, has been a moving target. Think of it as Excel’s way of keeping track of its ever-evolving self — one incremental update at a time.
Because updates occur so often in Excel, by the time this book is published, some features and screens may have changed. (On second thought, make that will have changed.)
Then there’s the matter of the subscription levels:
Individuals and Families:
Microsoft 365 Personal and Family
Businesses:
Business Basic (web/mobile only), Business Standard, and Business Premium, Apps for Business
Enterprises:
E1 (web/mobile only), E3, E5
Education:
A1 (web/mobile only) A3, A5
Government:
G1, G3, G5 are like E1, E3, and E5, but add additional compliance for governmental requirements
All Microsoft 365 users have the same feature set for the most part, but some differences can arise, such as the Inquire add-in in the Enterprise versions that creates detailed reports on workbooks, can compare two versions of a workbook, can improve performance by cleaning up workbooks, and can build diagrams that show how cells and worksheets relate together. I discuss how to enable this add-in in Chapter 10.
If you'd prefer a one-time purchase for Excel, Microsoft Office 2024 provides static versions of Excel and other Office apps. However, this version does not include access to the artificial intelligence features available in Microsoft 365 and will not receive any future feature or function updates.
Here’s how the book is broken down:
Part 1
, “Explaining Excel Fundamentals,” helps you get oriented in Excel, covering basic calculations, formatting, data sorting and filtering, as well as the often-overlooked Table feature, which can enhance spreadsheet integrity and drop repetitive tasks.
Part 2
, “Mastering Formulas and Functions,” introduces lookup functions for retrieving data within a workbook, transforming text without retyping, and incorporating decision-making into formulas. You also learn how to create dynamic formulas that spill into additional cells and explore formula tracing and debugging tools.
Part 3
, “Expanding Beyond the Basics,” teaches you to navigate both small and large workbooks efficiently, create refreshable reports with PivotTables and PivotCharts, and apply data visualizations with conditional formatting. You also learn how to secure sensitive data and formulas.
Part 4
, “Automating Analysis,” introduces Excel's AI-driven features and teaches you to streamline tasks by recording macros.
Part 5
, “The Part of Tens,” provides ten useful keyboard shortcuts and equips you with ten disaster recovery techniques.
Before diving in, I must get some technical conventions out of the way:
Text that you're meant to type exactly as it appears in the book is formatted in monofont (which looks like text typed on an old typewriter).
Web addresses and programming code are also displayed in monofont. If you're reading a digital version of this book on a device connected to the internet, you can tap or click a web address to visit the site, like this:
www.dummies.com
.
To use Microsoft 365 Excel effectively, the minimum recommended technical requirements vary slightly depending on whether you're using a Windows or macOS device. For Windows users, a dual-core processor with a speed of 1.6 GHz or faster is needed, along with at least 4 GB of RAM for 64-bit systems (or 2 GB for 32-bit systems). You'll also need 4 GB of available hard disk space, a display with a resolution of at least 1280 x 768, and a DirectX 9-compatible graphics card with WDDM 2.0 or higher for Windows 10. The software is compatible with Windows 10, Windows 11, and recent versions of Windows Server, and an internet connection is needed for product activation, updates, and cloud features.
For macOS users, Microsoft 365 Excel needs an Intel processor or Apple Silicon (M1, M2, or later), with at least 4 GB of RAM and 10 GB of available disk space. A display resolution of 1280 x 800 or higher is recommended, and the operating system should be one of the three most recent versions of macOS. Like Windows, an internet connection is necessary for activation, updates, and some online features. Additionally, users on both platforms will need a current browser (such as Microsoft Edge, Chrome, Safari, or Firefox) and a Microsoft account to access all the features of Microsoft 365.
For best performance, particularly when working with large datasets or advanced tools like Power Query or PowerPivot, higher specifications — such as faster processors and added RAM — are recommended.
When I discuss a command to choose, I separate the elements of the sequence with a command arrow that looks like this: ⇒ . For example, when you see Data ⇒ Sort, it means you should activate the Data tab in Excel's ribbon interface and then choose the Sort command. Some ribbon commands have drop-down menus, and I’ll often include the corresponding keyboard shortcuts for added convenience.
The ribbon interface sometimes collapses command groups based on your screen resolution or the size of the Excel window. If a command you’re looking for seems to be missing, try expanding the window or clicking the drop-down arrow in the collapsed group to reveal hidden options.
Foolish Assumptions
I had to make some assumptions about you while authoring this book, so here are my educated guesses:
You want to learn how to use a spreadsheet or improve how you already use spreadsheets.
You may want to analyze your data using Excel features and/or worksheet functions, and perhaps automate repetitive tasks.
You have a personal computer running Windows 10 or 11 (I drafted this book in Windows 10 because I have my reasons) or a Mac running macOS 12 Monterey or later.
Ideally, you have Microsoft 365 Excel on your computer, but much of this book also applies to older versions of Excel. In fact, a good part of this book can be used with Excel for the Web or even Google Sheets.
Icons Used in This Book
Throughout the book, I use icons to draw your attention to key concepts that I don't want you to miss. Sometimes these icons highlight tips to help you save time, while in other cases, they focus on keeping your spreadsheets safe and secure.
This icon points out time-saving tricks or nuances that you may come across in Excel.
This icon highlights tricky aspects of Excel that you should be aware of and keep in mind as you work.
Caution: Contents Hot. Oh, wait — wrong type of warning! You won’t burn yourself if this book falls into your lap but do pay close attention to the warnings you encounter. They’re here to help you avoid issues that could cause problems in your spreadsheets or, more commonly, lead to unnecessary frustration.
At times, I may include some geeky details about Excel, your web browser, or your computer. If technical stuff isn’t your cup of tea, feel free to skip over it without missing out on the main content.
Think of this as an “oh, by the way” that points you to more details elsewhere in the book.
Beyond the Book
This book comes, with two bonus chapters on automating data transformation with Power Query and dealing with many of Excel's frustrating prompts. You can find the bonus chapters here: www.dummies.com/go/excelfd.
In addition, this book comes with a free access-anywhere Cheat Sheet, which includes extra tips; a list of features available in Excel for Windows, but not in macOS; and a couple of macOS-exclusive features. To get this Cheat Sheet, simply go to www.dummies.com and type Microsoft 365 Excel For Dummies Cheat Sheet in the Search box.
Where to Go from Here
Part 1 covers the basics of Microsoft Excel, and even experienced users may uncover blind spots they didn't realize they had. Continue to Part 2 for an in-depth look at using worksheet formulas to automate calculations and transform data. Part 3 takes you beyond the basics, showing how to manage large spreadsheets, visualize data, and user-proof your work. Part 4 focuses on automation, including artificial intelligence and Visual Basic for Applications (VBA) macros. The book wraps up with Part 5, where you learn to turbo-charge your work with keyboard shortcuts and protect your spreadsheets from the many things that can go wrong.
Part 1
Explaining Excel Fundamentals
IN THIS PART …
Get started with spreadsheets.
Carry out basic calculations.
Format cells and ranges.
Sort, filter, and sift through data.
Enhance your spreadsheets with Excel tables.
Chapter 1
Getting Started with Spreadsheets
IN THIS CHAPTER
Getting oriented in Excel
Creating and saving workbooks
Working with spreadsheet templates
Entering and editing data
Sharing workbooks with others
Orienteering is an outdoor sport where participants navigate between waypoints on an unfamiliar course using nothing but a map and a compass. Think of it as a real-world treasure hunt — except instead of gold, you might find a mud puddle or a tree you’re pretty sure you’ve seen before. If that sounds a little too outdoorsy, consider the IKEA alternative: winding through an endless maze of bookshelves and meatballs, desperately searching for the exit.
Unlike both of those scenarios, this Excel adventure won’t leave you lost or questioning your life choices. No map, no compass — just a series of waypoints to guide you through the wilds of worksheets:
Using screenshots to draw your attention to various parts of Excel
Entering and editing data in an Excel worksheet
Creating and saving Excel workbooks, both from scratch and by way of prebuilt Excel templates
Sharing spreadsheets, collaborating in real-time, and keeping the conversation going
Our first stop is a Rosetta Stone of sorts, decoding what you're seeing onscreen when you launch Excel.
Exploring Excel’s User Interface
Whenever you launch Excel, it eagerly greets you with a blank workbook featuring a single worksheet tab — like a fresh notepad just waiting for you to scribble all over it. You also see a tabbed menu interface across the top that Microsoft insists on calling the ribbon — because apparently “menu bar” wasn't fancy enough. The ribbon includes a set of static tabs, along with other contextual tabs that pop up when Excel deems them necessary (whether you agree or not). Just above this ribbon spectacle sits a collection of icons known as the Quick Access Toolbar — your personal stash of frequently used commands, which I cover in the “Customizing the Quick Access Toolbar” section later in this chapter.
Below the ribbon, shown in Figure 1-1, you see a row composed of three sections, because two wouldn't be enough, and four would be excessive:
Name Box: Most users rely on this area to see the address of the currently selected worksheet cell.
You can do a surprising number of things in the Name Box — more than two dozen! No need to feel overwhelmed; think of this as a peek into just how much Excel can do. Chapter 11 provides more details.
Insert Function button:
Excel's real power comes from its hundreds of worksheet functions, which let you crunch numbers from simple sums to complex calculations using inputs called arguments. Clicking this button opens a search-friendly dialog box, followed by another that walks you through building the formula step by step. The “
Leveraging worksheet functions
” section later in this chapter offers a straightforward way to get started.
Formula Bar:
This expandable section shows what's inside the selected worksheet cell. If a cell has a formula, you see the result in the cell, while the Formula Bar reveals the formula itself. You can edit cell contents directly in the formula bar or within the cell itself. To do so, double-click on a cell or press F2 (Windows) or Cmd+U (macOS).
Cells can contain up to 32,000 characters, including text, numbers, symbols, nonprintable characters like carriage returns, or formulas.
FIGURE 1-1: Microsoft Excel's user interface.
The Expand Formula Bar button sits on the right-hand side of the Formula Bar. Clicking it — or pressing Ctrl+Shift+U (Windows) or Cmd+Shift+U (macOS) — expands the Formula Bar to show up to 11 rows. Just keep in mind that this reduces the number of visible worksheet rows. You can also resize the Formula Bar by dragging its bottom edge, balancing the need to see more cell contents and keeping more of the worksheet in view.
Below the Formula Bar lies the worksheet grid, made up of a fixed number of rows and columns. Rows are numbered from 1 to 1,048,576, while columns follow a lettered system: A through Z for the first twenty-six, AA through AZ for the next set, continuing on until the final column, XFD.
Every worksheet contains 1,048,576 rows and 16,384 columns — over 17 billion cells in total — giving you plenty of room to get lost in data.
Traversing the ribbon
The ribbon in Excel is divided into two groups: the headliners — main tabs that are always present — and the special guests, known as tool tabs. The rock star tabs that never leave the road, er screen, include:
File:
Opens the Backstage View, where Excel handles all the behind-the-scenes business — opening, saving, printing, sharing workbooks, and tweaking settings.
Home: There's no place like the Home tab — it's where Excel keeps the most-used commands for formatting, editing, sorting, and filtering. Check here before you start clicking around like a lost tourist.
If all those icons are starting to blur together, think of ScreenTips as little cue cards. Hover over any ribbon or toolbar command, and Excel will remind you what it does. Windows users even get keyboard shortcuts — macOS users, not so much.
Insert:
Think of this as Excel's “Add Stuff” tab. Drop in PivotTables, PivotCharts, and slicers (see
Chapter 12
), or spice things up with shapes, images (safe-for-work only, please), and text boxes (see
Chapter 3
).
Page Layout:
Fine-tunes how your worksheet looks when printed — margins, scaling, and other settings that help you wrestle Excel into submission —
on paper, at least
(see
Chapter 3
).
Formulas:
The command center for Excel’s number-crunching wizardry. Dig into function libraries, troubleshoot formulas, and tweak calculation settings — because sometimes, Excel needs a little nudge to do the math right (see
Chapters 6
through
10
).
Data:
Where Excel turns into a data-guzzling machine. The Get & Transform Data and Queries & Connections sections let you pull in info from just about anywhere using Power Query (see the bonus chapter “Automating Data Transformation with Power Query” available at
www.dummies.com/go/excelfd
). The Data Types feature (see
Chapter 15
) adds self-updating “smart” cells — because why settle for static data that just sits there? The rest of the tab wrangles sorting, filtering (see
Chapter 4
), and what-if analysis, like scenario management (see
Chapter 18
).
Review:
This tab is like Excel's safety net — catching typos, flagging accessibility issues (see
Chapter 3
), adding comments (see the “
Collaborating with Others
” section of this chapter), and locking down spreadsheets to protect them from
ahem
enthusiastic but error-prone users (see
Chapter 14
).
View:
The toolkit for controlling what’s visible in Excel. Freeze rows and columns in place (see
Chapter 4
), switch between custom views and adjust zoom levels (see
Chapter 11
), or step into the world of automation with macros and Excel’s Macro Recorder (see
Chapter 16
). Because sometimes, seeing is believing.
Help:
Need somebody? Not just anybody? This tab (or, on macOS, the Help menu) connects users to support resources, troubleshooting information, and usage tips for Excel — so you can get by with a little help from this tab (or menu).
SEARCHING EXCEL’S MENUS
If you're feeling adrift, get back on firm ground with Excel's multipurpose Search feature. Access it in one of the following ways:
Click the Search field in Excel's title bar.Press Alt+Q (Windows) or Cmd+Ctrl+U (macOS).Choose from suggestions based on recent or common actions, or start typing to generate a dynamic list of commands. To search within the worksheet, enter a term and select Find in Worksheet (Windows) or Find (macOS).
A second Search the Menus field appears when you right-click the worksheet frame or any cell. This search is limited to ribbon commands — use Find instead when hunting for data (covered in Chapter 11).
Depending on your rights and licensing, the Automate tab may appear, granting access to Office Scripts for automating repetitive tasks with JavaScript-based scripts. Any user can enable the Developer tab, packed with tools for creating and editing macros (see Chapter 16) — perfect for those ready to tinker under Excel’s hood.
Despite its name, the ribbon isn’t particularly fluid — the built-in commands refuse to budge. If it’s making you feel tied up in knots, cut through the clutter by adding new command groups to existing tabs or creating entirely new tabs. Here’s how to try your hand at menu design:
Open the Customization dialog box:
Windows:
Choose File ⇒ Options ⇒ Customize Ribbon or right-click the ribbon and select Customize the Ribbon (opens the Excel Options dialog box).
macOS:
Choose Excel ⇒ Preferences ⇒ Ribbon & Toolbar (opens the Ribbon & Toolbar dialog box).
Show or hide tabs by toggling the checkboxes in the Customize the Ribbon list.
Edit an existing tab:
Select a tab from the Customize the Ribbon list (defaults to Main Tabs). Select Tool Tabs or All Tabs to explore further.
Click New Group (+ then New Group on macOS) to add a custom group.
Rename it by clicking Rename (… then Rename on macOS).
Add commands by selecting them from the Choose Commands list and clicking Add >> (> on macOS). Use << Remove (< on macOS) to remove commands.
To create a new tab, click New Tab (+ then New Tab on macOS), then rename it and add groups and commands as needed.
Here's a peek at some of the tool tabs that may make special appearances — stepping on stage when a task calls for them:
Header & Footer:
This tab takes the stage when you decide what should grace the top or bottom of each page — because every sheet deserves a proper introduction and a grand finale (see
Chapter 3
).
Analyze:
This tab steps into the spotlight for PivotTables and PivotCharts (see
Chapter 12
), giving you tools to connect, explore, and wrangle your data.
Design:
This tab makes a stylish appearance when you select a cell in an Excel table (see
Chapter 5
), a PivotTable, or click on a chart or PivotChart (all covered in
Chapter 12
). While “Design” may not always be in the name, similar variations appear for Sparklines, slicers, and Timelines (you guessed it, all in
Chapter 12
).
Format:
This feature-specific tab appears for charts, PivotCharts, shapes, and images — because sometimes your data needs a little wardrobe upgrade.
Query:
This tab enables you to edit, load, reuse, combine, and share data connections through Power Query — because why do the heavy lifting when you can make Excel do it for you? See the bonus chapter “Automating Data Transformation with Power Query” (available at
www.dummies.com/go/excelfd
).
Enterprise and government users can — and frankly should — enable the Inquire add-in. This powerhouse of a tool helps document workbooks, compare workbook versions, and fine-tune performance, making it a must-have for anyone wrangling complex spreadsheets.
Customizing the Quick Access Toolbar
The Quick Access Toolbar is like Excel's junk drawer — full of useful stuff, but only if you take the time to set it up. You can stash your favorite commands above or below the ribbon. By default, it includes AutoSave, Save, Undo, and Redo, which I cover later in the chapter. In Excel for Windows, tapping Alt turns your screen into an eye chart of alphanumeric shortcuts for every command on the toolbar and ribbon — macOS users must use their mouse. To make it your own:
Add a single command:
Windows:
Right-click a ribbon command and select Add to Quick Access Toolbar
macOS:
Unfortunately, you can't add commands on the fly, but you can use the Finetuning instructions
Finetuning:
Windows:
Go to File ⇒ Options ⇒ Quick Access Toolbar to open the Excel Options dialog box.
macOS:
Go to Excel ⇒ Preferences ⇒ Ribbon & Toolbar to open the Ribbon & Toolbar dialog box.
From there, you can shuffle icons, relocate the toolbar, and decide whether command labels should appear as ScreenTips.
Starting over
(if your customizations spiral into chaos):
Windows:
In the Excel Options dialog box, click Rest and choose whether to restore just the Quick Access Toolbar or reset all ribbon customizations.
macOS:
Click … in the Ribbon & Toolbar dialog box then reset the toolbar or include the ribbon in your do-over.
You already know that Ctrl is the go-to key for most keyboard shortcuts in Windows. Alt has some quirks, especially when numbers get involved. Tapping Alt lets you trigger Quick Access Toolbar shortcuts using either the number pad or the numbers row up top. However, hold down Alt while pressing a number on the number pad summons special characters instead — like Alt+7 giving you a bullet (•). Other numbers conjure up different symbols, which is either a handy trick or an unexpected detour, depending upon what you are trying to do.
One of my favorite areas to explore is Commands Not in the Ribbon list within the Choose Commands From list. This hidden trove contains hundreds of commands left off the ribbon — either due to space constraints or feature deprecation. Here are a few gems I often add to my Quick Access Toolbar:
AutoFilter:
A one-click wonder that filters a normal range of cells based on whatever's in the selected cell. But don't get any wild ideas — this trick doesn't work within Excel tables (see
Chapter 5
).
Custom Views:
Think of this as Excel's version of quick-change artist, letting you swap between saved worksheet and workbook layouts. I cover Custom Views in
Chapter 11
.
Full Screen:
Clears away the ribbon, Formula Bar, and Status Bar so you can focus on your data — or just make Excel look extra dramatic. Press Escape to bring everything back.
The Customize Quick Access Toolbar drop-down lets you decide whether to apply changes For All Documents (customizing the toolbar on this device) or for a specific workbook. Workbook-specific toolbars stick with the file, so they can follow you to other computers — like a loyal sidekick.
Activating worksheets
Chapter 11 takes a deep dive into navigating worksheets and workbooks, so I'll keep things brief here. Each document you create in Excel is called a workbook, which consists of one or more worksheets — each represented by a tab at the bottom of the worksheet grid. Think of worksheets as pages in a ledger, neatly (or not so neatly) organized within your workbook.
Here are a few ways that you can activate a worksheet tab:
Click the tab:
Simple and straightforward.
Sheet navigation arrows:
Found just above the Status Bar, these let you nudge the visible tabs to the left or right — provided you have more tabs than can fit comfortably on screen.
Activate dialog box:
Right-click the sheet navigation arrows to summon the Activate dialog box (
Figure 1-2
). Pick a worksheet from the list and click OK — or double-click a sheet name to skip the extra step.
The Activate dialog box only lists visible worksheets — so if a sheet is playing hide-and-seek, it won't show up there. To track down hidden worksheets, use the Navigation task pane (covered in Chapter 11). For more on making sheets disappear (or reappear), check out the upcoming “Hiding or unhiding worksheets” section.
FIGURE 1-2: The Activate dialog box.
Exploring the Status Bar
The Status Bar — Excel's command center that hides out in plain sight — keeps you in the loop with various feedback mechanisms and quick-access tools. Here's a rundown of the default options (as shown in Figure 1-3):
Cell Mode Indicator: Your cell's current mood, expressed in one word:
Ready:
Just sitting there, minding its own business.
Enter:
Actively receiving data or a formula.
Point:
Waiting for you to pick cells for a formula, like a kid in a dodgeball lineup.
Edit (Windows Only):
Mid-editing, hoping you don't regret your choices.
Chapter 17 gets into the nitty-gritty of Enter, Point, and Edit modes.
AutoFilter Status:
Tells you how many records match your filter criteria — so you're not left wondering why half your data mysteriously vanished. Filtering gets the full treatment in
Chapter 4
.
Calculate Status: Signals that some values may be outdated because Automatic Calculation is turned off or when Excel enters Manual Calculation mode due to a large data set. Clicking it or pressing F9 (Windows only) recalculates outdated formulas in the open workbook.
Press Ctrl+Alt+F9 (Windows) or Cmd+Shift+= (macOS) to force a full recalculation of all formulas across all open workbooks. Windows users can also press Ctrl+Shift+Alt+F9 to rebuild the dependency tree — an internal structure that tracks relationships between formulas and the cells they reference — and then recalculate everything. Mac users can achieve a similar result by toggling Manual Calculation mode (via Excel ⇒ Preferences ⇒ Calculation) and then switch back to Automatic to force a full recalculation.
Unlike most Status Bar features, the AutoFilter and Calculate settings cannot be toggled on or off — they appear only when relevant, like pop-up guests in your spreadsheet party.
Accessibility Assistant:
Indicates if an accessibility issue has been detected — click the icon to open the Accessibility Checker (
Chapter 3
) where you can review and fix potential concerns so that your workbook is inclusive and easy to navigate for all users.
AutoCalculate Functions: Instantly crunches numbers when you select multiple cells, serving up average, count, numerical count, minimum, maximum, and sum — like a tiny data butler.
Click any calculation in the Status Bar to copy the value to the clipboard, letting you paste it elsewhere like the Excel wizard you are.
View Modes:
Located out on the right-hand side, these buttons let you switch between Normal (standard spreadsheet view), Page Layout (Excel's best guess at how your data will break across pages), and Page Break Preview (a true print preview, complete with headers and footers, so you can see exactly what will make it on the printed page).
Zoom Slider:
Don't worry, this won't pull you into yet another online meeting. Located on the far right, this lets you zoom in until your data takes over the screen or zoom out until it vanishes into microscopic oblivion. In Windows, percentage zoom level appears next to it — click the number to open the Zoom dialog box for precise control.
FIGURE 1-3: The Status Bar.
You can customize the Status Bar by turning features on or off. Just right-click anywhere on it and toggle the options you want. For example, I often disable the Zoom Slider because I inevitably graze it while using the horizontal scroll bar — instantly throwing my worksheet into either billboard mode or microscopic territory, neither of which is particularly helpful. Other options you may wish to enable include:
Sheet Number:
Displays where the current worksheet stands in the lineup — along with the total number of sheets in the workbook. It’s a handy way to confirm whether you’re on Sheet 3 of 27 or just hopelessly lost.
Workbook Statistics:
Click to open a dialog box that provides a quick summary of key details of the workbook, including the number of sheets, cells with data, tables, charts, and more.
Caps Lock, Num Lock, Scroll Lock Indicators: Letting you know if you've purposefully engaged one of these settings — or if you're typing LIKE YOU'RE SHOUTING and wondering why.
Scroll Lock is Excel for Windows's little gremlin, waiting for the perfect moment to ruin your day. When active, the arrow keys stop moving the selection and instead sends the whole worksheet gliding around like a greased-up air hockey puck. To disable it, press ScrLk again — if your keyboard even has one. If not, search for “On-Screen Keyboard” in Windows, and click ScrLk to wrestle back control.
Macro Recording:
When enabled, a button appears that allows you to start or stop the Macro Recorder — because why not make Excel do the heavy lifting? More on that in
Chapter 16
.
Entering and Editing Data
If you're itching to create a spreadsheet from scratch, you're in luck — after touring the Excel interface, it's time to build a simple task tracker. This example introduces basic data entry, formatting, and formulas while providing a preview of features explored in more detail later in the book.
Starting a task tracker
Here's how to get started with building a task tracking spreadsheet:
Add the following column titles (referred to as headers in Excel) to a blank Excel worksheet — if you don’t already have one open, try closing and reopening Excel, or check the “
Creating new workbooks
” section later in this chapter:
Cell A1:
Task
Cell B1:
Due Date
Cell C1:
Priority
Cell D1:
Status
Cell E1:Time Spent (Hours)
Column headers organize data, make the spreadsheet easier to read, and improve ease of use with certain Excel features, such as sorting lists (Chapter 4) and report writing with PivotTables (Chapter 12).
Enter the following example tasks (inspired by “The Jersey Shore”) into the cells below:
A2:
Gym
A3:
Tan
A4:
Laundry
A5:
Party
In column B, add due dates for each task:
Type 1/1 (the one day of the year that some folks actually go to the gym) in cell B2, then press Enter.
In unformatted cells, Excel converts entries in m/d or mm/dd format to d-mmm, with the hidden year defaulting to the current year. To display the full date in m/d/yyyy format, select Home ⇒ Number Format drop-down ⇒ Short Date.
Applying the Short Date format in advance to cells allows you to save keystrokes by omitting the year for dates within the current year.
Type 04/01/2026 in cell B3, then press Enter.
Excel pranks you by dropping the leading zeroes — see Chapter 2 for how to display them if needed.
Overwrite the values in cells B2:B3 with yesterday's date and today's date, respectively, using the m/d format.
Excel adds the current year automatically since both cells have date formats applied.
Drag the Fill Handle from B2, shown in Figure1-4, to cell B5 to fill the series of dates.
To create a series of month-end dates, enter two consecutive month-end dates in adjacent cells, select both, and then drag the Fill Handle down. Excel will recognize the pattern and extend it accordingly. This works for any data where Excel can identify a logical sequence.
Use column C to track priority:
C2:
High
C3:
Medium
C4:
Low
C5:High
As you may have noticed in cell D5, Excel can autofill entries based on similar entries within the current column of the current region ⇒ simply press Enter when the desired entry appears after typing matching characters.
Use column D to track status:
D2:
Completed
D3:
In Progress
D4:
Pending
D5:Never Ending
Chapter 14 covers creating in-cell drop-down lists with the Data Validation feature, streamlining data entry and ensuring consistent inputs.
Use column E to track hours:
E2:
2
E3:
1.5
E4:
3
E5:
4
FIGURE 1-4: Use the Fill Handle.
Applying basic formatting
A common next step is to dress up data:
Select the header cells (a necessary step before applying formatting):
Mouse action:
Click once on cell A1, hold down the left mouse button, and drag across to cell E1.
Keyboard action: Use the Up Arrow key to return to cell A1, hold down Shift, and the Right Arrow to select across to cell E1.
To select the entire contiguous block of cells that surround the active cell, press Ctrl+A (Windows) or Cmd+A (macOS) to select the current region. If you press the shortcut again, Excel selects the entire worksheet.
Apply basic formatting (see
Chapter
2
for more advanced formatting options):
Bold:
Choose Home ⇒ Bold or press Ctrl+B (Windows) or Cmd+B (macOS).
Underline: Choose Home ⇒ Underline or press Ctrl+U (Windows) or Cmd+U (macOS).
Some commands, such as Underline, include a drop-down button that provides additional functionality, such as the Double Underline command.
Center: Choose Home ⇒ Center to align the text in the center of each cell. While there's no built-in shortcut, see the “Customizing the Quick Access Toolbar” section earlier in this chapter to create your own.
Many formatting commands function as toggles, such as Bold or Underline, while others require selecting an alternative option, such as Left Align when choosing not to center text in a cell.
Select cell E1, then choose Home ⇒ Wrap Text.
Excel automatically adjusts the row height to ensure all data within the cell is visible.
Line breaks for wrapped text are based on the column width. To insert a manual line break, press Alt+Enter (Windows) or Option+Return (macOS).
To format the hours with two decimal places, select cells E2:E5, and then choose Home ⇒ Comma Style.
If necessary, adjust the decimal places by clicking Home ⇒ Increase Decimal or Home ⇒ Decrease Decimal.
To adjust the width of column D, click the letter D at the top of the worksheet to select the entire column, and then go to Home, and then choose Home ⇒ Format ⇒ AutoFit Column Width.
Sometimes Excel misinterprets your intent. For example, selecting cell D4 and choosing Home ⇒ Format ⇒ AutoFit Column Width reduces the column width to fit the seven characters in Pending, instead of displaying Never Ending in full, as you might have expected. To avoid this, select the entire column or the relevant range first, or double-click the column's right edge when the double-headed arrow appears to auto-fit to the widest cell contents. See Chapter 3 for more on adjusting column widths.
Leveraging worksheet functions
Worksheet functions are Excel's built-in magic tricks — prepackaged formulas that crunch numbers, manipulate data, and make your life easier. They work by using specific values called arguments, which you feed into the function like ingredients in a recipe. Whether you're doing simple math or wrangling a monster-sized data set, these functions have your back. You'll find them hanging out in the Function Library group of the Formulas tab of the ribbon. Some standout examples include:
Arithmetic:
SUM, AVERAGE (
Chapter 2
)
Lookups:
VLOOKUP, HLOOKUP, XLOOKUP (
Chapter 6
)
Text manipulation:
UPPER, LOWER, TEXTSPLIT, SUBSTITUTE (
Chapter 7
)
Logical operations:
IF, IFS, SWITCH, CHOOSE (
Chapter 8
)
Data analysis:
UNIQUE, SORT, FILTER (
Chapter 9
)
Most worksheet functions are a single string of alphanumeric characters, but some functions come in two parts separated by a period, followed by parentheses. Most worksheet functions require at least one argument — a cell reference, text, numbers, or another even formula — neatly tucked inside the parentheses like a well-wrapped data burrito.
For instance, the SUM function is an ideal way to add a total to cell E6. This can be done in two ways. The first is using the AutoSum command:
Select the cell where the total should appear; for example, E6.
Choose Home ⇒ AutoSum.
The second way is to use a manual formula:
Type
=SUM(
in cell E6 to start the formula.
Select cells E2:E5 using one of these methods:
Drag the mouse across E2:E5
Use the Up Arrow key to navigate to cell E5, hold Shift, then navigate to cell E2
Manually type
E2:E5
or
E5:E2
Type
)
to close the formula, and then press Enter.
Using either approach, the completed formula =SUM(E2:E5) appears. The SUM function has 255 arguments, but most users only use the first one:
number1:
The first range or amount to include in the total, for example, E2:E5.
number2..255: Optional additional ranges or amounts.
Always reference cell ranges when possible, such as =SUM(E2:E6) instead of going full-on manual with =SUM(E2,E3,E4,E5) or, worse, writing a formula that screams inefficiency =SUM(E2+E3+E4+E5). Using a range ensures that if you insert a row between rows 2 and 5, the new value is automatically included in the total — sparing you from a future forehead-smacking moment when your total is mysteriously off.
The Table feature (Chapter 5) includes a Total Row option, which creates total rows without manual formulas — and better yet, it automatically updates when additional amounts are added to the list. No extra effort (or forehead-smacking) required!
Here's how to create a slightly more complex formula:
Type Completion % in cell G1.
Add this formula to cell G2: =COUNTIF(D2:D5,"Completed")/COUNTA(A2:A5).
The COUNTIF function (Chapter 8) counts the number of instances of the word “Completed” in cells D2:D5, while the COUNTA function counts the number of non-blank cells in A2:A5. As covered in Chapter 2, the / operator performs division.
Similarly named worksheet functions can return wildly different results, so don't let Excel lull you into a false sense of security. Take COUNT, for example — it only tallies up numeric values, meaning =COUNT(A2:A5) would return a big fat zero if the range contains nothing but text. So, if you were expecting a number and got ghosted instead, now you know why.
Select cell G2 and then choose Home ⇒ Percent Style to format the result as a percent.
Handling Workbook Operations
As noted earlier in this chapter, launching Excel conjures up a blank workbook featuring a single worksheet tab. You can dive straight into data entry or open an existing workbook instead. If you open an existing workbook, Excel discreetly sweeps that initial blank workbook away, like a magician making a coin disappear, no dramatic hand flourish required. Because you've likely put some effort into building a worksheet, let’s make sure it’s saved for later use.
Saving Excel workbooks
The more time I spend working, the greater the odds a workbook will mysteriously close without saving. It's all too easy to get lost in a spreadsheet rabbit hole and forget to hit save — until it's too late. If you’re used to online tools like Google Sheets, Excel’s file management can feel like a rude awakening when you realize your hard work isn't automatically preserved. Here’s how to make sure that doesn’t happen:
