One Day • Instructor Led
In this class you will learn advanced Excel formulas and functions to bring your spreadsheets to the next level. Learn how to develop more complex formulas and learn the power of arrays. Learn formulas to help you automate your spreadsheets to simplify them for others to use, as well as yourself.
Prerequisites
An understanding of Excel Intermediate and Advanced topics including basic formulas, IF Statements and basic Lookup formulas. This is an intense one-day course covering functions and formulas.
Course Outline
Lesson 1: Logical & Information Functions
- Nesting IF Statements
- Nesting IF + AND functions
- Complex IF functions
Lesson 2: Text Functions
- Find and length function
- Concatenate
- Search, length and hyperlinks
Lesson 3: Additional Functions
- IS and Blank functions – COUNTBLANK, ISTEXT, ISODD, ISBLANK
- Array functions – TRANSPOSE and SUM and IF
- Formatting Functions
Lesson 4: Lookups
- VLOOKUP
- HLOOKUP
Lesson 5: Dynamic Drop-down Lists
- VLOOKUP drop-down lists
- IF and VLOOKUP
- IF and concatenate
- INDIRECT/VLOOKUP
- OFFSET drop-down lists
- Offset, indirect and CountA functions
Lesson 6: Filtered Columns
- ISTEXT and ROW
- SMALL
- INDEX and IFERROR
- ARRAY
- Normal validation list
- Removing blanks in the dropdown list
- ROW, COUNTBLANK and OFFSET