Excel Copilot: Working with Formulas and Functions

Course details

In this course, George Mount—an analytics author, trainer, and Excel MVP—shows you how to navigate the complexities of Excel calculations. Learn how to design structured tables and execute precise calculations using guidance from Copilot. Dive into dynamic data analysis with powerful lookup functions and automated aggregations, ensuring data-driven insights in real-time. Discover methods for cleaning and standardizing text data, making your datasets accurate and analysis-ready. Gain skills in managing arrays and debugging and turn intricate formulas into comprehensible, maintainable parts. Also, find out how to transform your data workflow by utilizing Copilot for seamless spreadsheet operations. This course targets intermediate Excel users who want to leverage AI tools for enhanced productivity. Whether for business, education, or personal development, this course empowers you to optimize Excel's capabilities, boosting your efficiency and analytical prowess.

Learning objectives
Identify the steps to set up and structure an Excel table for optimal use with Copilot.
Use Copilot to interpret and simplify complex Excel formulas.
Prompt Copilot to execute lookup functions to retrieve data from table-based sources.
Describe the steps involved in cleaning and standardizing text data using Copilot.
Identify and fix formula Excel formula errors using Copilot.

Concepts

0. Introduction

01 - Mastering Excel formulas and functions with Copilot
02 - What to know before starting this course

1. Building Formulas for Excel Tables

03 - Setting up and structuring a table for Copilot
04 - Writing basic column formulas with Copilot
05 - Challenge - Creating a pricing calculation with Copilot
06 - Solution - Creating a pricing calculation with Copilot

2. Analyzing and Summarizing Data

07 - Using lookup functions for table-based data
08 - Summarizing data with conditional aggregations
09 - Challenge - Creating a performance summary table
10 - Solution - Creating a performance summary table

3. Automating Data Cleaning and Text Formatting

11 - Cleaning and standardizing text data
12 - Extracting and combining data from multiple columns
13 - Challenge - Cleaning up a customer data table
14 - Solution - Cleaning up a customer data table

4. Advanced Formula Applications

15 - Analyzing and summarizing data with dynamic array functions
16 - Working with date functions
17 - Challenge - Analyzing sales campaign data
18 - Solution - Analyzing sales campaign data

5. Debugging and Optimizing Formulas

19 - Using Copilot to explain and break down complex formulas
20 - Identifying and fixing formula errors in Copilot
21 - Challenge - Debugging a customer review score with Copilot
22 - Solution - Debugging a customer review score with Copilot

Conclusion

23 - The future of AI in Excel formulas and functions