Curriculum – Microsoft Excel – Advanced Excel Formulas & Functions

Course Structure & Outline

Excel Formulas 101

  • Introduction
  • The Formula Library & Auditing Tools
  • DEMO: Evaluate Formula
  • Basic Formula Syntax
  • Fixed, Relative, & Mixed References
  • Common Errors & the IFERROR statement
  • Function, CTRL & ALT Shortcuts
  • Creating Custom Data Validation Rules
  • Fixed vs. Volatile Functions
  • Formulas 101

Conditional Statements & Logical Operators

  • Introduction
  • Anatomy of the IF Statement
  • Nested IF Statements
  • AND/OR Operators
  • NOT vs. “<>”
  • Fixing Errors with IFERROR
  • Common IS Statements
  • Logical Operators

Statistical Functions

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

Lookup/Reference Functions

  • Introduction
  • 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
  • UPDATE: VLOOKUP Correction
  • The OFFSET Function
  • Combining OFFSET with COUNTA to return a variable list
  • PROJECT SHOWCASE: Using OFFSET to create a dynamic scrolling chart
  • Lookup/Reference Functions

Text Functions

  • Introduction
  • UPPER/LOWER/PROPER & TRIM
  • The CONCATENATE Function (&)
  • LEFT/MID/RIGHT & LEN
  • TEXT/VALUE
  • SEARCH/FIND
  • Categorizing Data with IF(ISNUMBER(SEARCH))
  • Combining RIGHT, LEN, and SEARCH
  • The SUBSTITUTE Function
  • Text Functions

Date & Time Functions

  • Introduction
  • DATEVALUE: Your New BFF
  • Date Formatting & Fill Series
  • TODAY()/NOW()
  • YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
  • The EOMONTH Function
  • The YEARFRAC Function
  • WEEKDAY, WORKDAY & NETWORKDAYS
  • The DATEDIF Function
  • PROJECT SHOWCASE: Designing a Sample Budget Pacing Tool
  • Date/Time Functions

Formula-Based Formatting

  • Introduction
  • Creating, Editing & Managing Formula-Based Rules
  • Highlighting Every Other Row Using the MOD function
  • Formatting Cells Based on the Value of Another Cell
  • Formatting Cells Using Statistical Functions
  • Formatting Cells Using Text Functions & Logical Operators
  • Formula-Based Formatting

Array Formulas

  • Introduction
  • Rules of Array Functions
  • Pros & Cons of Array Functions
  • Vertical, Horizontal, and 2-Dimensional Array Constants
  • Using Array Constants in Formulas
  • Named Array Constants
  • The Transpose Function
  • Linking Data Between Sheets: Array vs. Non-Array Comparison
  • Returning the “X” Largest Values in a Range
  • Counting Characters Across Cells
  • Creating a “MAX IF” Array Formula
  • “MAX IF” with Multiple Criteria
  • Using the Double Unary Operator (“–“)
  • Array Formulas

Bonus Functions

  • Introduction
  • The INDIRECT Function
  • HYPERLINK: as Awesome as it Sounds
  • Tapping into Real-Time Data with WEBSERVICE & FILTERXML

Wrapping Up
Conclusion & Next Steps

For inquiry and demo, please contact Atal @9818569825

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.