In this advanced self-paced Excel course, you’ll learn how to unlock the full potential of Microsoft Excel by moving beyond basic spreadsheet organization into advanced data manipulation and visualization techniques. The course focuses on improving workflow efficiency, generating deeper insights from data, and using Excel’s powerful analytics tools, including macros, to manage complex spreadsheets more effectively.
As you progress, you’ll work with advanced functions such as MATCH, VLOOKUP-MATCH, and INDEX-Double MATCH to handle large and complex datasets across a wide range of professional scenarios. You’ll also manage cells using locking, auditing, and keyboard shortcuts, apply special formatting for date calculations, build nested IF statements, leverage analytical tools for consolidation, filtering, and pivot charts, record and create relative reference macros for ad-hoc reporting, and complete a final project to apply the key concepts learned throughout the course.
Course Outline
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 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
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.
