Course Outline

Mastering Excel 2003 Formulas & Functions
A one-day, hands-on course that focuses on several of Excel’s most powerful advanced functions, including MATCH and INDEX, and other complex lookups. Participants will learn to solve complex problems using multi-level nested functions and several variations of array formulas. The course covers advanced text functions such as SEARCH and INDIRECT and shows participants how to use Data Validation lists for on-demand data analysis. The course also covers advanced uses of Names for ranges, formulas and constants. 6 hours

Advanced lookup applications

  • Using Data Validation lists for lookups
  • Create a variance calculator tool with HLOOKUP
  • Looking left with MATCH and INDEX
  • Lookup with multiple lookup values

Advanced use of Names

  • Store constants and formulas as named objects
  • Using worksheet-level names
  • Using name intersections in formulas

Array formulas

  • Introduction to array formulas
  • Create single-cell array formulas

Advanced text functions

  • Create compound Data Validation lists using INDIRECT
  • Use text for complex number formatting

Prerequisites:  Excel level 2, Excel for Finance Professionals or equivalent experience. Participants should be experienced, intermediate-advanced Excel users.

Revised 11/10/2011
Return to Class Schedules             Computer Magic Training Home Page
Computer Magic Training • 4030 Moorpark Avenue • Suite 108 • San Jose, California 95117 • 408-261-2600