Complete Excel for Data Analytics

The Excel Data Analytics Series is a Series of Excel courses that together form the concepts and practical know how on how to use Excel features for data analysis. This course is designed to be taught either continuously or independently based on learning requirements, in order to give practicing or people beginning in Data Analysis careers the tools they need for the work. This is subdivided into three parts, with each part having lectures, demos and exercises done in 1 training day (7 hours).

By attending this course, you will learn how to build reports and analyses that were previously difficult or impossible to do in Excel.

• Build repeatable data cleaning processes for Excel with Power Query.

• Create relational data models and analysis measures with Power Pivot.

• Pull data quickly with dynamic array functions.

• Use AI to uncover patterns and trends from inside Excel.

• Integrate Python functionality with Excel for automated analysis and reporting.

The three parts consist of Data Cleaning and Transformation with Power Query for Day 1, Data Modeling and Analysis with Power Pivot for Day 2 and lastly the Excel Data Analytics Toolkit for Day 3.

Target Audience

Data Analysts, Business Owners, People who seek to grow their Excel

knowledge for data analysis

Duration

3 days

Day 1: Data Cleaning and Transformation with Power Query

Excel Tables

• Creating and Referring to Table Headers

• Viewing the Table Footers

• Naming Excel Tables

• Formatting Excel Tables

• Updating Table Ranges

• Organizing Data for Analytics

Power Query

• What Is Power Query?

• Power Query as Excel’s ETL Tool

• A Tour of the Power Query

• The Imported Data

• Data Profiling in Power Query

• Exploring the Data Preview Options

• Overriding the Thousand-Row Limit

• Closing Out of Data

Transforming Rows in Power Query

• Removing the Missing Values

• Refreshing the Query

• Splitting Data into Rows

• Filling in Headers and Cell Values

• Replacing Column Headers

• Filling Down Blank

Transforming Columns in Power Query

• Changing Column Case

• Delimiting by Column

• Changing Data Types

• Deleting Columns

• Working with Dates

• Creating Custom Columns

• Loading and Inspecting the Data

• Calculated Columns Versus Measures

• Reshaping Data

Merging and Appending Data in Power Query

• Appending Multiple Sources

• Connecting to External Excel Workbooks

• Appending the Queries

• Understanding Relational Joins

• Left Outer Join: Think VLOOKUP()

• Inner Join: Only the Matches

• Managing Your Queries

• Grouping Your Queries

• Viewing Query Dependencies

Day 2: Data Modeling and Analysis with Power Pivot

Power Pivot

• What Is Power Pivot?

• Why Power Pivot?

• Power Pivot and the Data Model

• Loading the Power Pivot Add-in

• A Brief Tour of the Power Pivot Add-In

• Data Model

• Calculations

• Tables

Creating Relational Models in Power Pivot

• Connecting Data to Power Pivot

• Creating Relationships

• Identifying Fact and Dimension Tables

• Arranging the Diagram View

• Editing the Relationships

• Loading the Results to Excel

• Understanding Cardinality

• One-to-One Cardinality

• One-to-Many Relationships

• Many-to-Many Relationships

• Why Does Cardinality Matter?

• Understanding Filter Direction

• Filtering orders with users

• Filtering users with orders

• Filter Direction and Cardinality

• From Design to Practice in Power Pivot

• Creating Columns in Power Pivot

• Calculating in Power Query Versus Power Pivot

• Example: Calculating Profit Margin

• Recoding Column Values with SWITCH()

• Creating and Managing Hierarchies

• Creating a Hierarchy in Power Pivot

• Using Hierarchies in the PivotTable

Creating Measures and KPIs in Power Pivot

• Creating DAX Measures

• Creating Implicit Measures

• Creating Explicit Measures

• Creating KPIs

• Adjusting Icon Styles

• Adding the KPI to the PivotTable

Intermediate DAX for Power Pivot

• CALCULATE() and the Importance of Filter Context

• CALCULATE() with One Criterion

• CALCULATE() with Multiple Criteria

• AND Conditions

• OR Conditions

• CALCULATE() with ALL()

• Time Intelligence Functions

• Adding a Calendar Table

• Creating Basic Time Intelligence Measures

Day 3: The Excel Data Analytics Toolkit

Introducing Dynamic Array Functions

• Dynamic Array Functions Explained

• What Is an Array in Excel?

• Array References

• Array Formulas

• An Overview of Dynamic Array Functions

• Finding Distinct and Unique Values with UNIQUE()

• Finding Unique Versus Distinct Values

• Using the Spill Operator

• Filtering Records with FILTER()

• Adding a Header Column

• Filtering by Multiple Criteria

• Sorting Records with SORTBY()

• Sorting by Multiple Criteria

• Sorting by Another Column Without Printing It

• Creating Modern Lookups with XLOOKUP()

• XLOOKUP() Versus VLOOKUP()

• A Basic XLOOKUP()

• XLOOKUP() and Error Handling

• XLOOKUP() and Looking Up to the Left

• Other Dynamic Array Functions

• Dynamic Arrays and Modern Excel

Augmented Analytics and the Future of Excel

• The Growing Complexity of Data and Analytics

• Excel and the Legacy of Self-Service BI

• Excel for Augmented Analytics

• Using Analyze Data for AI Powered Insights

• Building Statistical Models with XLMiner

• Reading Data from an Image

• Sentiment Analysis with Azure Machine Learning

Python with Excel

• The Role of Python in Excel

• A Growing Stack Requires Glue

• Network Effects Mean Faster Development Time

• Bring Modern Development to Excel

• Using Python and Excel Together with pandas and openpyxl

• Other Python Packages for Excel

• Demonstration of Excel Automation with pandas and openpyxl

• Cleaning Up the Data in pandas

• Summarizing Findings with openpyxl

• Adding a Styled Data Source