Microsoft Private Public On-Demand

Advanced Microsoft Excel Training (EXC365.3)

1 day

Microsoft Excel Microsoft Office Microsoft

Build advanced Excel 365 skills with PivotTables, complex functions, workbook auditing, data tools, collaboration features, and macro automation techniques.

Talk to a Learning Consultant Register for a Public Class Request a Private Class

Register or Request Training

Course Overview

Develop advanced Microsoft Excel skills for analyzing, managing, auditing, and sharing data. Learn to build PivotTables and PivotCharts, use dynamic array and lookup functions, validate and consolidate data, perform What-If Analysis, and inspect complex workbooks.

You will also work with external data, workbook protection and collaboration tools, and recorded macros that automate repetitive tasks. The course primarily covers Excel 365 and Excel 2021, with guidance on compatibility and alternatives for earlier versions when available.

Course Benefits

Create PivotTables and charts to quickly summarize large amounts of data.
Work with some of Excel's more advanced functions.
Learn to trace precedents and dependents to learn about the data connected to your active cell.
Convert blocks of text into columns.
Leverage data validation tools to control the data entered into your worksheet.
Consolidate data from various sources into one master worksheet for easy summary and review.
Protect your worksheets and workbooks for safe and secure collaboration.
Work with other applications by importing and exporting data, charts, and files.

Delivery Methods

Public Class
Live expert-led online training from anywhere. Guaranteed to run .

Private Class
Delivered for your team at your site or online.

On-Demand
Learn at your own pace with 24/7 access.

Microsoft Certified Partner

Webucator is a Microsoft Certified Partner. We design and deliver training that aligns directly with Microsoft's official technologies, standards, and learning objectives.

Course Outline

Using PivotTables
1. How PivotTables Work
2. Timeline Filters
3. Inserting Slicers
4. Grouping Data
5. Calculated Fields
6. PivotCharts
7. Working with PivotTables (exercise)
Advanced Functions
1. Function Syntax
2. ROWS, COLUMNS, INDEX, and XMATCH
3. Arrays and Array Formulas
4. Getting Unique Values (exercise)
5. SORT, FILTER, and SORTBY
6. Lookup Functions
7. Using the XLOOKUP Function (exercise)
8. The LET Function
9. The TRANSPOSE Function
Auditing Workbooks
1. Inspecting a Workbook
2. Tracing Precedents and Dependents
3. Tracing Precedents and Dependents Practice (exercise)
4. Watch Window
5. Evaluating Formulas
6. Error Checking
Data Tools
1. Importing Data from online source
2. Converting Text to Columns
3. Converting Text to Columns (exercise)
4. Importing Files
5. Importing Text Files (exercise)
6. Linking to External Data
7. Controlling Calculation Options
8. Data Validation
9. Using Data Validation (exercise)
10. Consolidating Data
11. Consolidating Data (exercise)
12. What-If Analysis
13. Using Goal Seek (exercise)
Working with Others
1. Comments and Notes
2. Protecting Worksheets and Workbooks
3. Password Protecting a Workbook (exercise)
4. Marking a Workbook as Final
5. Other Sharing Concerns
Recording and Using Macros
1. Recording Macros
2. Recording a Macro (exercise)
3. Running Macros
4. Editing Macros
5. Adding Macros to the Quick Access Toolbar
6. Adding a Macro to the Quick Access Toolbar (exercise)