Course outline for
Microsoft 365 Excel: Power Pivot

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1: Getting Started with Power Pivot

TOPIC A: Enable and Navigate Power Pivot

The Power Pivot Add-in

The Power Pivot Window

The Power Pivot Ribbon Interface

Power Pivot Worksheets

Activity 1-1: Exploring Power Pivot

TOPIC B: Import Data from Various Sources

Power Pivot Data Sources

Data Feeds

The Table Import Wizard

Data Types

How to Import Data From Various Data Sources

Activity 1-2: Importing Data from an Access Database

TOPIC C: Refresh Data from a Data Source

Data Refresh

Connections

Refresh Data from a Data Source

Activity 1-3: Refreshing Data from an Access Database

TOPIC D: Create Linked Tables

Linked Tables

How to Create Linked Tables

Activity 1-4: Creating Linked Tables

Summary

Review Questions

Lesson 2: Manipulating Power Pivot Data

TOPIC A: Managing Tables

Power Pivot Tables

How to Organize and Format Tables

How to Manipulate Columns in a Table

Activity 2-1: Managing Tables

TOPIC B: Creating Calculated Columns

Calculated Columns

Power Pivot Formulas

How to Create Calculated Columns

Activity 2-2: Creating Calculated Columns

TOPIC C: Sort and Filter Power Pivot Data

Sort Options

Filter Options

How to Sort and Filter Power Pivot Data

Activity 2-3: Sorting and Filtering Power Pivot Data

TOPIC D: Create and Manage Table Relationships

Table Relationships

Types of Relationships

The Create Relationship Dialog Box

The Manage Relationships Dialog Box

The Diagram View

How to Create and Manage Table Relationships

Activity 2-4: Creating and Managing Table Relationships

Summary

Review Questions

Lesson 3: Visualizing Power Pivot Data

TOPIC A: Create PivotTables

PivotTables

Flattened PivotTables

Create a Pivot Table from Power Pivot

The PivotTable Fields Pane

How to Create a PivotTable

Activity 3-1: Creating a PivotTable

TOPIC B: Create PivotCharts

PivotCharts

PivotChart Types

How to Create PivotCharts

Activity 3-2: Creating PivotCharts

TOPIC C: Create Key Performance Indicators (KPIs)

Create Measures

Add Measures to a PivotTable

Create KPIs

Manage KPIs

Delete KPIs

Activity 3-3: Create KPIs

TOPIC D: Filter Data Using Slicers

Slicers

How to Filter Data Using Slicers

Activity 3-4: Creating Slicers

TOPIC E: Create Sparklines

Sparklines

Types of Sparklines

The Sparkline Contextual Tab

How to Create and Customize Sparklines

Activity 3-5: Create Sparklines

TOPIC F: Apply Conditional Formatting

Apply Pre-Defined Conditional Formats

Types of Conditional Formats

Create Custom Conditional Formats

How to Use Conditional Formatting

Activity 3-6: Apply Conditional Formatting

Summary

Review Questions

Lesson 4: Using DAX Functions In Power Pivot

TOPIC A: Manipulate Power Pivot Data Using DAX Functions

Measures

DAX Functions

DAX Variables

Aggregate Functions

How to Manipulate Power Pivot Data Using DAX Functions

Activity 4-1: Manipulating Power Pivot Data Using DAX Functions

TOPIC B: Extract Data from Tables Using Functions

Filter Functions

How to Extract Data from Tables Using Functions

Activity 4-2: Extracting Data from Tables

TOPIC C: Work with Time Dependent Data

Time Intelligence Functions

Create a Date Table

How to Work with Time Dependent Data

Activity 4-3: Working with Time Dependent Data

Summary

Review Questions

Lesson 5: Distributing Power Pivot Data

TOPIC A: Protect Data

Workbook Protection

Worksheet Protection

How to Protect Data

Activity 5-1: Protecting Workbooks and Worksheets

TOPIC B: Saving Data in Different File Formats

File Formats

How to Save Data in Different File Formats

Activity 5-2: Saving Data in Different File Formats

Summary

Review Questions

Lesson Labs

Lesson 1

Lesson Lab 1-1

Lesson Lab 1-2

Lesson 2

Lesson Lab 2-1

Lesson Lab 2-2

Lesson Lab 2-3

Lesson 3

Lesson Lab 3-1

Lesson Lab 3-2

Lesson Lab 3-3

Lesson 4

Lesson Lab 4-1

Lesson 5

Lesson Lab 5-1

Course Wrap-Up

Post-Course Assessment

Course Summary

Appendices

Keyboard Shortcut Quick Reference Sheet

Glossary