(212) 684-5151

Excel for Power Users
Excel for Power Users

Price: $299.00

Course Code: EP | Hours: 7

Advanced Excel Training in NYC

Learn to solve challenging problems in Excel by applying and nesting various functions. You'll get an introduction to VBA, Excel's programming language, to automate workflow and repetitive tasks. Through various exercises and projects, you will reinforce and build upon knowledge gained in intermediate-level courses. 

Who Should Attend This Course?

This course is perfect for those who feel comfortable with topics covered in our Advanced Excel for Business course and want to elevate their skills and tackle difficult exercises in Excel. As opposed to standard courses where the desired function or formula is known beforehand, in this courses attendees (with instruction and guidance from an Excel expert) will have to devise the proper set of functions needed to solve the exercise. 

Course Objective: The goal of this course is not to simply provide attendees with complicated formulas for future reference, but to teach users how Excel's functions can be used to solve complex problems. 

Related Courses

Want to become a Power User but don't meet the prerequisites? Our Excel for Business Bootcamp is a 3-day program that will prepare you for the course. 

Looking to learn VLOOKUP and Pivot Tables? Master these functions and tools in Intermediate Excel for Business

Do you work in investment banking or a financial firm and have a strong foundation in Excel? Our Advanced Excel for Financial Modeling course will show you the tricks and techniques you need to become a top financial analyst. 

What You'll Learn in this Class:

  • Advanced summation techniques
  • Advanced Lookup techniques
  • Advanced Text Functions
  • Advanced conditional formatting
  • Arrays
  • Creating Macros

Prerequisites:

Students are recommended to take the following course or possess equivalent knowledge:

Next Classes:

Related Classes:

See Also:

Excel for Power Users Course Outline

Advanced summation techniques

• Running total
• Dynamic running total with SUM & OFFSET
• SUM & INDEX
• Sum through N items with SUM & OFFSET

Advanced Lookup techniques

• VLOOKUP with two conditions
• VLOOKUP with multiple rates
• INDEX-MATCH with cell anchoring
• VLOOKUP & INDIRECT to pull from two tables
• Partial Match VLOOKUP

Advanced Text Functions

• Review UPPER, LEN, FIND, SUBSTITUTE, RIGHT/LEFT, MID functions
• Capitalize the first letter with LEFT, UPPER, LEN, MID
• Find the name in an email address
• Find the first word in string of text

Advanced conditional formatting

• Review: Highlight the entire row with formulas & cell locking
• Highlighting duplicate cells in two ranges with AND & COUNTIF
• Dynamically highlight bottom N values with SMALL
• Highlight every other row in a table

Other Advanced Techniques & Functions

• Create submenus with INDIRECT & OFFSET
• CHOOSE function to select scenarios
• Dynamic named ranges
• Dynamic charting with Waterfall chart
• SUMIFS with advanced conditions

Arrays

• Single-cell array formulas
• Advantages & Disadvantages of Arrays
• TRANSPOSE Function
• MAX & MIN Functions with Arrays
• INDEX-MATCH with Arrays to retrieve the entire row

Creating Macros Through Fundamental VBA

• Finding the name of the current worksheet
• Finding the last Row used
• Finding the last column used
• VBA to create a Clustered Column Chart
• Creating a Pivot Table using our captured last row and column