Excel 2016 Formulas and Functions
9:00am - 4:30pm
This class is designed for students who use Excel regularly and want to explore formulas and functions.
AUDIENCE AND PREREQUISITES
How You Will Benefit
After completing this course, you will understand and be able to apply Excel formulas and functions to solve a variety of data analysis problems.
Before coming to the class, attendees should have an understanding of Excel fundamentals including formatting, navigation, and basic formulas.
*Course cost listed does not include the cost of courseware. Please contact us at firstname.lastname@example.org or 207-775-0244 for additional pricing information, or if you have any questions.
Course is subject to minimum enrollment.
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 efficiently1. 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 Range2. Using Range Names Defining a Range Name Working with Range Names3. 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 Times4. 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 Worksheet5. Troubleshooting Formulas Understanding Excel's Error Values Fixing Other Formula Errors Handling Formula Errors Using the Formula Error Checker Auditing a Worksheet6. 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 ToolPak7. 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 Another8. Working with Logical and Information Functions Adding Intelligence with Logical Functions Getting Data with Information Functions9. Working with Lookup Functions Excel's Lookup Functions Understanding Lookup Tables The Choose Function Looking Up Values in Tables10 Working with Date and Time Functions How Excel Deals with Dates and Times Using Excel's Date Functions Using Excel's Time Functions11. Working with Math Functions Excel's Math and Trig Functions Understanding Excel's Rounding Functions Summing Values Generating Random Numbers12. 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 Tools13. 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 Functions14. 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 Formula15. Using Excel's Business Modeling Tools Using What-If Analysis Working with Goal Seek Working with Scenarios16. 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 Analysis17. 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 Reports18. 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 Burrow19. Building Investment Formulas Working with Interest Rates Calculating the Future Value Working Toward an Investment Goal20. Building Discount Formulas Calculating the Present Value Discounting Cash Flows Calculating the Payback Period Calculating the Internal Rate of Return