Microsoft Excel 2016 Formulas and Functions / Edition 1

by Paul McFedries

Overview

Book + Content Update Program

Master core Excel 2016 tools for building powerful, reliable spreadsheets with Excel 2016 Formulas and Functions. Excel expert Paul McFedries shows how to use Excel 2016’s core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from AutoFill to Excel’s newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.

Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more.

• Quickly create powerful spreadsheets with range names and array formulas
• Use conditional formatting to instantly reveal anomalies, problems, or opportunities
• Analyze your data with standard tables and PivotTables
• Use complex criteria to filter data in lists
• Understand correlations between data
• Perform sophisticated what-if analyses
• Use regression to track trends and make forecasts
• Build loan, investment, and discount formulas
• Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets

In addition, this book is part of Que’s exciting Content Update Program. As Microsoft updates features of Excel 2016, sections of this book will be updated or new sections will be added to match the updates to the software. The updates will be delivered to you via a FREE Web Edition of this book, which can be accessed with any Internet connection. To learn more, visit www.quepublishing.com/CUP.

About MrExcel Library:
Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will
• Dramatically increase your productivity–saving you 50 hours a year or more
• Present proven, creative strategies for solving real-world problems
• Show you how to get great results, no matter how much data you have
• Help you avoid critical mistakes that even experienced users make

Product Details

ISBN-13:	9780789755643
Publisher:	Pearson Education
Publication date:	10/01/2015
Series:	MrExcel Library Series
Pages:	528
Sales rank:	683,250
Product dimensions:	7.00(w) x 9.10(h) x 1.20(d)

About the Author

Paul McFedries is an Excel expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 85 books to his credit, which combined have sold more than 4 million copies worldwide. His titles include the Que Publishing books My Office 2016, Windows 10 In Depth (with coauthor Brian Knittel), and PCs for Grownups, as well as the Sams Publishing book Windows 7 Unleashed. Paul is also the proprietor of Word Spy (www.wordspy.com), a website devoted to lexpionage, the sleuthing of new words and phrases that have entered the English language. Please drop by Paul’s personal website at www.mcfedries.com or follow Paul on Twitter, at twitter.com/wordspy.

Table of Contents

Introduction 1

PART I : MASTERING EXCEL RANGES AND FORMULAS
Chapter 1 Getting the Most Out of Ranges 5
Advanced Range-Selection Techniques 5
Mouse Range-Selection Tricks 6
Keyboard Range-Selection Tricks 7
Working with 3D Ranges 7
Selecting a Range Using Go To 8
Using the Go To Special Dialog Box 9
Data Entry in a Range 14
Filling a Range 14
Using the Fill Handle 15
Flash-Filling a Range 18
Creating a Series 20
Advanced Range Copying and Pasting 21
Pasting Selected Cell Attributes 22
Combining Two Ranges Arithmetically 23
Transposing Rows and Columns 24
Clearing a Range 25
Applying Conditional Formatting to a Range 25
Creating Highlight Cells Rules 26
Creating Top/Bottom Rules 27
Adding Data Bars 29
Adding Color Scales 32
Adding Icon Sets 33
Chapter 2 Using Range Names 37
Defining a Range Name 38
Working with the Name Box 39
Using the New Name Dialog Box 40
Changing the Scope to Define Sheet-Level Names 41
Using Worksheet Text to Define Names 41
Naming Constants 44
Working with Range Names 45
Referring to a Range Name 45
Working with AutoComplete for Range Names 47
Navigating Using Range Names 47
Pasting a List of Range Names in a Worksheet 48
Displaying the Name Manager 48
Filtering Names 48
Editing a Range Name’s Coordinates 49
Adjusting Range Name Coordinates Automatically 49
Changing a Range Name 51
Deleting a Range Name 51
Using Names with the Intersection Operator 51
Chapter 3 Building Basic Formulas 53
Understanding Formula Basics 53
Formula Limits in Excel 2016 54
Entering and Editing Formulas 54
Using Arithmetic Formulas 55
Using Comparison Formulas 56
Using Text Formulas 57
Using Reference Formulas 57
Understanding Operator Precedence 57
The Order of Precedence 58
Controlling the Order of Precedence 58
Controlling Worksheet Calculation 60
Copying and Moving Formulas 62
Understanding Relative Reference Format 62
Understanding Absolute Reference Format 64
Copying a Formula Without Adjusting Relative References 65
Displaying Worksheet Formulas 65
Displaying All Worksheet Formulas 65
Displaying a Cell’s Formula by Using FORMULATEXT() 65
Converting a Formula to a Value 66
Working with Range Names in Formulas 67
Pasting a Name into a Formula 67
Applying Names to Formulas 68
Naming Formulas 70
Working with Links in Formulas 71
Understanding External References 72
Updating Links 73
Changing the Link Source 73
Formatting Numbers, Dates, and Times 74
Numeric Display Formats 74
Date and Time Display Formats 83
Deleting Custom Formats 85
Chapter 4 Creating Advanced Formulas 87
Working with Arrays 87
Using Array Formulas 88
Using Array Constants 91
Functions That Use or Return Arrays 91
Using Iteration and Circular References 93
Consolidating Multisheet Data 95
Consolidating by Position 95
Consolidating by Category 98
Applying Data-Validation Rules to Cells 100
Using Dialog Box Controls on a Worksheet 103
Displaying the Developer Tab 103
Using the Form Controls 103
Adding a Control to a Worksheet 104
Linking a Control to a Cell Value 104
Understanding the Worksheet Controls 105
Chapter 5 Troubleshooting Formulas 111
Understanding Excel’s Error Values 112
#DIV/0! 112
#N/A 113
#NAME? 113
Case Study: Avoiding #NAME? Errors When Deleting Range Names 114
#NULL! 115
#NUM! 115
#REF! 115
#VALUE! 115
Fixing Other Formula Errors 116
Missing or Mismatched Parentheses 116
Erroneous Formula Results 117
Fixing Circular References 118
Handling Formula Errors with IFERROR() 118
Using the Formula Error Checker 119
Choosing an Error Action 120
Setting Error Checker Options 121
Auditing a Worksheet 123
Understanding Auditing 124
Tracing Cell Precedents 125
Tracing Cell Dependents 125
Tracing Cell Errors 125
Removing Tracer Arrows 125
Evaluating Formulas 126
Watching Cell Values 126

PART II : HARNESSING THE POWER OF FUNCTIONS
Chapter 6 Understanding Functions 129
About Excel’s Functions 130
The Structure of a Function 130
Typing a Function into a Formula 132
Using the Insert Function Feature 134
Loading the Analysis ToolPak 136
Chapter 7 Working with Text Functions 139
Excel’s Text Functions 139
Working with Characters and Codes 141
The CHAR() Function 141
The CODE() Function 144
Converting Text 144
The LOWER() Function 145
The UPPER() Function 145
The PROPER() Function 145
The NUMBERVALUE() Function 145
Formatting Text 146
The DOLLAR() Function 146
The FIXED() Function 147
The TEXT() Function 147
Displaying When a Workbook Was Last Updated 148
Manipulating Text 149
Removing Unwanted Characters from a String 149
The REPT() Function: Repeating a Character or String 150
Extracting a Substring 152
Converting Text to Sentence Case 153
A Date-Conversion Formula 154
Case Study: Generating Account Numbers, Part I 154
Searching for Substring 155
The FIND() and SEARCH() Functions 155
Extracting a First Name or Last Name 156
Extracting First Name, Last Name, and Middle Initial 157
Determining the Column Letter 157
Substituting One Substring for Another 158
The REPLACE() Function 159
The SUBSTITUTE() Function 159
Removing a Character from a String 160
Removing Two Different Characters from a String 160
Case Study: Generating Account Numbers, Part II 161
Removing Line Feeds 161
Chapter 8 Working with Logical and Information Functions 163
Adding Intelligence with Logical Functions 163
Using the IF() Function 164
Performing Multiple Logical Tests 167
Combining Logical Functions with Arrays 173
Case Study: Building an Accounts Receivable Aging Worksheet 178
Getting Data with Information Functions 181
The CELL() Function 182
The ERROR.TYPE() Function 184
The INFO() Function 186
The SHEET() and SHEETS() Functions 186
The IS Functions 187
Chapter 9 Working with Lookup Functions 191
Excel’s Lookup Functions 191
Understanding Lookup Tables 192
The CHOOSE() Function 193
Determining the Name of the Day of the Week 194
Determining the Month of the Fiscal Year 194
Calculating Weighted Questionnaire Results 195
Integrating CHOOSE() and Worksheet Option Buttons 195
Looking Up Values in Tables 196
The VLOOKUP() Function 197
The HLOOKUP() Function 197
Returning a Customer Discount Rate with a Range Lookup 198
Returning a Tax Rate with a Range Lookup 199
Finding Exact Matches 200
Advanced Lookup Operations 201
Chapter 10 Working with Date and Time Functions 207
How Excel Deals with Dates and Times 207
Entering Dates and Times 208
Excel and Two-Digit Years 209
Using Excel’s Date Functions 210
Returning a Date 212
Returning Parts of a Date 213
Calculating the Difference Between Two Dates 223
Using Excel’s Time Functions 227
Returning a Time 228
Returning Parts of a Time 229
Calculating the Difference Between Two Times 231
Case Study: Building an Employee Time Sheet 231
Chapter 11 Working with Math Functions 237
Excel’s Math and Trig Functions 237
Understanding Excel’s Rounding Functions 241
The ROUND() Function 241
The MROUND() Function 242
The ROUNDDOWN() and ROUNDUP() Functions 242
The CEILING.MATH() and FLOOR.MATH() Functions 243
The EVEN() and ODD() Functions 245
The INT() and TRUNC() Functions 245
Case Study: Rounding Billable 247
Summing Values 247
The SUM() Function 247
Calculating Cumulative Totals 248
Summing Only the Positive or Negative Values in a Range 249
The MOD() Function 249
A Better Formula for Time Differences 250
Summing Every nth Row 250
Determining Whether a Year Is a Leap Year 251
Creating Ledger Shading 251
Generating Random Numbers 253
The RAND() Function 253
The RANDBETWEEN() Function 255
Chapter 12 Working with Statistical Functions 257
Excel’s Statistical Functions 257
Understanding Descriptive Statistics 260
Counting Items with the COUNT() Function 261
Calculating Averages 262
The AVERAGE() Function 262
The MEDIAN() Function 262
The MODE() Function 263
Calculating the Weighted Mean 263
Calculating Extreme Values 264
The MAX() and MIN() Functions 264
The LARGE() and SMALL() Functions 266
Performing Calculations on the Top k Values 266
Performing Calculations on the Bottom k Values 267
Calculating Measures of Variation 267
Calculating the Range 268
Calculating the Variance 268
Calculating the Standard Deviation 269
Working with Frequency Distributions 270
The FREQUENCY() Function 271
Understanding the Normal Distribution and the NORMDIST() Function 272
The Shape of the Curve I: The SKEW() Function 274
The Shape of the Curve II: The KURT() Function 275
Using the Analysis ToolPak Statistical Tools 276
Using the Descriptive Statistics Tool 279
Determining the Correlation Between Data 280
Working with Histograms 283
Using the Random Number Generation Tool 285
Working with Rank and Percentile 288

PART III : BUILDING BUSINESS MODELS
Chapter 13 Analyzing Data with Tables 291
Planning an Excel Table 291
Converting a Range to a Table 292
Basic Table Operations 294
Sorting a Table 295
Performing a More Complex Sort 296
Sorting a Table in Natural Order 297
Sorting on Part of a Field 298
Sorting Without Articles 299
Filtering Table Data 300
Using Filter Lists to Filter a Table 300
Using Complex Criteria to Filter a Table 304
Entering Computed Criteria 307
Copying Filtered Data to a Different Range 308
Referencing Tables in Formulas 309
Using Table Specifiers 309
Entering Table Formulas 311
Excel’s Table Functions 313
About Table Functions 313
Table Functions That Don’t Require a Criteria Range 313
Table Functions That Accept Multiple Criteria 317
Table Functions That Require a Criteria Range 319
Case Study: Applying Statistical Table Functions to a Defects Database 322
Chapter 14 Analyzing Data with PivotTables 325
What Are PivotTables? 325
How PivotTables Work 326
Some PivotTable Terms 328
Building PivotTables 329
Building a PivotTable from a Table or Range 329
Building a PivotTable from an External Database 332
Working with and Customizing a PivotTable 333
Working with PivotTable Subtotals 333
Hiding PivotTable Grand Totals 334
Hiding PivotTable Subtotals 334
Customizing the Subtotal Calculation 334
Changing the Data Field Summary Calculation 335
Using a Difference Summary Calculation 336
Using a Percentage Summary Calculation 337
Using a Running Total Summary Calculation 340
Using an Index Summary Calculation 341
Creating Custom PivotTable Calculations 342
Creating a Calculated Field 344
Creating a Calculated Item 346
Using PivotTable Results in a Worksheet Formula 347
Chapter 15 Using Excel’s Business Modeling Tools 349
Using What-If Analysis 349
Setting Up a One-Input Data Table 350
Adding More Formulas to the Input Table 352
Setting Up a Two-Input Data Table 353
Editing a Data Table 355
Working with Goal Seek 355
How Does Goal Seek Work? 355
Running Goal Seek 356
Optimizing Product Margin 358
A Note About Goal Seek’s Approximations 358
Performing a Break-Even Analysis 360
Solving Algebraic Equations 360
Working with Scenarios 362
Understanding Scenarios 362
Setting Up Your Worksheet for Scenarios 363
Adding a Scenario 364
Displaying a Scenario 365
Editing a Scenario 366
Merging Scenarios 367
Generating a Summary Report 367
Deleting a Scenario 369
Chapter 16 Using Regression to Track Trends and Make Forecasts 371
Choosing a Regression Method 372
Using Simple Regression on Linear Data 372
Analyzing Trends Using Best-Fit Lines 373
Making Forecasts 380
Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model 386
Using Simple Regression on Nonlinear Data 393
Working with an Exponential Trend 394
Working with a Logarithmic Trend 399
Working with a Power Trend 401
Using Polynomial Regression Analysis 403
Using Multiple Regression Analysis 407
Chapter 17 Solving Complex Problems with Solver 411
Some Background on Solver 411
The Advantages of Solver 412
When Do You Use Solver? 412
Loading Solver 413
Using Solver 413
Adding Constraints 416
Saving a Solution as a Scenario 418
Setting Other Solver Options 418
Selecting the Method Solver Uses 419
Controlling How Solver Works 419
Working with Solver Models 422
Making Sense of Solver’s Messages 424
Case Study: Solving the Transportation Problem 425
Displaying Solver’s Reports 427
The Answer Report 427
The Sensitivity Report 429
The Limits Report 430

PART IV : BUILDING FINANCIAL FORMULAS
Chapter 18 Building Loan Formulas 433
Understanding the Time Value of Money 433
Calculating a Loan Payment 435
Loan Payment Analysis 435
Working with a Balloon Loan 436
Calculating Interest Costs, Part 1 436
Calculating the Principal and Interest 437
Calculating Interest Costs, Part 2 438
Calculating Cumulative Principal and Interest 439
Building a Loan Amortization Schedule 440
Building a Fixed-Rate Amortization Schedule 440
Building a Dynamic Amortization Schedule 441
Calculating the Term of a Loan 443
Calculating the Interest Rate Required for a Loan 445
Calculating How Much You Can Borrow 446
Case Study: Working with Mortgages 447
Chapter 19 Building Investment Formulas 453
Working with Interest Rates 453
Understanding Compound Interest 454
Nominal Versus Effective Interest 454
Converting Between the Nominal Rate and the Effective Rate 455
Calculating the Future Value 456
The Future Value of a Lump Sum 456
The Future Value of a Series of Deposits 457
The Future Value of a Lump Sum Plus Deposits 458
Working Toward an Investment Goal 458
Calculating the Required Interest Rate 458
Calculating the Required Number of Periods 459
Calculating the Required Regular Deposit 460
Calculating the Required Initial Deposit 461
Calculating the Future Value with Varying Interest Rates 461
Case Study: Building an Investment Schedule 462
Chapter 20 Building Discount Formulas 467
Calculating the Present Value 468
Taking Inflation into Account 468
Calculating Present Value Using PV() 469
Income Investing Versus Purchasing a Rental Property 470
Buying Versus Leasing 471
Discounting Cash Flows 472
Calculating the Net Present Value 473
Calculating Net Present Value Using NPV() 474
Net Present Value with Varying Cash Flows 475
Net Present Value with Nonperiodic Cash Flows 476
Calculating the Payback Period 477
Simple Undiscounted Payback Period 477
Exact Undiscounted Payback Point 478
Discounted Payback Period 479
Calculating the Internal Rate of Return 479
Using the IRR() Function 480
Calculating the Internal Rate of Return for Nonperiodic Cash Flows 480
Calculating Multiple Internal Rates of Return 481
Case Study: Publishing a Book 482
Index 497