Advanced Microsoft Excel 365

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.