Excel Data Analysis & Reporting – Part 1 focuses on preparing and transforming data for analysis. You’ll learn how to clean, split, and combine data using Excel’s built-in tools and Power Query. This course is ideal for anyone who wants to make their data easier to work with and report on.

Designed for analysts, managers, and anyone who works with business data. If you prepare reports or work with large datasets, this course is for you.

Knowledge equivalent to Excel Timesaving Features – Part 2, including:

• Using formulas and absolute references
• Common functions like AVERAGE, MAX, COUNT, IF
• Creating and using named ranges

By the end of this course, you’ll be able to:

• Clean and prepare data for analysis
• Combine and transform data from multiple sources
• Use Power Query to automate data preparation

Module 1: Source Data: Ranges vs Tables

• The difference between a Range and a proper Excel Table
• The benefits of Tables when writing calculations and functions.
• The impact of using a proper Table as your source in Data Analysis.

Module 2: Lookup Functions

• The basics of the most common used and infamous Lookup Function called VLOOKUP.
• The common reason why VLOOKUP functions fail.
• Learning a NEW Lookup function called XLOOKUP with solves many of the challenges with the VLOOKUP.

Module 3: Text Functions and Flash Fill

• Performing functions needed commonly needed to clean data including concatenating and splitting text.
• Flash Fill – A modern tool that uses pattern recognition to make copying, combining, and cleaning data more efficient.

Module 4: Transforming data in Power Query

• Understanding how to connect to and query data sources.
• Creating a recipe of applied steps that will transform and clean a piece of data each time if is refreshed.