Excel 365: Advanced Data Analysis

Description

Excel 365 recently introduced new features which are the biggest changes to Excel’s core functionality in decades. This hands-on course is an intensive session that will teach participants to use new features in Excel 365 to complete analytical work more efficiently and accurately. Participants will also learn how this new functionality can replace or supplement older methods of analysis in Excel. Assignments, handouts and examples will be used throughout the day.

Prerequisites

The course covers intermediate and advanced level Excel topics. As such, participants should have a good working knowledge of Excel prior to taking this course. Participants must have Excel 365 installed in order to access the features we will be practicing.

Time

4 hours

Learning Topics

Understand the Changes to Excel

Understand new “spilling” behaviors (Dynamic Arrays), and how they can be used in analysis
Learn about the limitations of “spilling” and potential errors it may create (#SPILL errors, etc.)
See the pitfalls of using these new functions across different versions of Excel
Learn how to use the Spilled Range Operator (#) and Intersect Operator (@) to change how Excel handles “spilled” ranges

New Data Analysis Functions

Use newly introduced Excel functions to leverage Dynamic Arrays to interpret and manipulate data
Employ Excel’s UNIQUE, FILTER, SORT and SORTBY functions to create dynamic, customizable subsets from a database
Create Dynamic Arrays of data using Excel’s SEQUENCE and RANDARRAY functions
Learn about new applications for legacy functions including OFFSET and TRANSPOSE

New Lookup Functions

Use Excel’s new lookup functions (XLOOKUP and XMATCH) to extract specific information from large datasets
Learn about the pros and cons of XLOOKUP vs. legacy lookup functions

Integrating Old Tools with New Functionality

Learn about how to integrate Dynamic Arrays with some legacy analysis tools including Tables (previously known as Lists) and Pivot Tables
Integrate Dynamic Arrays into charts to create powerful visuals that can update automatically