Excel Advanced Data Analysis with Pivot Tables and PowerPivot

Audience:

This course is ideal for experienced Excel users looking to further their expertise in data analysis by utilizing advanced PivotTable features and PowerPivot.

Prerequisites:

Participants should be comfortable using Excel and have experience creating basic PivotTables.

Description:

This full-day course will enhance your data analysis skills using advanced Excel features, including PivotTables and Power Pivot. You will learn how to organize, manipulate, and present large datasets for data-driven decision-making. The course is split into two parts: AM Section: Advanced use of PivotTables and PivotCharts. PM Section: Power Pivot for managing and analyzing large datasets

Outline:

Lesson 1: Preparing Data and Creating PivotTables

• Topic A: Prepare Data for PivotTable Reporting
• Topic B: Create PivotTables from Local Data Sources
• Topic C: Create PivotTables from Multiple Local Data Sources
• Topic D: Create PivotTables from External Data Sources

Lesson 2: Analyzing Data by Using PivotTables

• Topic A: Summarize PivotTable Data
• Topic B: Organize and Filter PivotTable Data
• Topic C: Format PivotTables
• Topic D: Refresh and Modify PivotTable Data

Lesson 3: Working with PivotCharts

• Topic A: Create and Format PivotCharts
• Topic B: Manipulate PivotChart Data
• Topic C: Use PivotCharts for Data Visualization

Lesson 4: Getting Started with PowerPivot

• Topic A: Enable and Navigate PowerPivot
• Topic B: Manage Data Relationships within Power Pivot

Lesson 5: Visualizing Power Pivot Data

• Topic A: Create Power Pivot Reports
• Topic B: Use Calculations in PowerPivot for Enhanced Data Insights

Lesson 6: Working with Advanced Functionality in Power Pivot

• Topic A: Create and Analyze KPIs (Key Performance Indicators)
• Topic B: Work with Dates and Time in PowerPivot for Advanced Analysis

Appendix A: Overview of Commonly Used DAX (Data Analysis Expressions) Functions