Class Outline
This one-day, instructor-led course will teach you advanced formulas and functions to perform more complex calculations in Microsoft Excel. By the end of this course, you will be able to use logical and lookup functions; date/time and text functions; reference functions; and statistical and financial functions. You will learn about auditing and error-handling both of which are tools to help you prevent formula and function errors and, how to summarize data by using the consolidation feature and the subtotals feature. If your job requires you to work in Excel on a regular basis, working your way through this level is important.
The training covers these topics:
Logical and Lookup Functions
- Using the IF Function
- Using the And Function
- Using the Or Function
- Using the Not Function
- Using VLOOKUP
- Using HLOOKUP
- Using XLOOKUP
Date and Time Functions
- The Today and Now Functions
- The Weekday and Workday Functions
- The EOMONTH and EDATE Functions
- Date Functions
Text Functions
- Using the MID and FIND Functions
- Using the LEN and FIND
- Using TRIM, UPPER, LOWER, and Proper Functions
- Using Concatenate
- Using Flash Fill
Statistical Functions
- Using the SUMIFS Function
- Using the COUNTIF and AVERAGEIF Functions
- Using the PMT Function
Advanced Formulas
- Using the Evaluate Formula Feature
- Adding a Watch
- Using the IFERROR Function
Summarizing Data
- Data Consolidation
- Subtotals