Class Outline

This one-day, instructor-led course will teach you advanced formulas and functions to perform more complex calculations in Microsoft Excel. By the end of this course, you will be able to use logical and lookup functions; date/time and text functions; reference functions; and statistical and financial functions. You will learn about auditing and error-handling both of which are tools to help you prevent formula and function errors and, how to summarize data by using the consolidation feature and the subtotals feature. If your job requires you to work in Excel on a regular basis, working your way through this level is important.

The training covers these topics:

Logical and Lookup Functions

  • Using the IF Function
  • Using the And Function
  • Using the Or Function
  • Using the Not Function
  • Using VLOOKUP
  • Using HLOOKUP
  • Using XLOOKUP

Date and Time Functions

  • The Today and Now Functions
  • The Weekday and Workday Functions
  • The EOMONTH and EDATE Functions
  • Date Functions

Text Functions

  • Using the MID and FIND Functions
  • Using the LEN and FIND
  • Using TRIM, UPPER, LOWER, and Proper Functions
  • Using Concatenate
  • Using Flash Fill

Statistical Functions

  • Using the SUMIFS Function
  • Using the COUNTIF and AVERAGEIF Functions
  • Using the PMT Function

Advanced Formulas

  • Using the Evaluate Formula Feature
  • Adding a Watch
  • Using the IFERROR Function

Summarizing Data

  • Data Consolidation
  • Subtotals

Excel Keyboard Shortcuts