Excel 2016 Formulas and Functions

Course Details

• What You Will Learn
• Gain what-if capability with the IF, AND, OR, and CHOOSE functions
• Cross-reference data with the table-lookup functions VLOOKUP, INDEX, and MATCH
• Use frequently-overlooked Text functions to clean and manipulate data
• Discover creative and powerful array formulas and functions
• Summarize data with the COUNTIF, SUMIF family of functions
• Instantly display all formulas in a worksheet
• Highlight all formula cells in a worksheet; highlight all numerical value cells
• Use entire row and column references in formulas
• Debug formulas quickly and effectively with a keystroke shortcut
• Use AutoSum (and its extended capabilities) more efficiently
• 1. Getting the Most Out of Ranges
• Advanced Range-Selection Techniques
• Data Entry in a Range
• Filling a Range
• Creating a Series
• Advanced Range Copying and Pasting
• Clearing a Range
• Applying Conditional Formatting to a Range
• 2. Using Range Names
• Defining a Range Name
• Working with Range Names
• 3. Building Basic Formulas
• Understanding Formula Basics
• Understanding Operator Precedence
• Controlling Worksheet Calculation
• Copying and Moving Formulas
• Displaying Worksheet Formulas
• Converting a Formula to a Value
• Working with Range Names in Formulas
• Working with Links in Formulas
• Formatting Numbers, Dates, and Times
• 4. Creating Advanced Formulas
• Working with Arrays
• Using Intergration and Circular References
• Consolidating Multi-sheet Data
• Applying Data-Validation Rules to Cells
• Using Dialog Box Controls on a Worksheet
• 5. Troubleshooting Formulas
• Understanding Excel's Error Values
• Fixing Other Formula Errors
• Handling Formula Errors
• Using the Formula Error Checker
• Auditing a Worksheet
• 6. Understanding Functions
• About Excel's Functions
• The Structure of a Function
• Typing a Function into a Formula
• Using the Insert Function Feature
• Loading the Analysis ToolPak
• 7. Working with Text Functions
• Excel's Text Functions
• Working with Characters and Codes
• Converting Text
• Formatting Text
• Manipulating Text
• Searching for Substrings
• Substituting One Substring for Another
• 8. Working with Logical and Information Functions
• Adding Intelligence with Logical Functions
• Getting Data with Information Functions
• 9. Working with Lookup Functions
• Excel's Lookup Functions
• Understanding Lookup Tables
• The Choose Function
• Looking Up Values in Tables
• 10 Working with Date and Time Functions
• How Excel Deals with Dates and Times
• Using Excel's Date Functions
• Using Excel's Time Functions
• 11. Working with Math Functions
• Excel's Math and Trig Functions
• Understanding Excel's Rounding Functions
• Summing Values
• Generating Random Numbers
• 12. Working with Statistical Functions
• Excel's Statistical Functions
• Understanding Descriptive Statistics
• Counting Items with the Count Function
• Calculating Averages
• Calculating Extreme Values
• Calculating Measures of Variation
• Working with Frequency Distributions
• Using the Analysis ToolPak Statistical Tools
• 13. Analyzing Data with Tables
• Planning an Excel Table
• Converting a Range to a Table
• Basic Table Operations
• Sorting a Table
• Filtering Table Data
• Referencing Tables in Formulas
• Excel's Table Functions
• 14. Analyzing Data with Pivot Tables
• What Are PivotTables?
• Building PivotTables
• Working with PivotTable Subtotals
• Changing the Data Field Summary Calculation
• Creating Custom PivotTable Calculations
• Using PivotTable Results in a Worksheet Formula
• 15. Using Excel's Business Modeling Tools
• Using What-If Analysis
• Working with Goal Seek
• Working with Scenarios
• 16. Using Regression to Track Trends and Make Forecasts
• Choosing a Regression Method
• Using Simple Regression on Linear Data
• Using Simple Regression on Nonlinear Data
• Using Multiple Regression Analysis
• 17. Solving Complex Problems with Solver
• Some Background on Solver
• Loading Solver
• Using Solver
• Adding Constraints
• Saving a Solution as a Scenario
• Setting Other Solver Options
• Making Sense of Solver's Message
• Displaying Solver's Reports
• 18. Building Loan Formulas
• Understanding the Time Value of Money
• Calculating a Loan Payment
• Building a Loan Amortization Schedule
• Calculating the Term of a Loan
• Calculating the Interest Rate Required for a Loan
• Calculating How Much You Can Burrow
• 19. Building Investment Formulas
• Working with Interest Rates
• Calculating the Future Value
• Working Toward an Investment Goal
• 20. Building Discount Formulas
• Calculating the Present Value
• Discounting Cash Flows
• Calculating the Payback Period
• Calculating the Internal Rate of Return