Excel 2016 Formulas and Functions (includes Content Update Program)

Paul McFedries  
QUE Publishing
Total pages
October 2015
Related Titles

Product detail

Product Price CHF Available  
Excel 2016 Formulas and Functions (includes Content Update Program)
44.50 approx. 7-9 days


This book provides coverage of specific features and functions related to their particular productivity use. This series is for students who need to go further with the software to specifically solve complex applications and uses. This book not only takes them through Excel's intermediate and advanced options, but also tells them why these options are useful and shows them how to use them in everyday situations and real-world models with no-nonsense, step-by-step tutorials and lots of practical, useful examples.


Students will learn to:

  • Quickly create powerful spreadsheets with FlashFill
  • 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

New to this Edition

This edition covers the new and changed formula-related features found in Excel 2016. This edition also covers the new and changed functions that come with Excel 2016.   

Table of Contents

Introduction    1

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

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

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

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


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.