Advanced analytical features in Excel enable professionals to efficiently process, manipulate, and extract valuable insights from large datasets. PowerPivot extends traditional pivot table functionality by introducing a powerful data model that enables more sophisticated data summarization, cross-tabulation, and complex calculations far beyond standard spreadsheet capabilities. Power Query retrieves information from multiple data sources and enables data cleaning and preparation prior to integration into the data model. Power View creates dynamic, interactive dashboards that enable real-time data exploration. Power Map provides three-dimensional visualization capabilities for creating geographic map charts and interactive visual tours of spatial data. These integrated tools bring the capabilities of advanced business analytics within reach of Excel users at all technical levels.
Excel Version Compatibility:
PowerPivot, Power Query, Power View, and Power Map function as add-in programs for Excel 2010 and Excel 2013. In Excel 2016, these components were fully integrated into the core application. This course is delivered using the Excel 2016 software platform. While the fundamental concepts remain consistent across versions, minor interface differences between versions are explained during instruction.
Intended Student Profile:
This course serves individuals whose job responsibilities require creating complex Excel reports and performing advanced analytical work with business data.
Course Prerequisites:
Students should complete Excel Introduction or possess equivalent foundational skills. Previous exposure to pivot tables proves helpful but is not required for course success.
Lesson 1: Understanding Power Pivot and Power Business Intelligence Tools
- Introducing Power Pivot, Power Query, Power View, and Power Map
- Benefits and applications of Power BI Tools
- Comparative analysis between Excel and Power Pivot
- Demonstrating Pivot Table Examples with Excel Data
- Demonstrating Power Pivot Examples Using a Data Model
- Power View and Power Map Examples and Applications
Lesson 2: Working with Data
- Utilizing Excel Lists
- Using Vlookup Functions to Create Helper Columns
- Utilizing Excel Tables
- Cleaning and Preparing Table Data
- Working with Normalized Tables
Lesson 3: Importing Data into Power Pivot
- Understanding Acceptable Data Types and Formats
- Importing Excel Tables into Power Pivot
- Importing Access Database Tables
- Saving Files Properly
- Adding and Maintaining Data in Power Pivot
Lesson 4: Creating the Data Model
- Understanding Data Model Concepts and Applications
- Recognizing and Using Key Fields
- Creating Relationships between Tables in the Model
- Managing Relationship Configurations
- Creating and Using Linked Tables
- Creating and Using Data Hierarchies
Lesson 5: Using Calculations in Power Pivot
- Identifying Types of Calculations in Power Pivot
- Creating Calculated Columns
- Creating Calculated Fields (Measures)
- Implicit Calculated Field Development
- Explicit Calculated Field Development
- Understanding DAX Measure Rules and Industry Best Practices
- Choosing Between Calculated Columns and Measures
- Creating Key Performance Indicators (KPIs)
Lesson 6: Using Data Analysis Expressions
- Understanding Data Analysis Expression (DAX) Formulas
- Identifying Appropriate DAX Formula Applications
- Mastering DAX Syntax Fundamentals
- Creating DAX Formulas Effectively
- Identifying Operation Types Possible with DAX
Lesson 7: Working with DAX Formulas
- Utilizing Filter Functions
- Implementing Time Intelligence Functions
- Using Multiple Functions in Single Formulas
- Utilizing Multiple Data Tables
Lesson 8: Data Analysis with Pivot Tables and Pivot Charts
- Creating Pivot Tables
- Using Slicers to Filter Data
- Adding Visualizations to Pivot Tables
- Creating and Customizing Pivot Charts
- Formatting Pivot Chart Elements
- Using Multiple Charts and Tables Together
Lesson 9: Working with Power View
- Visualizing Data Using Power View
- Creating Fundamental Reports
- Making Enhancements with Power View
- Creating Tables and Matrix Visualizations
- Building Bar, Column, and Pie Charts
- Constructing Line and Scatter Charts
- Producing Geographic Map-Based Visualizations
Lesson 10: Building Interactive Reports with Power View
- Linking Visualizations in Power View
- Using Tiles to Organize and Present Data
- Filtering Groups and Report Views
- Exposing the Dashboard to Users
Lesson 11: Loading and Transforming Data with Power Query
- Importing Data from Multiple Sources
- Transforming, Cleansing, and Filtering Data
- Merging and Reshaping Data
- Grouping and Aggregating Data
- Inserting Calculated Columns
Lesson 12: Visualizing Data with Power Map
- Preparing Data for Geographic Mapping
- Creating Map-Based Visualizations
- Creating Heat Maps and Regional Maps
- Adding Multiple Visualization Layers to Maps
- Analyzing Data Changes Over Time
- Creating Interactive Tours of Geographic Data
