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.

Details
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
Download a one-page course summary
Download Now