919 878 7100
Course Details

Microsoft® Excel: Data Analysis With Power Pivot

  • 1/2 day
  • a.m. or p.m. sessions

Course Description:

This advanced hands-on course is designed for experienced Excel users who want to expand their analytical capabilities beyond traditional PivotTables and flat-file reporting. Participants will learn how to use Microsoft Power Pivot to build relational data models, connect multiple data sources, create advanced calculations using DAX (Data Analysis Expressions), and develop interactive business reports that support deeper analysis and decision-making.

Traditional Excel analysis is often limited by the structure of worksheet-based data. Power Pivot transforms Excel into a powerful business intelligence and data modeling platform by allowing users to create relationships between multiple tables, build scalable data models, and perform high-performance calculations across large datasets.

Through practical exercises and real-world business scenarios, participants will learn how to create robust PivotTable reports from relational data models, develop calculated columns and measures, use DAX functions for advanced analytics, and build Key Performance Indicators (KPIs) for executive-level reporting.

This course is ideal for business analysts, financial professionals, accountants, operations teams, project managers, and Excel power users who need to work with large or complex datasets and create professional business intelligence solutions within Microsoft Excel.

Cancellation Policy: Participants must cancel at least two weeks (ten working days) prior to class or tuition will be forfeited.

IMPORTANT INFORMATION ABOUT PARTICIPATION:  As part of our sponsorship with NASBA, we have agreed to provide at least three (3) instances of engagement per CPE hour (50 minutes).  These may come in the form of open-ended questions, hand-raising, polling, and other techniques.  You MUST answer or respond to all three opportunities to engage in order to receive credit for that CPE hour.   Knowledge Source Inc. retains these responses after all CPE classes for audit purposes.

Knowledge Source Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

Course Objectives:

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

  • Understand the role of PowerPivot within Microsoft Excel’s data analysis ecosystem
  • Differentiate between traditional Excel reporting and relational data modeling
  • Build and manage data models using multiple related tables
  • Create and manage relationships between data tables
  • Import and integrate data from multiple data sources
  • Create PivotTables and Pivot Charts from Power Pivot data models
  • Develop calculated columns and reusable measures
  • Use DAX (Data Analysis Expressions) functions to perform advanced calculations
  • Apply time intelligence functions for trend and period analysis
  • Create Key Performance Indicators (KPIs) for business reporting
  • Design interactive reports and dashboards using slicers and Pivot Charts
  • Improve reporting performance and scalability when working with large datasets
  • Apply Power Pivot best practices for maintainable and efficient business models

Module 1 – Introduction to Power Pivot

  • Overview of Power Pivot capabilities
  • Understanding Excel’s Data Model
  • Traditional PivotTables versus Power Pivot
  • Benefits of relational data modeling
  • Understanding business intelligence concepts in Excel

Module 2 – Relational Database Fundamentals

  • Understanding relational databases
  • Fact tables versus dimension tables
  • Data normalization concepts
  • Primary keys and foreign keys
  • Reviewing relational data diagrams
  • Preparing data for modeling

Module 3 – Building a Data Model

  • Importing data into Power Pivot
  • Creating relationships between tables
  • Managing relationships in the Data Model
  • Working with multiple related data sources
  • Best practices for data model design
  • Troubleshooting relationship issues

Module 4 – Creating PivotTables from the Data Model

  • Building PivotTables using Power Pivot data
  • Using multiple related tables in reports
  • Filtering and slicing relational data
  • Creating PivotCharts from the Data Model
  • Designing interactive reports

Module 5 – Working with Calculations

  • Understanding calculated columns
  • Creating calculated fields
  • Creating and managing measures
  • Differences between calculated columns and measures
  • Best practices for reusable calculations
  • Formatting calculations for reporting

Module 6 – Introduction to DAX (Data Analysis Expressions)

  • Understanding DAX syntax and structure
  • Creating basic DAX calculations
  • Aggregation functions in DAX
  • Logical functions in DAX
  • Text and date functions in DAX
  • Using DAX in PivotTable reports

Module 7 – Advanced DAX & Time Intelligence

  • Understanding filter context and row context
  • Using CALCULATE and FILTER functions
  • Creating year-over-year comparisons
  • Using time intelligence functions
  • Building running totals and period calculations
  • Advanced business calculation scenarios

Module 8 – Key Performance Indicators (KPIs)

  • Understanding KPI concepts
  • Creating KPI measures
  • Defining targets and thresholds
  • Visualizing KPI performance
  • Using KPI indicators in reports
  • Designing executive summary views

Module 9 – Advanced Reporting & Visualization

  • Creating interactive PivotCharts
  • Using slicers and timelines
  • Designing dashboard-style reports
  • Combining PivotTables and charts
  • Improving report usability and presentation
  • Best practices for executive reporting

Module 10 – Power Pivot Best Practices

  • Managing large datasets efficiently
  • Optimizing Data Model performance
  • Organizing measures and calculations
  • Maintaining scalable reporting solutions
  • Troubleshooting common Power Pivot issues
  • Power Pivot workflow and governance considerations

Cancellation Policy: 5 working days for full refund. Cancellations after that time are charged full tuition for the course.

For more information regarding refund, concerns, and/or program cancellation policies please contact our offices at 919-878-7100 ext. 22

Course Information

Course Level: 
Credit: CPE 4 hours
Fee: $225Klarna
Length: 1/2 day
Hours: a.m. or p.m. sessions
Delivery: Virtual Instructor-Led or Onsite
Register

Related Courses

View All