
All Excel classes include
Free retakes
Detailed curriculum
Live instructor
Flexible rescheduling
Certificate of Completion
Excel Class - Advanced

Excel Class - Advanced
Learn to work with complex data sets and perform advanced analysis in this advanced Excel course. Discover more advanced functionality for Pivot Tables, and find ways to automate repetitive tasks. Learn how to use advanced tools for analysis, functions, and formatting. In this advanced Excel class, you will learn:
- Locking Cells: Learn to lock rows or columns with mixed cell referencing, which is helpful with formulas that span multiple rows or columns.
- Auditing Cells: Find out options to discover errors within formulas for errors and trace for dependent and precedent values.
- Advanced Conditional Formatting: Discover ways to automatically apply conditional formatting in Excel using formulas, such as all positive values appearing as green and all negative values appearing as red.
- Nested IF Statements: Go beyond the standard IF statements limited to either TRUE or FALSE. Use nested IF statements to include multiple options. For example, a teacher can evaluate cell values and have a range of 90 to 100 report the grade as an "A" while 80-89 would report as a "B".
- AND/OR IF Statements: Create complex logic statements in Excel using AND/OR IF statements. These statements can match either one conditional or multiple conditionals.
- Goal Seek: Find values needed to create a desired output across an Excel sheet or workbook. Use goal seek tool to find values necessary to meet a specific result, such as the amount necessary to meet a savings goal.
- Data Tables: Find out the impact of making changes to a single input impacts a targeted output, or build sensitivity analysis. Data Tables in Excel can show how compound interest can grow, allowing the exploration of various interest rates.
- Pivot Charts: Learn to visualize data compiled into Pivot Tables using Pivot Charts.
- MATCH VLOOKUP: Create a more powerful VLOOKUP by using the the MATCH function, which can locate the column input.
- MATCH INDEX: Find out how this flexible lookup makes it possible to perform a reverse lookup, and then discover how to combine two MATCH’s within the INDEX for building Excel formulas that are capable of data extraction.
- Hot Keys: If you prefer to work from your keyboard rather than a mouse, learn to create Hot Keys that automate functions and commands with a keystroke or combination of keystrokes. From text formatting to calculations, you can leave your mouse alone and perform Excel functions directly from your keyboard.
This course is available individually or as part of these certificate programs:
Excel Class - Advanced - Course Dates
All classes are led by a live instructor. Class times listed are Eastern time.
Advanced Excel Class Topics Covered
The Advanced Microsoft Excel training course covers the following topics:
Excel Cell Management
- Cell Locking: Learn to create powerful formulas by locking columns or rows.
- Cell Auditing: Understand the relationship between formulas and cells.
- Windows: Discover techniques for editing active windows.
- Hot Keys: Learn to use the ribbon as a custom set of personalized shortcuts.
Using Special Formatting in Excel
- Date functions: Learn to calculate dates using several functions
- Conditional Formatting of Formulas: Discover how to use conditional formatting of formulas using custom rules.
Understanding Advanced Excel Functions
- Nested IF statements: Learn to use nested IF statements to create multiple options for populating cells
- IF statements using AND/OR: Learn additional IF functions available by using AND / OR
Using Excel for What If Analysis
- Goal Seek: Adjust input values to find desired results
- Data Tables: Display the range of effects for one or two variables on a formula using Data Tables.
Understanding Advanced Analytical Tools in Excel
- Data Consolidation: Learn to summarize data from separate ranges, consolidating it to a specific output range
- Conditional SumProduct: Learn how using SumProduct with conditions excludes data that fails to meet specific criteria
- Pivot Table-Calculations: Learn to create calculated columns and rows using a Pivot Table to expand beyond source data
- Pivot Charts: Learn to build dynamic visual representations of data from pivot tables
Using Excel’s Advanced Database Functions
- MATCH function: Learn to return the column or row number of a lookup value
- VLOOKUP-MATCH: Create more accurate VLookUps through enhancing the determination of the Column Index number
- INDEX-MATCH: Efficiently return values and references from cells at the intersection of the row and column
- INDEX-Double MATCH: Learn to use a second Match function to create powerful two-way lookups
Learning Macros in Excel
- Recording Macros: Learn to record Macros involving formatting and calculations
- Relative Macros: Understand how to create relative reference macros used in ad-hoc reporting
Advanced Excel Course prerequisites
This course requires knowledge of Excel equivalent to the topics in our introductory and intermediate Excel Course, including knowledge of basic formulas and functions, charts, formatting, and absolute cell references. A computer with Excel is provided for use during the course for in-person training, or use your own computer for live online courses.
Advanced Excel Course materials
You will receive a comprehensive Excel course manual for this class.