Course Information

As you build your knowledge of modern Excel functions and features, understanding Excel tables and their structure is essential. This course begins with a strong foundation in tables, then progresses to building and using PivotTables.

For those who regularly clean and prepare data, Power Query can be a game-changer. Many users are unaware of its capabilities, and learning it independently can be challenging. This course focuses on using Power Query in Excel to efficiently transform your data.

Power Query is an ETL tool—Extract, Transform, and Load—that allows you to automate data preparation. Once a query is created, it can be reused and refreshed, saving time and improving efficiency.

The course also introduces the Power Pivot and how to navigate the Data Model. You’ll learn how to load data into the model, create relationships, build basic measures, and generate PivotTables from multiple datasets

Objectives

• Upon completion of this course, participants will be able to:

• Understand the structure and importance of Excel Tables as the foundation for advanced data analysis.

• Create and use PivotTables to summarize and analyze data effectively.

• Use Power Query to connect to, import, and clean data from various sources.

• Utilize Power Pivot to combine multiple datasets and build PivotTables.

Outline

REVIEW EXCEL TABLES

• Best Practices for Data Set Up

• Table Features

• Calculated Columns/Structured References

• Table Structured Reference Syntax

• Absolute Structured References in Table Formulas

PIVOTTABLES

• Create PivotTables

• Learn to Refresh and Modify PivotTables

• Work with Slicers and Understand How Slicers can help with Dashboards

• Value Field Setting, Formatting and other Options

• Work with PivotTable Timelines

INTRODUCTION TO POWER PIVOT

• What is Power Pivot

• Importing Tables into the Data Model

• Linking Tables

• Using the Related() Function

• Basic Calculations in the Data Model

• Creating a PivotTable using Multiple Data Sheets

POWER QUERY aka GET AND TRANSFORM

• What is Power Query

• Types of Data Connections

• Power Query Editor Window

• Review and Change Data Types

• Close and Load Options

• Data Specific Editing Tools such as Text, Numbers, and Date Tools

• Filling Data Up and Down

• Splitting and Combining Columns of Data

• Adding Conditional Columns

• Using Formulas such as IF and AND

• Basic Understanding of M Functions like Text.PadStart

• Unpivoting Data

• Merging Data and working with Joins

• Importing data from websites

• Importing data from pictures and the snipping tool

UNDERSTANDING POWER PIVOT

• What is the Data Model

• How to load information into the Data Model

• Create Relationships

• Learn to Create Measures

• Creating a Measure using AutoSum

• Working with the Measures Dialog Box

• Understanding DAX Syntax

• DAX Operators

• DAX Functions such as COUNT ROWS, COUNT DISTINCT AND COUNTA

• Logical DAX Functions like IF, OR, AND

Intermediate Advanced Excel Features Beneficial for Auditors & Accountants or an equivalent course.