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.
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.
Corporate training available
Students get a custom workbook with step-by-step instructions.
Modern computer classroom with your choice of Mac or PC to work on.
Small In-Person Classes
Get personalized attention in small groups with an expert instructor in the classroom.
Refresh the course materials within six months for free as long as there's space in the class.
Full Course SyllabusDownload PDF Outline
Advanced summation techniques
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
Single-cell array formulas
Advantages & Disadvantages of Arrays
MAX & MIN Functions with Arrays
INDEX-MATCH with Arrays to retrieve the entire row
Data Analysis ToolPak
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
Frequently Asked Questions
Do I need to bring a computer?
No. We provide a computer with the necessary software for each attendee. Choose a Mac or a PC during the checkout process.
Are discounts available for this course?
No. This course is not eligible for any discounts.
We offer discounts for packages of classes, including our Excel Bootcamp, SQL Bootcamp, Data Analytics, and PowerPoint Bootcamp.
We also provide discounts for ten or more classes purchased together. Please reach out for more information on our voucher programs.
Does this course come with a book?
Yes. This course includes a complimentary book or manual at no additional cost.
What if I need to retake this course? May I retake it for free?
Yes. You may retake this course for free once within six months, provided that the class is running with an available seat. For more information, visit our policies page.