A new chapter in practical learning · Now enrolling: Excel + AI Mastery, Excel Essentials in 2 Hours, Excel Pivot Tables & Dashboards, Excel VBA Basics with AI, Financial Modeling in Excel, Google Sheets Complete Course, Power BI and Project Management in ExcelView the courses ↗
EXCEL

Excel Pivot Tables & Dashboards: Basic to Advanced

Go from your first Pivot Table to the Data Model, DAX, automated Power Query reports and a one-page Retail KPI dashboard, with practice files, a hands-on assessment and quiz for every module and a final capstone.

11h 51mBeginner to Advanced132 lessons
Created by Priyendra Kumar (PK) · NextGenTemplates Academy
Excel Pivot Tables & Dashboards: Basic to Advanced

What you’ll learn

  • Prepare clean data and build Pivot Tables with confidence
  • Summarize and compare data with Value Field Settings and Show Values As
  • Make reports interactive with slicers, timelines, search and cross-filtering
  • Add calculated fields, calculated items, conditional formatting and sparklines
  • Connect Pivot Tables to other workbooks, CSV, web, PDF and multiple sheets
  • Build one pivot from several related tables with the Data Model, relationships and DAX measures
  • Report YTD, previous-year and YoY % results with DAX time intelligence
  • Automate refreshable reports with Power Query: unpivot cross-tabs and combine a folder of monthly files
  • Compare GROUPBY and PIVOTBY formula pivots with PivotTables and fix the most common Pivot Table errors
  • Build a sales report, an expense tracker, a multi-page sales dashboard and a one-page Retail KPI dashboard

A practical route to better work.

A complete, project-based Pivot Table course in 16 modules. Start with clean data and Pivot Table basics, then master Value Field Settings, sorting, filters, slicers, timelines, grouping, calculated fields, layouts and styles, Pivot Charts, infographic charts, Pivot Table options, GETPIVOTDATA and AI-assisted analysis. Connect Pivot Tables to tables, other workbooks, CSV files, websites, PDFs and multiple sheets with Power Query, then build a sales report, an expense tracking dashboard and a multi-page interactive sales dashboard from scratch. Module 14 adds the Data Model: one pivot from four related tables, DAX measures with CALCULATE, DIVIDE, DISTINCTCOUNT and ALL, and time intelligence (YTD, previous year and YoY %). Module 15 automates your reports: unpivot cross-tab data, combine a folder of monthly files that refreshes itself, compare GROUPBY and PIVOTBY formula pivots with PivotTables, and fix the eight most common Pivot Table errors. Modules 1-12, 14 and 15 each end with a hands-on assessment on a realistic US business workbook with a self-marking Checks sheet, a 10-question quiz and a full solution walkthrough video, and the course closes with a final capstone: a one-page Retail KPI dashboard powered only by PivotTables. Every lesson includes the practice workbook used in the video.

Inside the course

16 modules · 132 lessons
MODULE 01Welcome and Pivot Table Foundations+
Course preview: what you will build2 minPreview
Course roadmap: modules, assessments and the final capstone3 minPreview
What is a Pivot Table?2 minPreview
Clean your data before building a Pivot Table3 min
Pivot Table basics: rows, columns, values and filters4 min
Module 1 assessment: Bean Street Coffee: clean the export and build your first pivots45 min
Module 1 quiz: check your understanding10 min
Module 1 assessment: full solution walkthrough8 min
MODULE 02Value Field Settings+
Value Field Settings explained3 min
Number formatting in Value Field Settings2 min
Summarize Values By: sum, count, average and more2 min
Show Values As: % of total, running total, rank9 min
Module 2 assessment: Summit Fitness Clubs: membership revenue analysis45 min
Module 2 quiz: check your understanding10 min
Module 2 assessment: full solution walkthrough11 min
MODULE 03Sort, Filter, Slicers, Timelines and Grouping+
Sorting data in a Pivot Table2 min
Filters in a Pivot Table7 min
Slicers for interactive filtering8 min
Timelines for date filtering4 min
Grouping and ungrouping dates, numbers and text6 min
Module 3 assessment: Northstar Support Desk: ticket analysis45 min
Module 3 quiz: check your understanding10 min
Module 3 assessment: full solution walkthrough14 min
MODULE 04Calculated Fields and Items+
Calculated Fields5 min
Calculated Items5 min
Module 4 assessment: Redwood Outdoor Gear: margins with calculated fields45 min
Module 4 quiz: check your understanding10 min
Module 4 assessment: full solution walkthrough10 min
MODULE 05Layouts, Styles and Formatting+
Report layouts: compact, outline and tabular3 min
Preset and custom Pivot Table styles7 min
Preset and custom Slicer styles5 min
Preset and custom Timeline styles3 min
Sparklines with Pivot Tables6 min
Conditional formatting in Pivot Tables7 min
Module 5 assessment: Magnolia Hotels: monthly property report45 min
Module 5 quiz: check your understanding10 min
Module 5 assessment: full solution walkthrough10 min
MODULE 06Pivot Charts+
Pivot Charts: section overview2 min
Create your first Pivot Chart7 min
Clustered and stacked column charts8 min
Bar and stacked bar charts6 min
Line and area charts4 min
Pie and doughnut charts5 min
Combo chart (column + line)4 min
Connect slicers to Pivot Charts8 min
Expand and collapse buttons on Pivot Charts5 min
YTD sales chart3 min
Module 6 assessment: Prairie Solar Installers: pipeline PivotCharts45 min
Module 6 quiz: check your understanding10 min
Module 6 assessment: full solution walkthrough15 min
MODULE 07Advanced and Infographic Charts with Pivot Tables+
Advanced charts: section overview1 min
Sales heat map8 minPreview
Histogram chart4 min
Infographic chart8 min
Progress bar chart7 min
Male / female infographic chart7 min
Star rating chart6 min
Filled circle chart8 min
Semi-circle chart6 min
Speedometer chart with a slicer11 min
Progress circle chart with a slicer7 min
Vertical battery chart8 min
Horizontal battery chart5 min
Module 7 assessment: Evergreen Community Fund: fundraising KPI visuals45 min
Module 7 quiz: check your understanding10 min
Module 7 assessment: full solution walkthrough14 min
MODULE 08AI and Pivot Table Options+
AI-assisted Pivot Table analysis5 min
Pivot Table Options: Layout & Format7 min
Pivot Table Options: totals, filters, display and data10 min
Default Pivot Table settings5 min
Move a Pivot Table2 min
Report Filter Pages: one sheet per item3 min
Use Pivot Tables in PowerPoint3 min
GETPIVOTDATA explained4 min
Module 8 assessment: Keystone Property Management: rent roll and options45 min
Module 8 quiz: check your understanding10 min
Module 8 assessment: full solution walkthrough12 min
MODULE 09Advanced Interactivity+
Cross-filtering multiple Pivot Tables4 min
Add a search box to a Slicer5 min
Dynamic named range with OFFSET6 min
Module 9 assessment: Pioneer Freight: interactive shipment report45 min
Module 9 quiz: check your understanding10 min
Module 9 assessment: full solution walkthrough13 min
MODULE 10Connect Pivot Tables to Data Sources+
Connect a Pivot Table to an Excel Table3 min
Connect to another Excel file4 min
Connect to a CSV or text file3 min
Connect to website data5 min
Module 10 assessment: Cedar Health Clinics: connect three data sources45 min
Module 10 quiz: check your understanding10 min
Module 10 assessment: full solution walkthrough12 min
MODULE 11Multiple Tables, Sheets and PDF with Power Query+
Multiple tables: Append method7 min
Pivot Table from multiple sheets5 min
Multiple tables: Merge method6 min
Pivot Table from a PDF file8 min
PDF files with the File.Contents method3 min
Module 11 assessment: Liberty Home Goods: append and merge regional sheets45 min
Module 11 quiz: check your understanding10 min
Module 11 assessment: full solution walkthrough13 min
MODULE 12Projects: Sales Report and Expense Tracking Dashboard+
Project: Sales report with Pivot Tables12 min
Project: Expense tracking dashboard (part 1)12 min
Project: Expense tracking dashboard (part 2)12 min
Module 12 assessment: Bayview Marketing Studio: expense report mini project45 min
Module 12 quiz: check your understanding10 min
Module 12 assessment: full solution walkthrough9 min
MODULE 13Capstone: Multi-Page Interactive Sales Dashboard+
Dashboard demo: what we will build3 minPreview
Understand the dashboard data4 min
Design the dashboard pages12 min
Page navigation with hyperlinks3 min
Overview page: Pivot Tables7 min
Overview page: charts14 min
Product Performance page8 min
Sales Rep Analysis page10 min
Financial Summary page8 min
Monthly Trends page7 min
Custom slicers across all pages7 min
Module 13 quiz: dashboard design check10 min
MODULE 14Data Model, Relationships and DAX+
Data Model and relationships: one pivot from four tables11 min
DAX measures: SUM, DIVIDE, DISTINCTCOUNT, CALCULATE and ALL10 min
Time intelligence: YTD, previous year and YoY %12 min
Module 14 assessment: Cascade Outfitters returns analysis45 min
Module 14 quiz: check your understanding10 min
Module 14 assessment: full solution walkthrough10 min
MODULE 15Power Query Automation, Formula Pivots and Troubleshooting+
Unpivot cross-tab data before you pivot10 min
One pivot from a folder of monthly files (auto-refresh)9 min
GROUPBY and PIVOTBY: formula pivots vs PivotTables8 min
Pivot troubleshooting clinic: 8 errors and their fixes14 min
Module 15 assessment: automate the weekly store report45 min
Module 15 quiz: check your understanding10 min
Module 15 assessment: full solution walkthrough11 min
MODULE 16Final Capstone: Retail KPI Dashboard+
Final capstone brief: Retail KPI dashboard90 min
Submit your final capstone10 min
Final capstone: full solution walkthrough22 min

Practice files

61 downloadable practice workbooks to follow along with the lessons.

Video lessons

101 step-by-step video lessons (11h 51m), streamed securely.

A practical assignment

Apply your learning, submit your work and explain your approach.

A knowledge check

Test your understanding with clear feedback and explanations.

Certificate of completion

Complete the course to download a certificate of completion with a unique certificate ID.

Is this course for you?

  • Working professionals who prepare recurring Excel reports
  • Analysts and students who want to build dashboards in Excel
  • Managers and small-business owners who make decisions from Excel data

Before you start

  • Basic Excel navigation and simple formulas
  • Microsoft Excel 2016 or later for Windows (Microsoft 365 recommended for the AI, Power Query and Data Model lessons; required for GROUPBY and PIVOTBY)

About your instructor

Priyendra Kumar (PK), founder of NextGenTemplates

Priyendra Kumar (PK)

Founder, NextGenTemplates · Creator of PK: An Excel Expert

A business intelligence consultant, educator and template creator with more than 20 years of real-world experience in Excel, Power BI, Google Sheets, dashboards and business reporting.

PK has shared Excel tutorials on YouTube since 2017. In this course he teaches the same practical way: understand the idea, work through a business example, and build something you can use.

More about PK

Good to know

How do I buy a course, and which payment methods can I use?+

Choose a course, select Add to cart and complete checkout. With an Indian billing address you pay in INR through Razorpay, using UPI, cards or net banking. From any other country you pay in USD through PayPal, including debit and credit cards. It is a one-time payment, and the course appears in My Learning as soon as payment is confirmed.

Can I learn at my own pace?+

Yes. Lessons are organized into modules, and signed-in learners can save their progress and resume where they left off.

Are practice files included?+

Yes. Practice files are included with each paid course and can be downloaded from the course once you are enrolled. They are for your personal learning and your own internal work.

Will I receive a certificate?+

Yes. When you complete all required lessons (and any knowledge check or assignment in the course), you can download a certificate of completion with a unique certificate ID. It confirms course completion; it is not an academic or professional accreditation.

How long will I have course access?+

You get lifetime access: you can use the course for as long as NextGenTemplates Academy operates and offers it, as described in the Course Access Policy. There is no subscription or renewal fee.

Can I get a refund?+

Courses are digital content with immediate access, so purchases are generally non-refundable. Limited exceptions are explained in the Refund Policy.

FREE EXCEL LESSONS BY EMAIL

Not ready to enroll yet?

Join the Academy email list for practical Excel and AI tips, free preview lessons and news when a new course opens. Unsubscribe at any time.