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.

6 Weeks / 24 Course Hrs

Currently Enrolling

Offered in partnership with your preferred school

ed2go

Change School

Learning method

Self-Guided

$227

No instructor. Study on your own schedule

Enroll Now

Instructor-Moderated

$212

Confirm your start date in cart

Starting October 15 | November 12

Details + Objectives

Course code: ax5

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.

What you will learn

Master advanced data management techniques, including importing, sorting, and filtering data
Utilize Power Query and the Data Model for data transformation and consolidation
Perform comprehensive data analysis using Power Pivot and Excel’s analytical tools
Ensure data integrity through validation rules, error-checking formulas, and version control
Automate tasks with macros and VBA for increased efficiency
Create and customize PivotTables and PivotCharts for advanced analysis and reporting

How you will benefit

By mastering data management and automation techniques, you will save time and reduce the effort needed for repetitive tasks
Proficiency in Power Query, Power Pivot, and advanced Excel tools will enable you to conduct thorough data analysis, leading to better decision-making
Learning to ensure data integrity with validation rules and error-checking formulas will result in more reliable and precise data analyses
New skills in creating and customizing PivotTables and PivotCharts will allow you to present data in a clear and impactful manner, improving communication of insights

How the course is taught

Instructor-Moderated or Self-Guided online course
6 Weeks or 3 Months access
24 course hours

Outline

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Instructors & Support

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.

Instructor Interaction: The instructor looks forward to interacting with learners in the online moderated discussion area to share their expertise and answer any questions you may have on the course content.

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.

FAQ

Instructor-Moderated: A new session of each course begins each month. Please refer to the session start dates for scheduling.

Self-Guided: Your course begins immediately after you enroll.

Instructor-Moderated: Once a course session starts, two lessons will be released each week for the 6 week duration of your course. You will have access to all previously released lessons until the course ends. You will interact with the instructor through the online discussion area. There are no live sessions or online meetings with the instructor.

Self-Guided: You have 3 months of access to the course. After enrolling, you can learn and complete the course at your own pace, within the allotted access period. You will have the opportunity to interact with other students in the online discussion area.

Instructor-Moderated: The interactive discussion area for each lesson automatically closes two weeks after each lesson is released, so you're encouraged to complete each lesson within two weeks of its release. However, you will have access to all lessons from the time they are released until the course ends.

Self-Guided: There is no time limit to complete each lesson, other than completing all lessons within the allotted access period. Discussion areas for each lesson are open for the entire duration of the course.

Instructor-Moderated: Students enrolled in a six-week online class benefit from a one-time, 10-day extension for each course. No further extensions can be provided beyond these 10 days.

Self-Guided: Because this course is self-guided, no extensions will be granted after the start of your enrollment.

Advanced Microsoft Excel 365

ed2go