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

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