A new session of each course opens each month, allowing you to enroll whenever your busy schedule permits!
How does it work? Once a session starts, two lessons will be released each week, for the six-week duration of your course. You will have access to all previously released lessons until the course ends.
Keep in mind that the interactive discussion area for each lesson automatically closes 2 weeks after each lesson is released, so you’re encouraged to complete each lesson within two weeks of its release.
The Final Exam will be released on the same day as the last lesson. Once the Final Exam has been released, you will have 2 weeks plus 10 days to complete the Final and finish any remaining lessons in your course. No further extensions can be provided beyond these 10 days.
Week One
Lesson 01 - Looking Up Data in Arrays
Wednesday
This lesson introduces advanced lookup functions that help you retrieve values from large tables and arrays. You will begin with the basics of why lookup functions are important, then practice with VLOOKUP and HLOOKUP to search vertically or horizontally. You will explore their limitations and see how XLOOKUP provides more flexibility. Finally, you will learn how XLOOKUP works with dynamic arrays and discover related functions such as MATCH, INDEX, XMATCH, and FILTER, giving you a complete toolkit for looking up and returning data.
Lesson 02 - Creating Simple Excel Databases
Friday
In this lesson, you will learn to treat Excel worksheets like databases. You will start by importing data from text files, renaming tables, and using data entry forms to manage records. Then you will organize data with sorting and filtering, including slicers and advanced filter criteria. The lesson concludes with D-functions, where you will practice using DMAX and other database functions to query tables and return specific results.
Week Two
Lesson 03 - Managing Data with Power Query and the Data Model
Wednesday
This lesson focuses on transforming and consolidating data. You will load tables into Power Query, clean and rename queries, and refresh data as it updates. You will then practice merging and appending queries, as well as adding calculated fields. Next, you will explore the Data Model, where you'll load multiple tables, create relationships between them, and use Power Pivot to combine data from different sources for more powerful analysis.
Lesson 04 - Analyzing Data
Friday
In this lesson, you will explore Excel's advanced analysis tools. You will load the Analysis ToolPak, use the Analyze Data tool, and create forecast sheets. You will also explore descriptive statistics, correlation, histograms, and rank-and-percentile analysis. Then you will move to What-If analysis, using Goal Seek for single-variable solutions, Solver for multi-variable optimization, and Scenarios for testing different input combinations.
Week Three
Lesson 05 - Ensuring Data Integrity
Wednesday
This lesson covers techniques for keeping data accurate and reliable. You will use Data Validation to create rules that control entries, limit input to lists, and prevent duplicates. You will also practice error checking with IFERROR, ISNUMBER, and ISDATE, as well as formulas that cross-check data. Finally, you will explore version control and tracking features to monitor edits and prevent data loss.
Lesson 06 - Conditional Formatting
Friday
In this lesson, you will learn how to highlight data dynamically with conditional formatting. You will start with simple rules based on cell values and icon sets, then practice modifying and managing multiple rules. You will also apply advanced techniques such as top/bottom rules, formulas that control formatting, and rules that identify unique or duplicate values.
Week Four
Lesson 07 - Creating and Using Macros
Wednesday
This lesson introduces automation with macros. You will learn about macro security, enabling macro recording, and preparing macro-enabled files. Then you will record and test your first macros, assigning them to keyboard shortcuts, buttons on the Quick Access Toolbar, or even custom ribbon commands. The lesson concludes with an introduction to the VBA editor, giving you a first look at how recorded macros work and how you can edit them.
Lesson 08 - PivotTable Concepts
Friday
In this lesson, you will explore the fundamentals of PivotTables. You will learn what they are, how to plan and create them, and how to navigate their interface. You will practice building PivotTables from an exercise scenario, layering and reordering fields, and then use GETPIVOTDATA to reference PivotTable values accurately in formulas.
Week Five
Lesson 09 - PivotTable Design and Formatting
Wednesday
This lesson focuses on customizing PivotTables for readability and presentation. You will control subtotals, grand totals, and math operations. You will then practice changing layouts, applying styles, and formatting values within PivotTables. Finally, you will learn how to copy, move, and manage PivotTables while understanding how the pivot cache works.
Lesson 10 - Calculated Fields, PivotTable Options, and Field Settings
Friday
In this lesson, you will expand PivotTable functionality with customization tools. You will create calculated fields to perform custom math inside a PivotTable. You will also explore PivotTable options, including naming, formatting, filtering, display, and printing settings. Finally, you will practice adjusting field settings and value field settings to refine results.
Week Six
Lesson 11 - Sorting, Grouping, and Filtering in PivotTables
Wednesday
This lesson explores ways to manage large PivotTables. You will practice sorting rows, columns, and values, including custom sort orders. You will also group data by categories or dates, and apply filters using labels, values, and report filters. To work more efficiently with dates, you will learn to control automatic grouping. The lesson concludes with hands-on practice using slicers and timelines to filter PivotTables interactively.
Lesson 12 - Creating PivotCharts
Friday
This final lesson shows how to visualize PivotTable data with PivotCharts. You will create PivotCharts, resize and move them, and apply filters directly within the chart. You will customize chart types, layouts, and formatting to suit your needs. The course concludes with working in the Data Model, where you will create PivotTables and PivotCharts from multiple sources to build rich, multi-dimensional reports.