Building BI with Pivot Tables


  • How to develop Excel Pivot Table reports and modify their aesthetics
  • How to leverage tools like Power Query and Power Pivot to enhance what’s possible with Pivot Tables
  • How to refresh Pivot Table local and external data
This course includes:

Course Description

Building Business Intelligence with Pivot Tables is an online video course that is perfect for anyone looking to build reports and quickly summarize tens to thousands of rows of data. This course will provide the analyst with the best ways to source and clean up source data for reporting. As the data is cleansed, the course will show you how to present the data in a way that is easy to use for analysis by presenting data both tabular and visually. The course will further explore the best ways to analyze the data and drill in and out of data. By the end of this course, you will feel confident sourcing, cleansing, and analyzing data through pivot tables and dashboards.


Click here for a downloadable PDF



Module 01 | The Value of Pivot Tables
26m 04s total


Module 01A | The Business Intelligence Life Cycle
06m 42s


Module 01B | Why You Care About Pivot Tables
06m 33s


Module 01C | Creating Your First Pivot Table
12m 59s


Module 02 | Pivot Compliant Data Sets
51m 35s total


Module 02A | Required Data Layout
13m 42s


Module 02B | Connecting to your Source Data
22m 26s


Module 02C | Using Data Direct from Databases
15m 27s


Module 03 | Modifying Pivot Table Look & Feel
55m 49s total


Module 03A | Value Field Formats
11m 12s


Module 03B | Report Layout
13m 39s


Module 03C | Grouping Data
16m 02s


Module 03D | Pivot Table Styles
07m 14s


Module 03E | Sorting Pivot Tables
07m 42s


Module 04 | Sourcing Data
112m 11s total


Module 04A | Using Power Query to Get & Transform your Data
30m 23s


Module 04B | Appending Data Tables
29m 39s


Module 04C | Consolidating Excel Data
12m 34s


Module 04D | Un-pivoting Data
17m 28s


Module 04E | Flattening Datasets
22m 07s


Module 05 | Changing Pivot Table Aggregations
49m 27s total


Module 05A | SUM, COUNT and Others
09m 28s


Module 05B | Show Values As
23m 57s


Module 05C | Calculated Fields
16m 02s


Module 06 | Slicing and Filtering
42m 12s total


Module 06A | Filtering with Classic Controls
06m 52s


Module 06B | Filtering with Slicers
14m 16s


Module 06C | Timelines
08m 15s


Module 06D | The Show Details Feature
06m 05s


Module 06E | Keeping Your Pivots Looking Nice
06m 44s


Module 07 | Building Pivot Based Dashboards
51m 08s total


Module 07A | Conditional Formatting on Pivots
09m 46s


Module 07B | Keeping Pivots in Sync
07m 49s


Module 07C | Tricks for Extracting Key Info
15m 52s


Module 07D | Working with Pivot Charts
17m 41s


Module 08 | Data Refresh & Security
22m 20s total


Module 08A | Driving Pivot Table Refresh
14m 15s


Module 08B | How Secure is Your Data
08m 05s



Chartered Professional Accountant, Author and Trainer
Microsoft Excel MVP Ken Puls is a Chartered Professional Accountant, author and trainer. With 20 years of experience as a corporate controller and business owner, he has been exposed to many business systems, databases, and analysis tools, leading to a career of building Excel-driven applications to harness data and turn it into management information.

System Requirements

  • All Pivot Table tasks can be performed in any version of Excel. Tasks related to Power Query require installation of the free Power Query tools in some versions of Excel. Tasks related to Power Pivot can only be performed if Power Pivot is installed in your product. Details to activate these tools can be found below:
    • Excel 2010
      • Power Pivot: Can be downloaded and installed in any SKU of Office 2010. Ensure that your Power Pivot version is version 11.0 or higher (not version 10.5.x) The most recent version can be downloaded for free here.
      • Power Query: The most recent version can be downloaded for free here.
    • Excel 2013
      • Power Pivot: Requires Excel 2013 Professional PLUS, Excel Pro PLUS (subscription) or a standalone Excel license
      • Power Query: The most recent version can be downloaded for free here.
    • Excel 2016
      • Power Pivot: Requires Excel 2016 Professional PLUS, Excel Pro PLUS (subscription) or a standalone Excel license
      • Power Query: Built in to all versions of Excel

What to Know Before Class

This is a course aimed squarely at beginner/intermediate Excel users looking to take their skills to the next level. Before starting this course, you should be comfortable working in Excel. Knowledge of a variety of formulas can be helpful, although is by no means necessary. While those comfortable with Pivot Tables are likely to pick up new tricks to make their lives easier, the course is designed to take someone with absolutely no Pivot Table knowledge and teach them how to collect, clean and set up the data, present it in Pivot Tables and build dashboards using Pivot Charts. A basic understanding of Excel functions is helpful, but not necessary for this course. The target audience of this course is for power users who are learning DAX for the first time.

Great course. I now know much more about pivot tables and feel extremely comfortable using them in advanced settings!

Mohammed A.

Ken Puls is great.

Tim A.

This class was very informative.


Start With The Free Community Plan

Pragmatic Works free community plan gives you lifetime access to 7 Microsoft “in a day” courses on Power BI, Excel, Power Apps, Azure Synapse, Power Automate, Paginated Reports and Chatbots.



• Get preview access to all 70+ courses & custom Learning Paths for 7 days

• Labs and files included

• Access courses from our mobile app or desktop

• Access quizzes to practice while you learn

Get Instant Access Now!