Silhouette of a hand reaching towards computer screen with dot pattern.

Introduction to Data Preparation and Modelling in Excel

Analyze data more efficiently by introducing yourself to the main concepts of data preparation and modelling in Excel for Microsoft 365.

Before modelling data in Excel, we need to do some data preparation. This helps us analyze our financial data more efficiently, reduces errors and enables us to write simpler formulas for calculations. Data modelling can be described as the process of analyzing and defining the different data sources and the relationships between those data sources using visual representations.

In this introductory course, you will be introduced to the main concepts of data preparation and modelling, and the different elements and stages of data modelling in Excel, from importing the data to creating the data model to ultimately creating reports.

This online, video-led course is performed using Excel for Microsoft 365 and includes an eBook and practical examples that are yours to keep.

Note: This course can be taken individually or as part of the Comprehensive Excel Certificate Program.

This is an introductory course, to take a deeper dive into data preparation and modelling, see our Data Preparation: Cleansing, Modelling and Dashboards course.

Data modelling in Excel and the Data Analysis Expressions (DAX) language
How to import data using Power Query in Excel
Sample data transformations in Power Query and the M language for writing code in Power Query
Modelling different tables in Excel
Types of tables (Fact vs. Dimension tables)
Types of relationships in PowerPivot
Measures and calculated columns and implicit and explicit measures
Evaluation contexts in DAX (Filter vs. Row context)
RELATED() and RELATEDTABLE() functions
Using the FILTER() function to filter tables in DAX
ALL() function and the difference between ALL() and ALLSELECTED() functions
CALCULATE() function