22,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Turn Excel into an unstoppable data-and number-crunching machine
Microsoft Excel is the Swiss Army knife of apps. With over 470 built-in functions and countless custom formulas, the program can help make you the smartest guy or gal in any room. And now that it's been supercharged with Copilot—Microsoft's AI-powered helper—it's even easier to produce accurate and useful results anywhere, anytime.
Best of all, it doesn't take an advanced degree in mathematics or data science to take full advantage of Excel's functionality. Just grab a copy of this latest edition of Excel Formulas & Functions For Dummies and get a flying start on the Excel functions and formulas that power up your data superpowers. With this book, you'll:
- Learn to create and use hundreds of formulas and functions, correct common mistakes, and make calculations
- Discover how to analyze data and calculate statistics, and even work with dates and times
- Use the ever-evolving, AI-powered Copilot to expand Excel's functionality and make it easier to use
Get ready to transform your copy of Excel at home or at work into an unstoppable toolkit equipped for almost any occasion. Grab a copy of Excel Formulas & Functions For Dummies today!
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 485
Veröffentlichungsjahr: 2025
Ähnliche
Microsoft® 365 Excel® Formulas & Functions For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Microsoft® 365 Excel® Formulas & Functions 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: Getting Started with Excel Formulas and Functions
Chapter 1: Tapping into Formula and Function Fundamentals
Working with Excel Fundamentals
Gaining the Upper Hand on Formulas
Using Functions in Formulas
Chapter 2: Saving Time with Function Tools
Getting Familiar with the Insert Function Dialog Box
Finding the Correct Function
Entering Functions Using the Insert Function Dialog Box
Directly Entering Formulas and Functions
Chapter 3: Saying “Array!” for Formulas and Functions
Discovering Arrays
Using Arrays in Formulas
Changing the Shape of Arrays
Working with Functions That Return Arrays
Chapter 4: Fixing Formula Boo-Boos
Catching Errors As You Enter Them
Auditing Formulas
Watching the Watch Window
Evaluating and Checking Errors
Making an Error Behave the Way You Want
Part 2: Doing the Math
Chapter 5: Calculating Loan Payments and Interest Rates
Understanding How Excel Handles Money
Figuring Loan Calculations
Chapter 6: Appreciating What You’ll Get, Depreciating What You’ve Got
Looking into the Future
Depreciating the Finer Things in Life
Measuring Your Internals
Chapter 7: Using Basic Math Functions
Adding It All Together with the SUM Function
Rounding Out Your Knowledge
Leaving All Decimals Behind with INT
Leaving Some Decimals Behind with TRUNC
Looking for a Sign
Ignoring Signs
Chapter 8: Advancing Your Math
Using PI to Calculate Circumference and Diameter
Generating and Using Random Numbers
Ordering Items
Combining
Creating a Sequence
Raising Numbers to New Heights
Multiplying Multiple Numbers
Using What Remains with the MOD Function
Summing Things Up
Getting an Angle on Trigonometry
Asking Copilot For Help
Part 3: Solving with Statistics
Chapter 9: Throwing Statistics a Curve
Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE
Deviating from the Middle
Analyzing Data with Percentiles and Bins
Going for the Count
Chapter 10: Rolling the Dice on Predictions and Probability
Modeling
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
What’s Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions
Using NORM.DIST and POISSON.DIST to Determine Probabilities
Part 4: Dancing with Data
Chapter 11: Dressing Up for Date Functions
Understanding How Excel Handles Dates
Formatting Dates
Making a Date with DATE
Breaking a Date with DAY, MONTH, and YEAR
Converting a Date from Text
Finding Out What TODAY Is
Determining the Day of the Week
Working with Workdays
Chapter 12: Keeping Well-Timed Functions
Understanding How Excel Handles Time
Formatting Time
Deconstructing Time with HOUR, MINUTE, and SECOND
Finding the Time NOW
Chapter 13: Using Lookup, Logical, and Reference Functions
Testing on One Condition
Choosing the Right Value
Let’s Be Logical
Finding Where the Data Is
Looking It Up
Chapter 14: Digging Up the Facts
Getting Informed with the CELL Function
Getting Information about Excel and Your Computer System
Finding What IS and What IS Not
Getting to Know Your Type
Chapter 15: Writing Home about Text Functions
Breaking Apart Text
Putting Text Together
Changing Text
Comparing, Finding, and Measuring Text
Part 5: The Part of Tens
Chapter 16: Ten Tips for Working with Formulas
Master Operator Precedence
Display Formulas
Fix Formulas
Use Absolute References
Turn Calc On/Turn Calc Off
Use Named Areas
Use Formula Auditing
Use Conditional Formatting
Use Data Validation
Create Your Own Functions
Chapter 17: Ten Ways to Get Fancy with Excel
Calculating Data from Multiple Sheets
Getting Data from the Internet
Determining the Needed Number
Removing Duplicates
Getting to the Last Row of Your Data
Freezing Panes
Splitting a Worksheet
Filling Cells
Adding Notes to Cells
Getting More Information about a Workbook or Worksheet
Chapter 18: Ten Really Cool Functions
Work with Hexadecimal, Octal, Decimal, and Binary Numbers
Convert Units of Measurement
Find the Greatest Common Divisor and the Least Common Multiple
Easily Generate a Random Number
Convert to Roman Numerals
Factor in a Factorial
Determine Part of a Year with YEARFRAC
Find the Data TYPE
Find the LENgth of Your Text
Just in CASE
Index
About the Author
Connect with Dummies
End User License Agreement
List of Tables
Chapter 1
TABLE 1-1 Basic Formulas
TABLE 1-2 Referencing Cells
TABLE 1-3 Order of Operations
TABLE 1-4 Error Types
TABLE 1-5 Using Functions in Formulas
TABLE 1-6 Arguments in Functions
Chapter 2
TABLE 2-1 Function Categories in the Insert Function Dialog Box
Chapter 4
TABLE 4-1 Valid Formulas That Return Incorrect Answers
Chapter 6
TABLE 6-1 Depreciation Methods
Chapter 7
TABLE 7-1 Using the ROUND Function
TABLE 7-2 Using the ROUNDUP and ROUNDDOWN Functions
TABLE 7-3 Using FLOOR and CEILING for Sophisticated Rounding
TABLE 7-4 Rounding to Even or Odd Integers
Chapter 8
TABLE 8-1 Using INT and ROUND to Process Random Values
TABLE 8-2 The Power of Raising Numbers to a Power
TABLE 8-3 Argument Values for the SUBTOTAL Function
Chapter 9
TABLE 9-1 Using Criteria with the COUNTIF Function
Chapter 11
TABLE 11-1 WEEKDAY Returned Values
Chapter 12
TABLE 12-1 How Excel Represents Time
Chapter 13
TABLE 13-1 Using the IF Function
TABLE 13-2 Using the ADDRESS Function
TABLE 13-3 Using ROW and COLUMN
TABLE 13-4 Using ROWS and COLUMNS
Chapter 14
TABLE 14-1 Selecting the First Argument for the CELL Function
TABLE 14-2 Returned Values for the format Argument
TABLE 14-3 Using INFO to Find Out about Your Computer or Excel
TABLE 14-4 Using the IS Functions to See What Really Is
TABLE 14-5 Returning Info about Errors
TABLE 14-6 Returning Info about Data in a Cell
TABLE 14-7 Getting a Number of an Error
Chapter 15
TABLE 15-1 How MID Works
TABLE 15-2 TEXTJOIN arguments
TABLE 15-3 The Rounding Feature
TABLE 15-4 Formatting Options for the TEXT Function
TABLE 15-5 Applying the SUBSTITUTE Function
TABLE 15-6 Removing Spaces
TABLE 15-7 Changing Text Case
TABLE 15-8 Comparing FIND and SEARCH
TABLE 15-9 Finding One String inside Another String
TABLE 15-10 Using the SEARCH Function
List of Illustrations
Chapter 1
FIGURE 1-1: The Info tab shows details about your Excel file.
FIGURE 1-2: Looking at a workbook and worksheets.
FIGURE 1-3: Changing the name of a worksheet.
FIGURE 1-4: Inserting a new worksheet.
FIGURE 1-5: Getting to know the Ribbon.
FIGURE 1-6: Eyeing the Watch Window.
FIGURE 1-7: Looking at what goes into a worksheet.
FIGURE 1-8: Selecting a range of cells.
FIGURE 1-9: Adding a name to the workbook.
FIGURE 1-10: Using the Name Box to find the named area.
FIGURE 1-11: Trying a table.
FIGURE 1-12: Formatting data.
FIGURE 1-13: Using the Format Cells dialog box for advanced formatting options.
FIGURE 1-14: Entering a formula that references cells.
FIGURE 1-15: Completing the formula.
FIGURE 1-16: A finished formula.
FIGURE 1-17: Getting ready to drag the formula down.
FIGURE 1-18: Populating cells with a formula by using the fill handle.
FIGURE 1-19: Getting a message from Excel.
FIGURE 1-20: Entering the AVERAGE function.
FIGURE 1-21: Using the Insert Function dialog box.
FIGURE 1-22: Getting ready to enter some arguments to the function.
FIGURE 1-23: Using RefEdit to enter arguments.
FIGURE 1-24: Completing the function entry.
FIGURE 1-25: Math was never this easy!
FIGURE 1-26: Nesting functions.
FIGURE 1-27: Getting a result from nested functions.
Chapter 2
FIGURE 2-1: Use the Insert Function dialog box to easily enter functions in a w...
FIGURE 2-2: The AutoSum button offers quick access to basic functions and the I...
FIGURE 2-3: Selecting a function.
FIGURE 2-4: Confirming that no arguments exist with the Function Arguments dial...
FIGURE 2-5: Populating a worksheet cell with today’s date.
FIGURE 2-6: Preparing to multiply some numbers with the PRODUCT function.
FIGURE 2-7: Ready to input function arguments.
FIGURE 2-8: Getting instant results in the Function Arguments dialog box.
FIGURE 2-9: Getting the final answer from the function.
FIGURE 2-10: Adding a table and a named area to a worksheet.
FIGURE 2-11: Entering arguments.
FIGURE 2-12: Calculating a sum based on cell and range references.
FIGURE 2-13: Entering a formula in the Formula Box has its conveniences.
FIGURE 2-15: Completing the direct-in-the-cell formula entry.
FIGURE 2-14: Entering functions has never been this easy.
FIGURE 2-16: Setting Formula AutoComplete.
Chapter 3
FIGURE 3-1: Creating a named area with the New Name dialog box.
FIGURE 3-2: A stock portfolio.
FIGURE 3-3: Calculating the value of a stock portfolio the old-fashioned way.
FIGURE 3-4: Calculating the value of a stock portfolio using an array function.
FIGURE 3-5: Making an easy calculation using an array formula.
FIGURE 3-6: Expanding an array or range.
FIGURE 3-7: Shrink an array with DROP.
FIGURE 3-8: Only a small section of this array is relevant.
FIGURE 3-9: The TAKE function has created an array of just the needed informati...
FIGURE 3-10: Using HSTACK and VSTACK.
FIGURE 3-11: Transposing data.
FIGURE 3-12: Preparing an area to receive transposed data.
FIGURE 3-13: Completing the function.
FIGURE 3-14: Transposed data after formatting.
FIGURE 3-15: Using the Paste Special dialog box to transpose data.
Chapter 4
FIGURE 4-1: Using parentheses in a formula.
FIGURE 4-2: Fixing mismatched parentheses.
FIGURE 4-3: Correcting a circular reference.
FIGURE 4-4: Setting calculation and iteration options.
FIGURE 4-5: Hunting down circular references.
FIGURE 4-6: Browsing for an unfound external workbook.
FIGURE 4-7: Using the Workbook Links pane to correct external reference problem...
FIGURE 4-8: Confirming that you mean to break links.
FIGURE 4-9: Calculating a sum with no possible error.
FIGURE 4-10: Excel detects a possible error.
FIGURE 4-11: Deciding what to do with the possible error.
FIGURE 4-12: Setting error-handling options.
FIGURE 4-13: Understanding precedents and dependents.
FIGURE 4-14: Tracing formulas.
FIGURE 4-15: Examining the components of a complex formula.
FIGURE 4-16: Using the Watch Window to keep an eye on a formula’s result.
FIGURE 4-17: Evaluating a formula.
FIGURE 4-18: Checking the cause of an error.
FIGURE 4-19: Two ways to prevent an error from being seen.
Chapter 5
FIGURE 5-1: Using the Format Cells dialog box to control numeric display.
FIGURE 5-2: Viewing regional settings.
FIGURE 5-3: Customizing how numeric values are handled.
FIGURE 5-4: The PMT function calculates the loan payment amount.
FIGURE 5-5: The NPER function calculates the number of payments for a loan.
FIGURE 5-6: The PDURATION function calculates the number of payments for a loan...
FIGURE 5-7: The RATE function calculates the periodic interest rate.
FIGURE 5-8: The PV function calculates the principal amount of a loan.
Chapter 6
FIGURE 6-1: Earning extra money in an investment.
FIGURE 6-2: Depreciating an asset.
FIGURE 6-3: Offsetting depreciation periods from the calendar.
FIGURE 6-4: Calculating the return on a business venture.
FIGURE 6-5: Comparing business opportunities.
FIGURE 6-6: Calculating internal rate of return with several cash flows.
Chapter 7
FIGURE 7-1: Using the SUM function to add noncontiguous numbers.
FIGURE 7-2: Calculating a sum from a range of cells.
FIGURE 7-3: Calculating a sum of multiple ranges.
FIGURE 7-4: Using AutoSum to guess a range for the SUM function.
FIGURE 7-5: Using AutoSum to work with other popular functions.
FIGURE 7-6: Using FLOOR or CEILING to round to a desired multiple.
FIGURE 7-7: Using INT to drop unnecessary decimals.
FIGURE 7-8: Using SIGN to sum amounts correctly.
Chapter 8
FIGURE 8-1: Setting worksheet calculation options.
FIGURE 8-2: Putting the PRODUCT function to work.
FIGURE 8-3: Using MOD to find specific values.
FIGURE 8-4: Working with the SUBTOTAL function.
FIGURE 8-5: Getting SUBTOTAL to ignore hidden values.
FIGURE 8-6: Following the steps used by SUMPRODUCT.
FIGURE 8-7: Being productive with SUMPRODUCT.
FIGURE 8-8: Using SUMIF for targeted tallying.
FIGURE 8-9: Using SUMIFS to get a multiple filtered sum.
FIGURE 8-10: Using SIN, COS, and TAN functions.
FIGURE 8-11: Data before being turned into a table.
FIGURE 8-12: Data is selected and the confirmation popup is visible.
FIGURE 8-13: The data is now in a table.
FIGURE 8-14: Copilot is ready for data analysis.
FIGURE 8-15: Copilot has calculated the data and displays the answer in the cha...
FIGURE 8-16: Copilot displays the calculated data and waits for a confirmation ...
FIGURE 8-17: The data table now has the new column.
Chapter 9
FIGURE 9-1: Defining central tendencies in a list of grades.
FIGURE 9-2: Deciding what to do with an unusual value.
FIGURE 9-3: Calculating a creative mean.
FIGURE 9-4: Calculating variance from the mean.
FIGURE 9-5: Calculating variance from the mean.
FIGURE 9-6: Calculating the standard deviation.
FIGURE 9-7: Displaying a normal distribution in a graph.
FIGURE 9-8: Normal distributions come in different heights and widths.
FIGURE 9-9: Standardizing a distribution of data.
FIGURE 9-10: Working with skewed data.
FIGURE 9-11: Measuring the kurtosis of two distributions.
FIGURE 9-12: Using COVARIANCE to look for a relationship between two data sets.
FIGURE 9-13: Comparing the results of advertising campaigns.
FIGURE 9-14: Finding out values at quarter percentiles.
FIGURE 9-15: Using PERCENTILE to find high scorers.
FIGURE 9-16: Determining the rank of a value.
FIGURE 9-17: Setting up bins to use with the FREQUENCY function.
FIGURE 9-18: Preparing to type the FREQUENCY function.
FIGURE 9-19: Completing the entry of the FREQUENCY function.
FIGURE 9-20: Preparing to plot the frequency distribution.
FIGURE 9-21: Displaying a frequency distribution as a column chart.
FIGURE 9-22: Finding high and low values.
FIGURE 9-23: Counting with and without criteria.
Chapter 10
FIGURE 10-1: An exponential curve.
FIGURE 10-2: The scatter plot indicates that the
x
and
y
data in this worksheet...
FIGURE 10-3: Creating a scatter chart.
FIGURE 10-4: A data set displayed with its linear regression line.
FIGURE 10-5: Forecasting sales.
FIGURE 10-6: Using the TREND function to calculate predictions for an array.
FIGURE 10-7: Demonstrating use of the GROWTH function to project exponential da...
FIGURE 10-8: Using the NORM.DIST function to calculate probabilities.
FIGURE 10-9: A Poisson distribution with a mean of 20.
FIGURE 10-10: A Poisson distribution with a mean of 4.
FIGURE 10-11: A cumulative Poisson distribution with a mean of 20.
FIGURE 10-12: Using the POISSON.DIST function to calculate a cumulative probabi...
Chapter 11
FIGURE 11-1: Setting how years are interpreted in the Customize Format dialog b...
FIGURE 11-2: Using the Format Cells dialog box to control how dates are display...
FIGURE 11-3: Using the DATE function to assemble a date from separate month, da...
FIGURE 11-4: Splitting apart a date with the DAY, MONTH, and YEAR functions.
FIGURE 11-5: Using the DAY function to analyze customer activity.
FIGURE 11-6: Using the MONTH function to count the number of dates falling in e...
FIGURE 11-7: Converting dates to their serial equivalents with the DATEVALUE fu...
FIGURE 11-8: Using WEEKDAY tells you which day of the week a date falls on.
FIGURE 11-9: Counting workdays with NETWORKDAYS.
Chapter 12
FIGURE 12-1: Using the Format Cells dialog box to specify how time values are d...
FIGURE 12-2: Splitting time with the HOUR, MINUTE, and SECOND functions.
FIGURE 12-3: Using the HOUR function to summarize results.
FIGURE 12-4: Calculating minutes elapsed with the MINUTE function.
Chapter 13
FIGURE 13-1: Keeping an eye on inventory at the guitar shop.
FIGURE 13-2: Looking for hot inventory items.
FIGURE 13-3: Choosing what to see.
FIGURE 13-4: Being selective with summing.
FIGURE 13-5: Being logical about what to choose.
FIGURE 13-6: Using XOR to find where data is not what was expected.
FIGURE 13-7: Changing a reference from relative to absolute.
FIGURE 13-8: Using reference functions to find a value.
FIGURE 13-9: Finding values by using the OFFSET function.
FIGURE 13-10: Using HLOOKUP to locate data in a table.
FIGURE 13-11: Using VLOOKUP to locate data in a table.
FIGURE 13-12: Getting multiple columns of data with XLOOKUP.
FIGURE 13-13: Making a match.
FIGURE 13-14: Using INDEX to extract data from a table.
Chapter 14
FIGURE 14-1: Keeping track of which cell had the latest entry.
FIGURE 14-2: Using CELL and the
format
argument to display a useful message.
FIGURE 14-3: Getting facts about Excel and the computer with the INFO function.
FIGURE 14-4: Using an error to your advantage.
FIGURE 14-5: Calculating how many employees are missing an entry.
FIGURE 14-6: Getting the type of the data.
Chapter 15
FIGURE 15-1: Getting the three left characters from a larger string.
FIGURE 15-2: Using MID to pull characters from any position in a string.
FIGURE 15-3: Using TEXTSPLIT to break apart a string of text.
FIGURE 15-4: Putting strings together with CONCATENATE.
FIGURE 15-5: Putting strings together with TEXTJOIN.
FIGURE 15-6: Using DOLLAR to round numbers and format them as currency.
FIGURE 15-7: Formatting options in the Format Cells dialog box.
FIGURE 15-8: Using TEXT to report in a well-formatted manner.
FIGURE 15-9: Repeating text with the REPT function.
FIGURE 15-10: Using REPLACE to change text.
FIGURE 15-11: Removing spaces with the TRIM function.
FIGURE 15-12: Comparing strings with the EXACT function.
FIGURE 15-13: Splitting names apart.
Chapter 16
FIGURE 16-1: Setting options.
FIGURE 16-2: Viewing formulas the easy way.
FIGURE 16-3: Checking for errors.
FIGURE 16-4: Setting the calculation method.
FIGURE 16-5: Defining a named area.
FIGURE 16-6: Defining a named area the easy way.
FIGURE 16-7: Auditing formulas.
FIGURE 16-8: Applying a format when a condition is met.
FIGURE 16-9: Setting data validation.
FIGURE 16-10: Caught making a bad entry.
FIGURE 16-11: Writing your own function.
FIGURE 16-12: Finding the function in the User Defined category.
FIGURE 16-13: Using the custom Add function.
Chapter 17
FIGURE 17-1: Retrieving data from the internet.
FIGURE 17-2: Determining the correct number.
FIGURE 17-3: Entering a custom fill list.
Guide
Cover
Table of Contents
Title Page
Copyright
Begin Reading
Index
About the Author
Pages
iii
iv
1
2
3
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
36
37
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
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
162
163
164
165
166
167
168
169
170
171
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
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
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
255
256
257
258
259
260
261
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
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
309
310
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
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
353
354
355
356
357
358
359
360
361
362
363
364
365
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
Microsoft® 365 Excel® Formulas & Functions For Dummies®, 7th Edition
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. Microsoft 365 and Excel are trademarks or registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book. Microsoft®365 Excel®Formulas & Functions For Dummies®, 7th Edition, is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation.
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: 2025932955
ISBN 978-1-394-29551-7 (pbk); ISBN 978-1-394-29558-6 (ebk); ISBN 978-1-394-29557-9 (ebk)
Introduction
Excel worksheets are used in many walks of life: business, education, home finances, and even hobbies (such as keeping track of your baseball-card collection). In my house, we use Excel for a lot, from our taxes (boring!) to our ever-growing recipe collection (yummy!). Often, I use Excel in place of a calculator. After all, Excel is like a calculator on steroids!
In the workplace, Excel is one of the most commonly used analysis and reporting tools. Financial statements, sales reports, inventory, project scheduling, customer activity — so much of this stuff is kept in Excel. The program’s capability to manipulate and give feedback about the data makes it attractive. Excel’s flexibility in storing and presenting data is like magic.
About This Book
This book is about the number-crunching side of Excel. Formulas are the keystone to analyzing data — that is, digging out nuggets of important information. What is the average sale? How many times did we do better than average? How many days are left on the project? How much progress have we made? These sorts of things.
Formulas calculate answers, straight and to the point. But that’s not all. Excel has dozens of built-in functions that calculate everything from a simple average to a useful analysis of your investments to complex inferential statistics. But you don’t have to know it all or use it all; just use the parts that are relevant to your work.
This book discusses more than 150 of these functions. But rather than just show their syntax and list them alphabetically, I assemble them by category and provide real-world examples of how to use them alone, and in formulas, along with step-by-step instructions and illustrations of the results.
Within this book, you may note that some web addresses break across two lines of text. If you’re reading this book in print and want to visit one of these web pages, simply key in the web address exactly as it’s noted in the text, pretending as though the line break doesn’t exist. If you’re reading this as an e-book, you’ve got it easy — just click the web address to be taken directly to the web page.
Foolish Assumptions
I assume that you have a PC with Excel loaded. That’s a no-brainer! Nearly all the material is relevant for use with earlier versions of Excel as well. I also assume that you know how to navigate with a keyboard and mouse. Last, I assume that you have used Excel before, even just once. I do discuss basics in Chapter 1 but not all of them. If you really need to start from scratch, I suggest that you read the excellent Excel All-in-One For Dummies, by Paul McFedries and Greg Harvey (Wiley).
Other than that, this book is written for the Microsoft 365 version of Excel, but just between you and me, it works fine with older versions of Excel. There can be a function or two that isn’t in an older version or works slightly differently. But Microsoft has done an excellent job of maintaining compatibility between versions of Excel, so when it comes to formulas and functions, you can be confident that what works in one version works in another.
Icons Used in This Book
This book uses icons in the margins to grab your attention. Here’s a guide to what the icons mean:
The Tip icon highlights information that’ll make your life easier — at least when it comes to Excel.
The Remember icon marks some basic concept that you’ll want to keep tucked away somewhere in your brain.
As it implies, the Warning icon is used for serious stuff. This icon tells you to be careful — usually because you can accidentally erase your data or some such horrible event.
Once in a while, some tidbit is interesting to the tech-head types but not to anyone else. I mark these paragraphs with the Technical Stuff icon. You can read these items or ignore them as you see fit.
Beyond the Book
In addition to the material in the print or e-book you’re reading right now, this product also comes with some access-anywhere goodies on the web. Be sure to check out the free online Cheat Sheet to find the Excel order of operations, Excel cell references worth remembering, common Excel error messages, and more. To get the Cheat Sheet, simply go to www.dummies.com and type Excel Formulas & Functions For Dummies Cheat Sheet in the Search box.
Where to Go from Here
Roll up your sleeves, take a deep breath, and then forget all that preparing-for-a-hard-task stuff. Using Excel is easy. You can hardly make a mistake without Excel’s catching it. If you need to brush up on the basics, go to Chapter 1. That chapter is also the best place to get your first taste of formulas and functions. After that, it’s up to you. The book is organized more by area of focus than anything else. If finance is what you do, go to Part 2. If working with dates is what you do, go to Part 4. Seek, and you will find.
Part 1
Getting Started with Excel Formulas and Functions
IN THIS PART …
Get to know formula and function fundamentals.
Discover the different ways to enter functions.
Understand array-based formulas and functions.
Find out about formula errors and how to fix them.
Chapter 1
Tapping into Formula and Function Fundamentals
IN THIS CHAPTER
Getting the skinny on the Excel basics
Writing formulas
Working with functions in formulas
Excel is to computer programs what a Ferrari is to cars: sleek on the outside and a lot of power under the hood. Excel is also like a truck. It can handle all your data — lots of it. In fact, in Excel, a single worksheet has 17,179,869,184 places to hold data. Yes, that’s what I said — more than 17 billion data placeholders. And that’s on just one worksheet!
The number of available rows and columns may be fewer depending on how much memory your computer has.
Excel is used in all types of businesses. And you know how that’s possible? By being able to store and work with any kind of data. It doesn’t matter whether you’re in finance or sales, whether you run an online e-commerce store or organize wilderness trips, or whether you’re charting party RSVPs or tracking the scores of your favorite sports teams — Excel can handle all of it. Its number-crunching ability is just awesome! And so easy to use!
Just putting a bunch of information on worksheets doesn’t crunch the data or give you sums, results, or analyses. If you just want to store your data somewhere, you can use Excel or get a database program instead. In this book, I show you how to build formulas and how to use the dozens of built-in functions that Excel provides. That’s where the real power of Excel is — making sense of your data.
Don’t fret that this is a challenge and that you may make mistakes. I did when I was ramping up. Besides, Excel is very forgiving. Excel usually tells you when you made a mistake, and sometimes it even helps you correct it. How many programs do that? But first, the basics. This first chapter gives you the springboard you need to use the rest of the book. I wish that books like this were around when I was introduced to computers. I had to stumble through a lot of this.
Working with Excel Fundamentals
Before you can write any formulas or crunch any numbers, you have to know where the data goes and how to find it again. I wouldn’t want your data to get lost! Knowing how worksheets store your data and present it is critical to your analysis efforts.
Understanding workbooks and worksheets
In Excel, a workbook is the same as a file. Excel opens and closes workbooks, just as a word processor program opens and closes documents. When you start up Excel, you are presented with a selection of templates to use, the first one being the standard blank workbook. Also there is a selection of recent files to select from. After you open a new or already created workbook, click the File tab to view basic functions such as opening, saving, printing, and closing your Excel files (not to mention a number of other nifty functions to boot!). Figure 1-1 shows the contents presented on the Info tab.
The default Excel file extension is .xlsx. However, you may see files with the older .xls extension; these older files work fine in the latest version of Excel. You may also see Excel files with the .xslm extension; those are fine to use, too.
Start Excel and double-click the Blank Workbook icon to create a new blank workbook. When you have more than one workbook open, you pick the one you want to work on by clicking it on the Windows Taskbar.
FIGURE 1-1: The Info tab shows details about your Excel file.
A worksheet is where your data actually goes. A workbook contains at least one worksheet. If you didn't have at least one, where would you put the data? Figure 1-2 shows an open workbook that has two sheets, aptly named Sheet1 and Sheet2. To the right of these worksheet tabs is the New Sheet button (looks like a plus sign), used to add worksheets to the workbook.
FIGURE 1-2: Looking at a workbook and worksheets.
At any given moment, one worksheet is always on top. In Figure 1-2, Sheet1 is on top. Another way of saying this is that Sheet1 is the active worksheet. There is always one and only one active worksheet. To make another worksheet active, just click its tab.
Worksheet, spreadsheet, and just plain old sheet are used interchangeably to mean the worksheet.
What’s really cool is that you can change the name of the worksheets. Names like Sheet1 and Sheet2 are just not exciting. How about Baseball Card Collection or Last Year’s Taxes? Well, actually, Last Year’s Taxes isn’t too exciting, either.
The point is, you can give your worksheets meaningful names. You have two ways to do this:
Double-click the worksheet tab and then type a new name.
Right-click the worksheet tab, select Rename from the menu, and then type a new name.
Press Enter to complete the name change.
Figure 1-3 shows one worksheet name already changed and another about to be changed by right-clicking its tab.
FIGURE 1-3: Changing the name of a worksheet.
You can try changing a worksheet name on your own. Do it the easy way:
Double-click a worksheet’s tab.
Type a new name and press Enter.
The name cannot exceed 31 characters.
You can change the color of worksheet tabs. Right-click the tab and select Tab Color from the menu. Color coding tabs provides a great way to organize your work.
To insert a new worksheet into a workbook, click the New Sheet button, which is located after the last worksheet tab. Figure 1-4 shows how. To delete a worksheet, just right-click the worksheet’s tab and select Delete from the menu.
FIGURE 1-4: Inserting a new worksheet.
Don’t delete a worksheet unless you really mean to. You cannot get it back after it is gone. It does not go into the Windows Recycle Bin.
You can insert many new worksheets. The limit as to how many is based on your computer’s memory, but you should have no problem inserting 200 or more. Of course, I hope you have a good reason for having so many, which brings me to the next point.
Worksheets enable you to organize your data. Use them wisely and you will find it easy to manage your data. For example, say that you are the boss (I thought you’d like that!), and over the course of a year you track information about 30 employees. You may have 30 worksheets — one for each employee. Or you may have 12 worksheets — one for each month. Or you may just keep it all on one worksheet. How you use Excel is up to you, but Excel is ready to handle whatever you throw at it.
You can set how many worksheets a new workbook has as the default. To do this, click the File tab, click Options, and then click the General tab. Under the When Creating New Workbooks section, use the Include This Many Sheets spinner control to select a number.
Introducing the Formulas tab
Without further ado, I present the Formulas tab of the Ribbon. The Ribbon sits at the top of Excel. Items on the Ribbon appear as menu headers along the top of the Excel screen, but they actually work more like tabs. Click them and no menus appear. Instead, the Ribbon presents the items that are related to the clicked Ribbon tab.
Figure 1-5 shows the top part of the screen, in which the Ribbon displays the items that appear when you click the Formulas tab. In the figure, the Formulas tab is set to show formula-based methods. At the left end of the tab, functions are categorized. One of the categories is opened to show how you can access a particular function.
FIGURE 1-5: Getting to know the Ribbon.
These groups are along the bottom of the Formulas tab:
Function Library:
This includes the Function Wizard, the AutoSum feature, and the categorized functions.
Defined Names:
These features manage named areas, which are cells or ranges on worksheets to which you assign a meaningful name for easy reference.
Formula Auditing:
These features are for checking and correcting formulas. Also here is the Watch Window, which lets you keep an eye on the values in designated cells, but within one window. In
Figure 1-6
you can see that a few cells have been assigned to the Watch Window. If any values change, you can see this in the Watch Window. Note how the watched cells are on sheets that are not the current active sheet. Neat! By the way, you can move the Watch Window around the screen by clicking the title area of the window and dragging it with the mouse.
FIGURE 1-6: Eyeing the Watch Window.
Calculation:
This is where you manage calculation settings, such as whether calculation is automatic or manual.
Another great feature that goes hand in hand with the Ribbon is the Quick Access Toolbar. (So there is a toolbar after all!) In Figure 1-5, the Quick Access Toolbar sits just above the left side of the Ribbon. On it are icons that perform actions with a single click. The icons are ones you select by using the Quick Access Toolbar tab in the Excel Options dialog box. You can put the toolbar above or below the Ribbon by clicking the Customize Quick Access Toolbar drop-down arrow on the Quick Access Toolbar and choosing an option. In this area, too, are the other options for the Quick Access Toolbar.
Working with rows, columns, cells, ranges, and tables
A worksheet contains cells. Lots of them. Billions of them. This may seem unmanageable but actually it’s pretty straightforward. Figure 1-7 shows a worksheet that contains data. Use this to look at a worksheet’s components. Each cell can contain data or a formula. In Figure 1-7, the cells contain data. Some, or even all, cells can contain formulas, but that’s not the case here.
Columns have letter headers — A, B, C, and so on. You can see these listed horizontally just above the area where the cells are. After you get past the 26th column, a double lettering system is used — AA, AB, and so on. After all the two-letter combinations are used up, a triple-letter scheme is used. Rows are listed vertically down the left side of the screen and use a numbering system.
You find cells at the intersection of rows and columns. Cell A1 is the cell at the intersection of column A and row 1. A1 is the cell’s address. There is always an active cell — that is, a cell in which any entry would go into should you start typing. The active cell has a border around it. Also, the contents of the active cell appear in the Formula Box.
FIGURE 1-7: Looking at what goes into a worksheet.
When I speak of, or reference, a cell, I am referring to its address. The address is the intersection of a column and row. To talk about cell D20 means to talk about the cell that you find at the intersection of column D and row 20.
In Figure 1-7, the active cell is C7. You have a couple of ways to see this. For starters, cell C7 has a border around it. Also notice that the column head C is shaded, as well as row number 7. Just above the column headers are the Name Box and the Formula Box. The Name Box is all the way to the left and shows the active cell’s address of C7. To the right of the Name Box, the Formula Box shows the contents of cell C7.
GETTING TO KNOW THE FORMULA BAR
Taken together, the Formula Box and the Name Box make up the Formula Bar. You use the Formula Bar quite a bit as you work with formulas and functions. The Formula Box is used to enter and edit formulas. The Formula Box is the long entry box that starts in the middle of the bar. When you enter a formula into this box, you can click the little check-mark button to finish the entry. The check-mark button is enabled only when you are entering a formula. Pressing Enter also completes your entry; clicking the X cancels the entry.
An alternative is to enter a formula directly into a cell. The Formula Box displays the formula as it is being entered into the cell. When you want to see just the contents of a cell that has a formula, make that cell active and look at its contents in the Formula Box. Cells that have formulas do not normally display the formula but instead display the result of the formula. When you want to see the actual formula, the Formula Box is the place to do it. The Name Box, on the left side of the Formula Bar, is used to select named areas in the workbook.
If the Formula Bar is not visible, choose File ⇒ Options, and click the Advanced tab. Then, in the Display section in the Excel Options dialog box, select the Show Formula Bar check box to make the Formula Bar visible.
A range is usually a group of adjacent cells, although noncontiguous cells can be included in the same range (but that’s mostly for rocket scientists and those obsessed with treating data like jigsaw puzzle pieces). For your purposes, assume a range is a group of continuous cells. Make a range right now! Here’s how:
Position the mouse pointer over the first cell where you want to define a range.
Press and hold the left mouse button.
Move the pointer to the last cell of your desired area.
Release the mouse button.
Figure 1-8 shows what happened when I did this. I selected a range of cells. The address of this range is A3:D21.
A range address looks like two cell addresses put together, with a colon (:) in the middle. And that’s what it is! A range address starts with the address of the cell in the upper left of the range, then has a colon, and ends with the address of the cell in the lower right.
One more detail about ranges: You can give them a name. This is a great feature because you can think about a range in terms of what it is used for, instead of what its address is. Also, if I did not take the extra step to assign a name, the range would be gone as soon as I clicked anywhere on the worksheet. When a range is given a name, you can repeatedly use the range by using its name.
Say you have a list of clients on a worksheet. What’s easier — thinking of exactly which cells are occupied, or thinking that there is your list of clients?
FIGURE 1-8: Selecting a range of cells.
Throughout this book, I use areas made of cell addresses and ranges that have been given names. It’s time to get your feet wet creating a named area. Here’s what you do:
Position the mouse pointer over a cell, click and hold the left mouse button, and drag the pointer around.
Release the mouse button when you’re done.
You’ve selected an area of the worksheet.
Click Define Name in the Defined Names group on the Formulas tab.
The New Name dialog box appears. Figure 1-9 shows you how it looks so far.
Name the area or keep the suggested name. You can change the suggested name as well.
Excel guesses that you want to name the area with the value it finds in the top cell of the range. That may or may not be what you want. Change the name if you need to. In Figure 1-9, I changed the name to Clients.
An alternative method of naming an area is to select it, type the name in the Name Box (left of the Formula Bar), and press Enter.
Click OK.
FIGURE 1-9: Adding a name to the workbook.
That’s it. Hey, you’re already on your way to being an Excel pro! Now that you have a named area, you can easily select your data at any time. Just go to the Name Box and select it from the list. Figure 1-10 shows how to select the Clients area.
FIGURE 1-10: Using the Name Box to find the named area.
Tables work in much the same manner as named areas. Tables have a few features that are unavailable to simple named areas. With tables you can indicate that the top row contains header labels. Further, tables default to have filtering ability. Figure 1-11 shows a table on a worksheet, with headings and filtering ability.
With filtering, you can limit which rows show, based on which values you select to display.
FIGURE 1-11: Trying a table.
The Tables group in the Insert tab contains the Table button for inserting a table.
Formatting your data
Of course, you want to make your data look all spiffy and shiny. Bosses like that. Is the number 98.6 someone’s temperature? Is it a score on a test? Is it 98 dollars and 60 cents? Is it a percentage? Any of these formats is correct:
98.6
$98.60
98.6%
Excel lets you format your data in just the way you need. Formatting options are on the Home tab of the Ribbon, in the Number group.
Figure 1-12 shows how formatting helps in the readability and understanding of a worksheet. Cell B1 has a monetary amount and is formatted with the Accounting style. Cell B2 is formatted as a percentage. The actual value in cell B2 is .05. Cell B7 is formatted as currency. The currency format displays a negative value in parentheses. This is just one of the formatting options for currency. Chapter 5 explains further about formatting currency data.
FIGURE 1-12: Formatting data.
Besides selecting formatting on the Home tab of the Ribbon, you can use the Format Cells dialog box. This is the place to go for all your formatting needs beyond what’s available on the Home tab of the Ribbon. You can even create custom formats. You can display the Format Cells dialog box two ways:
On the Home tab, click the drop-down menu in the Number group and then click More Number Formats.
Right-click any cell and select Format Cells from the pop-up menu.
Figure 1-13 shows the Format Cells dialog box. I discuss this dialog box and formatting more extensively in Chapter 5.
FIGURE 1-13: Using the Format Cells dialog box for advanced formatting options.
Getting help
Excel is complex; you can’t deny that. And lucky for all of us, help is just a key press away. Yes, literally one key press — just press the F1 key. Try it now.
This starts the Help system. From there you can search on a keyword or browse through the Help table of contents. Later on, when you are working with Excel functions, you can get help on specific functions directly by clicking the Help on This Function link in the Insert Function dialog box. Chapter 2 covers the Insert Function dialog box in detail.
Gaining the Upper Hand on Formulas
Okay, time to get to the nitty-gritty of what Excel is all about. Sure, you can just enter data and leave it as is, and even generate some pretty charts from it. But getting answers from your data, or creating a summary of your data, or applying what-if tests — all of this takes formulas.
To be specific, a formula in Excel calculates something or returns some result based on data in one or more worksheets. These worksheets can be in more than one workbook. A formula is placed in a cell and must start with an equal sign () to tell Excel that it is a formula and not data. Sounds simple, and it is.
All formulas start with an equal () sign.
Look at some very basic formulas. Table 1-1 shows a few formulas and tells you what they do.
TABLE 1-1 Basic Formulas
Formula
What It Does
=2+2
Returns the number 4.
=D5
The cell that contains this formula displays the value that is in cell D5. If you try to enter this formula into cell D5 itself, you create a circular reference. That is a no-no. See Chapter 4.
=SUM(A2:A5)
Returns the sum of the values in cells A2, A3, A4, and A5.This formula uses the SUM function to sum up all the values in the range.
I use the word return to refer to the result of the formula or function calculation. So, saying “The formula returns a 7” is the same as saying “The formula calculated the answer to be 7.”
Entering your first formula
Ready to enter your first formula? Make sure Excel is running and a worksheet is in front of you, and then follow these steps:
Click an empty cell.
Type
=10+10
.
Press Enter.
That was easy, wasn't it? You should see the result of the formula — the number 20.
Try another. This time you create a formula that adds the value of two cells:
Click any cell.
Type any number.
Click another cell.
Type another number.
Click a third cell.
This cell will contain the formula.
Type
.
Click the first cell.
This is an important point in the creation of the formula. The formula is being written by both your keyboard entry and your clicks of the mouse. The formula should look about half complete, with an equal sign immediately followed by the address of the cell you just clicked. Figure 1-14 shows what this looks like. In the example, the value 15 has been entered into cell B3 and the value 35 into cell B6. The formula was started in cell E3. Cell E3 so far has =B3 in it.
FIGURE 1-14: Entering a formula that references cells.
Type
+
.
Click the cell that has the second entered value.
In this example, this is cell B6. The formula in cell E3 now looks like this: =B3+B6. You can see this in Figure 1-15.
FIGURE 1-15: Completing the formula.
Press Enter.
This ends the entry of the function. All done! Congratulations!
Figure 1-16 shows how the example ended up. Cell E3 displays the result of the calculation. Also notice that the Formula Bar displays the content of cell E3, which really is the formula.
FIGURE 1-16: A finished formula.
Understanding references
References abound in Excel formulas. You can reference cells. You can reference ranges. You can reference cells and ranges on other worksheets. You can reference cells and ranges in other workbooks. Formulas and functions are at their most useful when you're using references, so you need to understand them.
And if that isn’t enough to stir the pot, you can use three types of cell references: relative, absolute, and mixed. Okay, one step at a time here. Try a formula that uses a range.
Formulas that use ranges often have a function in the formula, so use the SUM function here:
Enter some numbers in many cells going down one column.
Click another cell where you want the result to appear.
Type =SUM(.
This starts the function.
Click the first cell that has an entered value, hold the left mouse button down, and drag the mouse pointer over all the cells that have values.
Release the mouse button.
The range address appears where the formula and function are being entered.
Type
)
.
Press Enter.
Give yourself a pat on the back!
Wherever you drag the mouse to enter the range address into a function, you can also just type the address of the range, if you know what it is.
Excel is dynamic when it comes to cell addresses. If you have a cell with a formula that references a different cell’s address, and you copy the formula from the first cell to another cell, the address of the reference inside the formula changes. Excel updates the reference inside the formula to match the number of rows and/or columns that separate the original cell (where the formula is being copied from) from the new cell (where the formula is being copied to). This may be confusing, so try an example so that you can see this for yourself:
In cell B2, type
100
.
In cell C2, type
=B2*2
.
Press Enter.
Cell C2 now returns the value 200.
If C2 is not the active cell, click it once so it becomes the active cell.
Press Ctrl+C, or click the Copy button in the Clipboard group on the Home tab of the Ribbon.
Click cell C3.
Press Ctrl+V, or click the Paste button in the Clipboard group on the Home tab of the Ribbon.
If you see a strange moving line around cell C2, press the Esc key.
Cell C3 should be the active cell, but if it is not, just click it once. Look at the Formula Bar. The contents of cell C3 are =B3*2, and not the =B2*2 that you copied.
Did you see a moving line around a cell? That line's called a marquee. It’s a reminder that you are in the middle of a cut or copy operation, and the marquee goes around the cut or copied data.
