Excel Level III: Advanced
- Format
- Live or Self-paced
Choose how you learn
Compare formats, then explore the details for your choice.
Book directly on nobledesktop.com. Opens in a new tab.
Book live on Noble DesktopSummary
This advanced Excel course is designed for experienced users who want to take on more complex calculations, analysis tools, and workflow improvements. Students learn advanced navigation techniques, mixed references, auditing tools, custom conditional formatting with formulas, date functions, and custom number formats, along with more sophisticated logic using nested IF statements and IF formulas with AND/OR criteria.
The course also introduces What-If Analysis with Goal Seek and Data Tables, advanced PivotTable calculations, Pivot Charts, XMATCH, INDEX-MATCH, INDEX with Double MATCH, macros, and dynamic arrays. It prepares students to build more flexible models, automate repetitive tasks, and approach spreadsheet analysis with greater precision.
Who this course is for
This class is meant for those with prior experience with basic and intermediate-level Excel skills looking to learn Excel’s most advanced functions and capabilities, including advanced lookup functions, Pivot Charts, and macros.
Prerequisites
Attendees must have Excel proficiency equivalent to our Intermediate Excel course, including VLOOKUP, Pivot Tables, and IF statements.
Curriculum
What you'll learn
- Use advanced navigation tools, Autofill techniques, Hot Keys, and Go To Special to move through worksheets and work more efficiently.
- Build stronger formulas with mixed references, cell auditing tools, date functions, and custom number formats.
- Create advanced logic with nested IF statements and IF formulas that incorporate AND/OR criteria for more flexible results.
- Perform What-If Analysis with Goal Seek and Data Tables to test variables and evaluate possible outcomes.
- Analyze data with advanced PivotTable tools, including base fields and sets, calculated fields, and Pivot Charts.
- Use XMATCH, INDEX-MATCH, macros, and dynamic arrays to create powerful lookups, automate tasks, and complete an end-of-class project reviewing key concepts.
Course syllabus
Advanced Navigation
Advanced Navigation
- Advanced navigation techniques
Fill Review
- Review of Autofill conventions and techniques
Cell Management
Mixed Reference Formulas
- Create powerful formulas by locking either the column or the row
Hot Keys
- Transform the ribbon into a visual listing of pre-assigned shortcuts
Cell Auditing
- Observe the relationship between formulas and cells
Go To Special
- Quickly select cells that meet certain criteria
Special Formatting
Conditional Formatting-Formulas
- Create custom rules for Conditional Formatting with formulas
Date Functions
- Calculate dates with a variety of functions
Custom Number Formats
- Customize number formats to meet specific requirements
Advanced Functions
Nested IF statements
- Nested "IF" statements allow for more than just two possibilities in a single cell
IF statements with AND/OR
- Expand the functionality of the IF function by adding an AND / OR criteria
What If Analysis
Goal Seek
- Find the desired result by adjusting an input value
Data Tables
- Data Tables show the range of effects of one or two different variables on a formula
Advanced Analytical Tools
Calculation Options
- Minimize volatility by changing calculation options
Pivot Table-Base Fields & Sets
- Analyze data in a Pivot Table with increased granularity by defining base fields and sets
Pivot Table-Calculations
- Create calculated rows or columns in a Pivot Table that go beyond the source data
Pivot Charts
- Create dynamic, graphical representations of Pivot Table data
Advanced Database Functions
XMATCH function
- Return the relative position (column or row number) of a lookup value
INDEX-MATCH
- Efficiently return a value or reference from a cell at the intersection of the row and column
INDEX-Double MATCH
- Use a second Match function to create a powerful, two-way lookup tool
Introduction to Macros
Recording Macros
- Record macros that involve formatting and calculations
Dynamic Arrays
Dynamic Arrays
- Use formulas that can return arrays of variable size
End of Class Projects
Projects
- End of class project to review key concepts from the class
What's included
- Free course retake within one year to refresh the material and gain practice.
- Class recordings
Stated by the provider. Confirm what your tuition covers before enrolling.
Live classes
Choose dates and book your instructor-led class on nobledesktop.com.
-
–
In-person or live online
Starts at 18:00 · America/New_York (EDT); final session ends at 21:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
–
In-person or live online
Starts at 18:00 · America/New_York (EST); final session ends at 21:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
–
In-person or live online
Starts at 18:00 · America/New_York (EST); final session ends at 21:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
–
In-person or live online
Starts at 18:00 · America/New_York (EDT); final session ends at 21:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
–
In-person or live online
Starts at 18:00 · America/New_York (EDT); final session ends at 21:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EDT); final session ends at 17:00 EDT
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
-
In-person or live online
Starts at 10:00 · America/New_York (EST); final session ends at 17:00 EST
Confirm current dates and availability on Noble Desktop before booking.
Self-paced course
Learn through recorded lessons on your own schedule.
This course is available for 30 days. You can choose when to start your access period. Once you activate, you will have 30 days to complete it (access the course materials, quizzes, projects and videos). You may request one extension of seven (7) days. Other extension requests will be evaluated on a case-by-case basis. Videos are not downloadable.
- Tuition
- $299
- Course length
- 6 hours
- Schedule
- On your schedule
Learn all of the most complex features of Microsoft Excel in this advanced training course. Once you’ve mastered the basics of building and organizing spreadsheets using Excel, you can learn to manipulate and visualize that data to improve your workflow and draw deeper insights.
In this class, you will find out how to manage spreadsheets, utilize advanced analytics tools, and write macros to improve efficiency. You will also become familiar with complex Excel functions, including MATCH, VLOOKUP-MATCH, and INDEX-Double MATCH. The tools you’ll learn in this class will apply to virtually any job setting where you must organize large amounts of complex data.
Why Learn Excel?
Excel is one of the most commonly used business applications on the planet, so no matter what field you work in, learning Excel has the potential to help optimize your workflow and improve your productivity. Whether you're working as a professional Data Analyst or just working at a company with a lot of information to catalog and organize, learning Excel can pay long-term dividends.
Very few jobs strictly use Excel, so anyone looking to start a new career will need supplemental training. However, learning Excel is foundational for anyone hoping to work in a data analytics or financial career.
Who the self-paced course is for
This class is meant for those with prior experience with basic and intermediate-level Excel skills looking to learn Excel’s most advanced functions and capabilities, including advanced lookup functions, Pivot Charts, and macros.
Self-paced prerequisites
Attendees must have Excel proficiency equivalent to our Intermediate Excel course, including VLOOKUP, Pivot Tables, and IF statements.
Self-paced curriculum
What you'll learn self-paced
- Cell management, including cell locking, auditing, and hot keys
- Special formatting for calculating dates
- Use advanced functions such as nested IF statements
- Learn advanced analytical tools for data consolidation, conditions to exclude data, and pivot charts
- Use advanced database functions including MATCH, VLOOKUP-MATCH, and INDEX-Double MATCH
- Record macros and relative reference macros for ad hoc reporting
- Create a project that applies key concepts from the class
Self-paced syllabus
Advanced Navigation
Advanced Navigation
- Advanced navigation techniques
Fill Review
- Review of Autofill conventions and techniques
Cell Management
Advanced Cell Locking
- Create powerful formulas by locking either the column or the row
Hot Keys
- Transform the ribbon into a visual listing of pre-assigned shortcuts
Cell Auditing
- Observe the relationship between formulas and cells
Go To Special
- Quickly select cells that meet certain criteria
Special Formatting
Conditional Formatting-Formulas
- Create custom rules for Conditional Formatting with formulas
Date Functions
- Calculate dates with a variety of functions
Custom Number Formats
- Customize number formats to meet specific requirements
Advanced Functions
Nested IF statements
- Nested "IF" statements allow for more than just two possibilities in a single cell
IF statements with AND/OR
- Expand the functionality of the IF function by adding an AND / OR criteria
What If Analysis
Goal Seek
- Find the desired result by adjusting an input value
Data Tables
- Data Tables show the range of effects of one or two different variables on a formula
Advanced Analytical Tools
Calculation Options
- Minimize volatility by changing calculation options
Conditional SumProduct
- Use SumProduct with conditions to exclude data that does not meet certain criteria
Pivot Table-Base Fields & Sets
- Analyze data in a Pivot Table with increased granularity by defining base fields and sets
Pivot Table-Calculations
- Create calculated rows or columns in a Pivot Table that go beyond the source data
Pivot Charts
- Create dynamic, graphical representations of Pivot Table data
Advanced Database Functions
XMATCH function
- Return the relative position (column or row number) of a lookup value
INDEX-MATCH
- Efficiently return a value or reference from a cell at the intersection of the row and column
INDEX-Double MATCH
- Use a second Match function to create a powerful, two-way lookup tool
Introduction to Macros
Recording Macros
- Record macros that involve formatting and calculations
Dynamic Arrays
Dynamic Arrays
- Use formulas that can return arrays of variable size
End of Class Projects
Projects
- End of class project to review key concepts from the class
What's included with self-paced
- Self-paced video lessons
Self-paced lessons
Watch free previews and explore the lessons included with enrollment at Noble Desktop.
Excel Level III: Advanced Course Online (Self-Paced)
-
Advanced Cell Lock
Lesson 1: Cell Management
10:00
Lock cells in Excel by using mixed referencing to maintain specific row or column values when autofilling formulas.
This preview is temporarily unavailable. Please try again later.
-
Enrollment required
Hot Keys
Lesson 1: Cell Management
7:14
Utilize the Alt key on a PC to access Excel's ribbon commands and create custom keyboard shortcuts for efficient navigation and task execution.
View enrollment options at Noble Desktop -
Enrollment required
Go To Special
Lesson 1: Cell Management
8:51
Use "Go To Special" in Excel to quickly select specific cell types, such as constants, blanks, or visible cells, for efficient data management and editing.
View enrollment options at Noble Desktop -
Date Functions
Lesson 2: Special Formatting
7:29
Calculate future dates, days between dates, workdays, week numbers, and month ends using Excel's DAYS, NETWORKDAYS, WEEKNUM, EOMONTH, and EDATE functions.
This preview is temporarily unavailable. Please try again later.
-
Enrollment required
Adv Conditional Formatting
Lesson 2: Special Formatting
9:35
Apply advanced conditional formatting using formulas to format cells based on criteria in different columns.
View enrollment options at Noble Desktop -
Enrollment required
Nested IFs
Lesson 2: Special Formatting
9:37
Nest multiple if statements to evaluate more conditions and provide different responses based on the evaluated criteria.
View enrollment options at Noble Desktop -
If AND OR
Lesson 3: Advanced Functions
7:43
Combine the IF statement with AND or OR functions to evaluate multiple conditions and return specific results based on whether those conditions are true or false.
This preview is temporarily unavailable. Please try again later.
-
Enrollment required
Goal Seek
Lesson 4: What If Analysis
4:18
Use Goal Seek in Excel to determine the required growth rate or exam score needed to reach a specific target value by adjusting a related variable.
View enrollment options at Noble Desktop -
Enrollment required
Data Tables
Lesson 4: What If Analysis
7:32
Create data tables in Excel to analyze the impact of different variables on a formula.
View enrollment options at Noble Desktop -
Conditional Sumproduct
Lesson 5: Advanced Analytical Tools
9:48
Use conditional sum product to calculate totals or averages by applying criteria to filter specific data subsets.
This preview is temporarily unavailable. Please try again later.
-
Enrollment required
Pivot Table Variances
Lesson 5: Advanced Analytical Tools
6:17
Create a pivot table, group data by quarters and years, and use "difference from" and "percentage difference from" calculations to show variances between years.
View enrollment options at Noble Desktop -
Enrollment required
Pivot Calculations
Lesson 5: Advanced Analytical Tools
10:22
Perform calculations within pivot tables using calculated fields and items for efficient data analysis.
View enrollment options at Noble Desktop -
Enrollment required
Pivot Charts
Lesson 5: Advanced Analytical Tools
6:05
Create dynamic graphical representations of pivot table data using pivot charts in Excel.
View enrollment options at Noble Desktop -
Match Function
Lesson 6: Advanced Database Functions
4:25
Find the relative position of a lookup value within a single column or row using the match function.
This preview is temporarily unavailable. Please try again later.
-
Enrollment required
Vlookup Match
Lesson 6: Advanced Database Functions
8:08
Enhance VLOOKUP accuracy by using the MATCH function to dynamically determine the column index number, allowing for more efficient data retrieval with mixed referencing.
View enrollment options at Noble Desktop -
Enrollment required
Index Match
Lesson 6: Advanced Database Functions
7:02
Combine INDEX and MATCH functions to efficiently locate a value in a row or column, allowing lookups in either direction.
View enrollment options at Noble Desktop -
Enrollment required
Index Double Match
Lesson 6: Advanced Database Functions
7:07
Utilize the Index Double Match function to perform a two-way lookup by identifying both row and column positions for retrieving specific data from a table.
View enrollment options at Noble Desktop -
Enrollment required
Windows
Lesson 6: Advanced Database Functions
6:56
Learn to use keyboard shortcuts and commands in Windows to efficiently navigate and arrange Excel windows for better multitasking.
View enrollment options at Noble Desktop -
Enrollment required
Working Across Sheets
Lesson 6: Advanced Database Functions
4:38
Apply formatting and summarize calculations across multiple sheets by selecting ranges while holding the shift key and using functions like SUM and autofill for common cells.
View enrollment options at Noble Desktop -
Macros
Lesson 7: Introduction to Macros
8:29
Automate Excel tasks by creating and running macros using the developer tab, keyboard shortcuts, buttons, and the quick access toolbar to efficiently repeat actions like entering data.
This preview is temporarily unavailable. Please try again later.
-
Enrollment required
MacrosReport
Lesson 7: Introduction to Macros
6:14
Create a macro to automate daily report formatting tasks, reducing a two-minute manual process to under a second.
View enrollment options at Noble Desktop -
Enrollment required
Consolidation
Lesson 8: Dynamic Arrays
5:10
Use the consolidate function to sum values across multiple sheets into a summary worksheet and enable automatic updates when source data changes.
View enrollment options at Noble Desktop