Advanced Microsoft Excel Training

Course Length: 1 day

Delivery Methods: Multiple delivery options

Course Overview

This Advanced Microsoft Excel class teaches you to perform important data management and analysis tasks.

You will learn to summarize your data using PivotTables, audit and analyze worksheet data, consolidate data from multiple sources, collaborate with others, automate repetitive tasks, and more.

By the end of this course, you will be ready to use Excel's most powerful tools to manage your data and gain actionable business insights.

Excel Version: This course primarily targets Excel 365 and Excel 2021; however, we draw attention to features that are not supported in earlier versions of Excel, and point out alternative options, 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.

Available Delivery Methods

Public Class
Public expert-led online training from the convenience of your home, office or anywhere with an internet connection. Guaranteed to run .

Private Class
Private classes are delivered for groups at your offices or a location of your choice.

Self-Paced
Learn at your own pace with 24/7 access to an On-Demand course.

Microsoft Certified Partner

Webucator is a Microsoft Certified Partner for Learning Solutions (CPLS). Our curriculum has been tested and approved by ProCert Labs, the official tester of Microsoft courseware, and has been found to meet the highest industry standards of instructional quality.

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)

Class Materials

Each student will receive a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this Microsoft Excel class:

Basic and Intermediate Excel experience.

Prerequisite Courses

Courses that can help you meet these prerequisites:

Advanced Microsoft Excel Training

Register for a Live Class

Request a Private Class

Self-Paced Course