# Excel Functions and Formulas

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

View all Upcoming Classes