The Complete Excel Course with AI Integration

Excel PivotTables course: pivot tables step by step

PivotTables answer a business question in seconds, and The Complete Excel Course with AI Integration teaches them the way work uses them: a short section of their own on order-level sales data, then the PivotTables that drive its industry-standard marketing, budget and sales dashboards, up to PivotTables built on a Power Pivot data model. Every one of those lessons is below in course order, with the tables and data model that feed them.

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

You will be able to:

  • Get a source table PivotTable-ready: one spelling for each value, no stray spaces or blank rows, and new rows picked up as the table grows.
  • Answer a business question in seconds by placing fields in rows, columns, values and filters, and choose how values are summarised in Value Field Settings.
  • Group order dates into years, quarters and months, and show the monthly trend as a line PivotChart.
  • Lay a PivotTable out as a report people can read: subtotals and grand totals on or off, compact, outline or tabular form, banded rows and a style.
  • Wire slicers and a timeline to every PivotTable on a sheet through Report Connections, so one click filters the whole dashboard.
  • Build PivotTables on a Power Pivot data model of related tables, with DAX measures as the values and a Top 10 filter on products.

Projects

What you will build.

The course has 14 industry-standard projects. These five matter most here.

  • Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Where PivotTables click: net sales by region, state and channel, cost beside revenue by category, a monthly trend, then a challenge you solve on your own.

  • Project 08

    MarketPulse

    One screen that shows which campaigns earn back their spend.

    A dashboard engine of PivotTables, each answering one marketing question, built by hand and again with Copilot, then driven by a timeline and channel and campaign slicers.

  • Project 09

    FinanceGuard

    The budget vs actual report finance teams send every month.

    Formula results pasted as values into a table, then PivotTables by month, department and category and a department-by-month matrix, all under connected slicers.

  • Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    PivotTables on a real data model of sales, product and region tables, with DAX measures as the values and slicers wired to all of them.

  • Project 14

    Agentic AI in Excel

    AI finds and fixes the formula; you check it before it counts.

    Copilot turns one precise prompt into a PivotTable of revenue by region and month, and shows why a vague one falls short.

Tap or pinch to zoom in, drag to move around. Click or scroll to zoom in, drag to move around.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 23 of them, 1 h 54 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 57 PivotTables 101 in Excel

Start at Lesson 57 if you already keep lists as Excel tables; if not, Lesson 19 shows you how first. The later runs sit inside projects: the MarketPulse PivotTables read campaign data prepared from Lesson 105, and the data model PivotTables use the SalesFusion queries built from Lesson 153, so begin a project at its start if you have not built it. With lifetime access, every lesson here stays yours whenever a report calls for a PivotTable.

23 of 186 lessons · 1 h 54 min

03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 1 of 9 lessons · 8 min Excel tables: create and style one, add a total row, and watch new rows join it. A PivotTable built on a table takes in those rows at its next refresh.
  1. 19 Excel tables Create and format a table, structured references and total row 8 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables on SalesPlusPerformance: clean the source, insert a PivotTable, arrange rows, columns and values, group dates, add slicers, chart the result, set the layout and style, refresh, then the challenge.
  1. 57 Start here PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
10 Boosting productivity with AI in Excel (Copilot, ChatGPT and Claude) 1 of 17 lessons · 5 min Copilot builds a PivotTable of revenue by region and month from one prompt; a vague follow-up returns only advice, and a precise sort is applied.
  1. 92 PivotTables with Copilot Build an Excel PivotTable from a prompt 5 min
11 MarketPulse campaign intelligence dashboard 1 of 10 lessons · 6 min The MarketPulse back end: PivotTables copied from one another and rebuilt for channels, campaigns and months, then labelled so the sheet stays readable.
  1. 108 Marketing PivotTables in Excel Build the MarketPulse pivot back end 6 min
11 MarketPulse campaign intelligence dashboard 2 of 10 lessons · 10 min Copilot asked for several PivotTables in one prompt returns one, checked against the hand-built set; then PivotCharts and a date timeline joined to every PivotTable with Report Connections.
  1. 110 PivotTables with Copilot Generate and verify marketing pivots in Excel 6 min
  2. 111 Marketing charts and a timeline in Excel MarketPulse dashboard PivotCharts 4 min
11 MarketPulse campaign intelligence dashboard 1 of 10 lessons · 4 min Channel and campaign slicers connected to every PivotTable, cleared with one button, with several items picked by holding Ctrl.
  1. 113 Slicers for an Excel dashboard Interactive filtering in MarketPulse 4 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 1 of 10 lessons · 8 min A PivotTable workspace for budget against actual: totals with ratios worked out beside them, views by month, department and category, and a matrix of departments by months.
  1. 120 PivotTables for budget vs actual The FinanceGuard pivot workspace 8 min
12 Project: FinanceGuard budget variance analyser (budget vs actual Excel dashboard) 2 of 10 lessons · 9 min Charts drawn from those PivotTables, among them a budget and actual combo with a secondary axis, then month, department and category slicers connected to every PivotTable.
  1. 122 Budget vs actual charts in Excel Combo chart with secondary axis for variance 5 min
  2. 123 Slicers and PivotCharts in Excel Interactive FinanceGuard budget dashboard 4 min
15 SalesFusion: advanced data tools and a Power Query modelling system 4 of 21 lessons · 31 min PivotTables on a data model: sales, product and region tables related in Power Pivot, DAX measures as the values, a report by category in tabular form, then PivotTables by region, month and top products under shared slicers. Power Pivot needs Excel for Windows.
  1. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  2. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  3. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  4. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Is there an Excel course that teaches pivot tables step by step?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches PivotTables hands-on, step by step: a short section of their own on order-level sales data, from preparing the source to refreshing the result, then the PivotTables behind its industry-standard marketing, budget and sales dashboards, up to PivotTables built on a Power Pivot data model. This guide picks out 23 lessons, 1 hour 54 minutes of lessons taught by the instructors themselves, with lifetime access to come back to them.

How should my data be laid out before I make a PivotTable?

As one clean table: a single row of headers, one record per row, no blank rows or columns, and each value written one way. Lesson 58 in The Complete Excel Course with AI Integration shows why it matters: a region typed as West and as WEST appears twice in the PivotTable, and a blank row can make it treat the data as ending early. You fix it there with Find and Replace for the regions, TRIM for stray spaces in the states and PROPER for the cities, then turn the range into a table, the kind Lesson 19 builds.

Why doesn't my PivotTable show the rows I just added?

Because a PivotTable waits for a refresh, and The Complete Excel Course with AI Integration makes refreshing a habit. Lesson 65 adds a row to the sales table, shows the PivotTable unchanged, then refreshes it from the right-click menu and refreshes every PivotTable in the workbook at once with Refresh All on the PivotTable Analyze tab. It also shows why the source should be an Excel table: the new row joins the table, while a plain range would leave it out until the PivotTable is rebuilt.

How do I group dates by month or quarter in a PivotTable?

Put the date field in Rows and let Excel do the grouping. In Lesson 61 of The Complete Excel Course with AI Integration, the order dates fall into years and quarters on their own, and removing the Years and Quarters fields leaves a monthly summary, or a daily one once the months go too. That monthly view becomes a line PivotChart in Lesson 63, and in SalesFusion a Year field placed above the sale date gives a monthly trend straight from the data model (Lesson 172).

What is the difference between a PivotTable filter, a slicer and a timeline?

A filter is a drop-down above one PivotTable; a slicer is a panel of buttons that can drive several PivotTables at once; a timeline does the same for dates. In The Complete Excel Course with AI Integration, Lesson 62 adds slicers to the sales PivotTable and narrows it to one state, city or region at a click. MarketPulse then builds channel and campaign slicers (Lesson 113), wires each to every PivotTable through Report Connections and picks several items with Ctrl, while Lesson 111 adds a timeline that filters the whole dashboard by month.

Can I make one PivotTable from several tables?

Yes, through a data model, and The Complete Excel Course with AI Integration builds a real one in SalesFusion. Lesson 169 loads a sales table and product and region lookups into the data model and relates them in Power Pivot's Diagram View, one to many, so no VLOOKUP joins them. A PivotTable inserted from the data model then reads all three, with DAX measures as its values (Lesson 170) and product categories or region names as its rows (Lesson 172). Power Pivot needs Excel for Windows; the single-table PivotTables earlier in the course work on a Mac too.

Can Copilot build PivotTables for me?

Yes, and The Complete Excel Course with AI Integration teaches you to get a right one. In Lesson 92, one precise prompt naming the figure, its breakdown, the table and where to put it builds a PivotTable of revenue by region and month, and a precise follow-up sorts it, where a vague one only returns advice. Copilot needs its own Microsoft licence, and its work still needs your eye: in Lesson 110, Copilot's PivotTables for the marketing dashboard are checked against the ones you built by hand, total by total, so nothing wrong reaches the dashboard.

How do I show a share of the total or a margin in a PivotTable?

With measures and formulas that keep working as the report changes. In The Complete Excel Course with AI Integration, a data model PivotTable gets its share of the total written once as a DAX measure, total sales divided by CALCULATE with ALL across categories, plus a margin and an average order value with DIVIDE, and every one recalculates for each slicer (Lesson 170). On an ordinary PivotTable, MarketPulse works click-through rate, conversion rate and return on investment out from its totals (Lesson 109), and FinanceGuard does the same for variance percentage and budget use (Lesson 120).

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 industry-standard projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

The same course

For another job, goal, level, topic or comparison.