Advanced Microsoft Excel Training (EXC365.3)

This advanced Excel course is designed for both individuals and teams aiming to upgrade their Excel capabilities, covering diverse topics from dynamic data visualization to complex functions and collaborative tools.

We begin with Using PivotTables, an essential feature for sophisticated data analysis. You will learn how PivotTables work, insert slicers and timeline filters for better data manipulation, group data, and create calculated fields. You will also explore PivotCharts and practice these concepts through hands-on exercises to solidify your understanding of working with PivotTables.

Next, we move on to Advanced Functions, where we break down complex function syntax. You will gain insight into essential functions such as ROWS, COLUMNS, INDEX, and XMATCH, along with learning about array formulas and methods to get unique values. The lesson includes practical exercises on using SORT, FILTER, and SORTBY functions, exploring lookup functions like XLOOKUP, and diving into the versatile LET and TRANSPOSE functions.

In Auditing Workbooks, we cover important techniques for maintaining the integrity of your Excel workbooks. Topics include inspecting a workbook, tracing precedents and dependents, utilizing the Watch Window, and evaluating formulas. You’ll also learn about error checking through practical exercises designed to give you robust auditing skills.

The Data Tools lesson is designed to enhance your data manipulation capabilities. You’ll discover how to import data from online sources, convert text to columns, and efficiently link to external data. This module also covers controlling calculation options, data validation, and consolidating data, along with practical exercises on What-If Analysis and using Goal Seek.

The Working with Others section focuses on collaborative features in Excel. You’ll learn to add comments and notes, protect worksheets and workbooks, and password-protect your files. Additionally, the module covers how to mark a workbook as final and addresses other sharing concerns to ensure smooth teamwork.

In Recording and Using Macros, we introduce you to automating repetitive tasks. You’ll learn to record and run macros, edit previously recorded macros, and add them to the Quick Access Toolbar for enhanced efficiency. This lesson includes hands-on exercises for recording and adding macros, helping you streamline your workflow.

By the end of this course, you will possess a strong command over advanced Excel functionalities, enabling you to analyze data more effectively, automate routine tasks, and collaborate seamlessly with your team. This training will prepare you to handle complex Excel tasks and elevate your organization's data management prowess.

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.

1. 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)
2. 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
3. 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
4. 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)
5. 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
6. 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)

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

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