Advanced Microsoft Excel 365

In this course, you will transform Excel into a functional database and master advanced data management techniques. Key learning components include how to sort and filter data using AutoFilter, slicers, and advanced criteria, as well as how to utilize Power Pivot for comprehensive analyses.

Details
Syllabus
Requirements
Instructor

Enrollment Options

$163.00

Select Instruction Type:

Self-Guided

3 Months Access

No Instructor. Course begins when you enroll.

Instructor-Moderated

6 Weeks Access

Study online with Instructor moderated discussion boards.

Select Course Start Date:

October 15, 2025

November 12, 2025

December 17, 2025

January 14, 2026

Enroll Now

$163.00

Self-Guided

Enroll Now

Details

This Microsoft Excel course covers advanced Excel skills utilizing analytical tools, including the Analysis ToolPak, forecast sheets, and various statistical methods alongside Goal Seek, Solver, and What-If Analysis tools for scenario-based analysis. Data integrity is emphasized through data validation rules, error-checking formulas, conditional formatting, and version control. You will even gain skills in automating tasks with macros and VBA.

PivotTables and PivotCharts are also explored in depth, from basic creation to advanced customization and multi-source analysis. By the end of this Microsoft Excel training program, you will be proficient in Excel's advanced features, ready to apply them to real-world scenarios.

Syllabus

Lesson 1 - Looking Up Data in Arrays

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 2 - Creating Simple Excel Databases

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.

Lesson 3 - Managing Data with Power Query and the Data Model

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 4 - Analyzing Data

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.

Lesson 5 - Ensuring Data Integrity

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 6 - Conditional Formatting

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.

Lesson 7 - Creating and Using Macros

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 8 - PivotTable Concepts

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.

Lesson 9 - PivotTable Design and Formatting

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

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.

Lesson 11 - Sorting, Grouping, and Filtering in PivotTables

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

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.

Requirements

Prerequisites:

There are no pre-requisites to complete this course, but the learner should have a basic understanding of Microsoft Excel's uses and functions or completion of Introduction to Microsoft Excel & Intermediate Microsoft Excel.

Requirements

Hardware Requirements:

This course can be taken on either a PC or Mac.

Software Requirements:

PC: Windows 10 or later
Mac: OS X Snow Leopard 10.6 or later
Browser: The latest version of Firefox, Chrome, or Safari browsers (although Firefox or Chrome is preferred)
Microsoft Excel 365 for Windows or Microsoft Excel for Mac. Excel 365 Online can be used to study the features it includes, but you will not be able to practice certain skills. For the best experience, use Excel 365 for Windows or Word for Mac to complete this course. If that software isn’t available, some exercises can be completed using Excel 365 Online in a web browser on Windows, MacOS, or ChromeOS.
Adobe Acrobat Reader.
Other: Email capabilities and access to a personal email account
Software must be installed and fully operational before the course begins

Instructional Material Requirements:

The instructional materials required for this course are included in enrollment and will be available online.

Instructor

Chad Wambolt

Chad Wambolt has spent his entire professional career in the finance field, working for both private and publicly-held companies with sales ranging from $500 million to $3 billion. He is a graduate of Boise State University, where he obtained his bachelor's degree in accounting. Through the course of his career, Wambolt became an expert user of Microsoft Excel. Since 1997, he has taught Excel to students of varying skill levels and helped organizations streamline internal processes.