## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

## Book Description

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

2. Title Page
4. Contents at a Glance
5. Contents
6. About the Author
7. Dedication
8. Acknowledgments
9. We Want to Hear from You!
11. Introduction
12. Part I: Mastering Excel Ranges and Formulas
1. 1. Getting the Most Out of Ranges
1. Advanced Range-Selection Techniques
2. Data Entry in a Range
3. Filling a Range
4. Creating a Series
5. Advanced Range Copying and Pasting
6. Clearing a Range
7. Applying Conditional Formatting to a Range
8. From Here
2. 2. Using Range Names
1. Defining a Range Name
2. Working with Range Names
3. From Here
3. 3. Building Basic Formulas
1. Understanding Formula Basics
2. Understanding Operator Precedence
3. Controlling Worksheet Calculation
4. Copying and Moving Formulas
5. Displaying Worksheet Formulas
6. Converting a Formula to a Value
7. Working with Range Names in Formulas
8. Working with Links in Formulas
9. Formatting Numbers, Dates, and Times
10. From Here
4. 4. Creating Advanced Formulas
1. Working with Arrays
2. Using Iteration and Circular References
3. Consolidating Multisheet Data
4. Applying Data-Validation Rules to Cells
5. Using Dialog Box Controls on a Worksheet
6. From Here
5. 5. Troubleshooting Formulas
1. Understanding Excel’s Error Values
2. Fixing Other Formula Errors
3. Handling Formula Errors with IFERROR()
4. Using the Formula Error Checker
5. Auditing a Worksheet
6. From Here
13. Part II: Harnessing the Power of Functions
1. 6. Understanding Functions
2. 7. Working with Text Functions
1. Excel’s Text Functions
2. Working with Characters and Codes
3. Converting Text
4. Formatting Text
5. Manipulating Text
6. Searching for Substrings
7. Substituting One Substring for Another
8. From Here
3. 8. Working with Logical and Information Functions
1. Adding Intelligence with Logical Functions
2. Getting Data with Information Functions
3. From Here
4. 9. Working with Lookup Functions
1. Excel’s Lookup Functions
2. Understanding Lookup Tables
3. The CHOOSE() Function
4. Looking Up Values in Tables
5. From Here
5. 10. Working with Date and Time Functions
1. How Excel Deals with Dates and Times
2. Using Excel’s Date Functions
3. Using Excel’s Time Functions
4. From Here
6. 11. Working with Math Functions
1. Excel’s Math and Trig Functions
2. Understanding Excel’s Rounding Functions
3. Summing Values
4. The MOD() Function
5. Generating Random Numbers
6. From Here
7. 12. Working with Statistical Functions
1. Excel’s Statistical Functions
2. Understanding Descriptive Statistics
3. Counting Items with the COUNT() Function
4. Calculating Averages
5. Calculating Extreme Values
6. Calculating Measures of Variation
7. Working with Frequency Distributions
8. Using the Analysis ToolPak Statistical Tools
9. From Here
14. Part III: Building Business Models
1. 13. Analyzing Data with Tables
1. Planning an Excel Table
2. Converting a Range to a Table
3. Basic Table Operations
4. Sorting a Table
5. Filtering Table Data
6. Referencing Tables in Formulas
7. Excel’s Table Functions
8. From Here
2. 14. Analyzing Data with PivotTables
1. What Are PivotTables?
2. Building PivotTables
3. Working with PivotTable Subtotals
4. Changing the Data Field Summary Calculation
5. Creating Custom PivotTable Calculations
6. Using PivotTable Results in a Worksheet Formula
7. From Here
3. 15. Using Excel’s Business Modeling Tools
1. Using What-If Analysis
2. Working with Goal Seek
3. Working with Scenarios
4. From Here
4. 16. Using Regression to Track Trends and Make Forecasts
1. Choosing a Regression Method
2. Using Simple Regression on Linear Data
3. Using Simple Regression on Nonlinear Data
4. Using Multiple Regression Analysis
5. From Here
5. 17. Solving Complex Problems with Solver
1. Some Background on Solver
3. Using Solver
5. Saving a Solution as a Scenario
6. Setting Other Solver Options
7. Making Sense of Solver’s Messages
8. Displaying Solver’s Reports
9. From Here
15. Part IV: Building Financial Formulas
1. 18. Building Loan Formulas
1. Understanding the Time Value of Money
2. Calculating a Loan Payment
3. Building a Loan Amortization Schedule
4. Calculating the Term of a Loan
5. Calculating the Interest Rate Required for a Loan
6. Calculating How Much You Can Borrow
7. From Here
2. 19. Building Investment Formulas
1. Working with Interest Rates
2. Calculating the Future Value
3. Working Toward an Investment Goal
4. From Here
3. 20. Building Discount Formulas
1. Calculating the Present Value
2. Discounting Cash Flows
3. Calculating the Payback Period
4. Calculating the Internal Rate of Return
5. From Here
16. Index
17. Code Snippets