Microsoft Excel For Business

Introduction to Formulas and Functions
Intro
Say hi to your fellow students!
Share your new course with your friends!
Course Structure & Outline

Excel Formulas 101
Introduction
Handout - Formulas 101
Excel Download - Fixed Relative References
The Formula Library & Auditing Tools
Basic Formula Syntax
Fixed, Relative, & Mixed References
Common Errors & the IFERROR statement
Function, CTRL & ALT Shortcuts
Handout - Mac Shortcuts
BONUS: Shortcuts for Mac Users
Creating Custom Data Validation Rules
Fixed vs. Volatile Functions

Logical Operators
Introduction
Handout - Logical Operators
Excel Download - Logical Operators
Anatomy of the IF Statement
Nested IF Statements
AND/OR Operators
NOT vs. \\"
Fixing Errors with IFERROR
Common IS Statements

Statistical Functions
Introduction
Handout - Statistical Functions
Excel Download - Statistical Functions
Basic Statistical Functions
SMALL/LARGE & RANK/PERCENTRANK
RAND() & RANDBETWEEN
The SUMPRODUCT Function
COUNTIFS/SUMIFS/AVERAGEIFS
PROJECT SHOWCASE: Building a Basic Dashboard with COUNTIFS & SUMIFS

Lookup/Reference Functions 1
Introduction
Handout - Lookup/Reference Functions
Excel Download - Lookup/Reference Functions
Working with Named Arrays
ROW/ROWS & COLUMN/COLUMNS
VLOOKUP/HLOOKUP
Joining Data with VLOOKUP
Fixing Errors with IFERROR & VLOOKUP
VLOOKUP Reference Array Options
The INDEX Function
The MATCH Function
Using INDEX & MATCH together
Combining MATCH with VLOOKUP
The OFFSET Function
PROJECT SHOWCASE: Using OFFSET to create a dynamic scrolling chart

Lookup/Reference Functions 2
The INDEX Function
The MATCH Function
Using INDEX & MATCH together
Combining MATCH with VLOOKUP
The OFFSET Function
PROJECT SHOWCASE: Using OFFSET to create a dynamic scrolling chart

Text Functions
Introduction
Handout - Text Functions
Excel Download - Text Functions
UPPER/LOWER/PROPER & TRIM
The CONCATENATE Function (&)
LEFT/MID/RIGHT & LEN
TEXT/VALUE
SEARCH/FIND
Categorizing Data with IF(ISNUMBER(SEARCH))
Combining RIGHT