Select tickets
Course Code: 0200-240-19-W
Course Format: Instructor-Led Online
Prerequisites:
- Excel Level 2
In this course, you will cover how to: name cells and ranges, use those names in formulas, use Logical and Lookup function formulas, use the IF and IFS formulas, Data Validation, use Match and Indexing formulas, work with Date and Time formulas, and use the formula auditing tools to troubleshoot problems in formulas.
Topics:
Lesson 1: Logical & Lookup Functions
- Logical Functions
- Names
- Naming Cells
- Editing Names
- IF Functions
- IF Functions
- Nested IF Functions
- Nested IF Statements
- IFS Functions
- IFS Functions
- AND Functions
- Using an AND in an IF Function
- OR Functions
- OR Functions
- Using an OR in an IF Function
- NOT Functions
- NOT Functions
- Using a NOT Function in an IF Function
- Lookup Functions
- VLOOKUP Functions
- VLOOKUP Function
- HLOOKUP Function
- HLOOKUP Function
- Data Validation List
- Data Validation List
- Creating a Data Validation List
- MATCH Function
- MATCH Function
- INDEX Function
- INDEX Function
- INDEX Array Form
- INDEX Reference form
Lesson 2: Complex Summing Functions
- Filtering Data for Unique Values
- Using the Advanced Filter
- SUMIF Functions
- Using the SUMIF Formula
- AVERAGEIF Function
- Using the AVERAGEIF Formula
- COUNTIF Functions
- Using the COUNTIF Formula
- SUMIFS Functions
- Using the SUMIFS Formula
- COUNTIFS Functions
- Using the COUNTIFS Formula
- MAXIFS and MINIFS Functions
Lesson 3: Date & Time Functions
- Time
- Using the Time Functions
- Adding and Subtracting Times
- Custom Time Formatting Codes
- NOW Function
- Using the NOW Function
- Refreshing the NOW Function
- Dates
- Understanding Dates
- Date Functions
- Using the TODAY Function
- Finding the Difference Between Two Dates
- Using the DATEDIF Function
- Using the Days Formula
- WORKDAY Functions
- Using the WORKDAY Formula
- Using the EDATE Formula
- NETWORKDAYS Functions
- Calculating Working Days
- Finding the Day of the Week
- Using the WEEKDAY Formula
- SWITCH Function
- Using the SWITCH Function
- Finding Week Number
- Using the WeekNum Formula
- Using the ISOWEEKNUM Formula
Lesson 4: Formula Auditing
- Formula Auditing
- The Formula Auditing Group
- Tracing Formulas
- Tracing Precedents
- Tracing Dependents
- Removing Arrows
- Errors
- Errors
- Error Messages
- Error Checking Options
- Error Checking
- Error Checking
- Formula Options
- Evaluating Formulas
- Using the Evaluate Formula Tool
- Watch Window
- Using the Watch Window
- To Add a Cell to the Watch Window
- To Delete a Cell from the Watch Window
- Calculations
- Changing the Calculation Option