The Ultimate Microsoft Excel Pivot Table MasterClass

Clean your source data before building a pivot table by ensuring headers, consistent formats, no grand totals or subtotals, and no blank rows or columns.

Compare two pivot table styles and adopt converting data into a data table for automatic updates, enabling dynamic refresh of pivot tables as new records are added.

Merge two data sets using a common key (item code) with VLOOKUP, add pricing and profit margin, and build a single pivot table to analyze city-wise sales and totals.

Explore pivot table layout options—compact, outline, and tabular—and learn to create, compare, and adjust subtotals, label repetition, and indentation in Excel.

Learn how pivot tables automatically compute subtotals and grand totals, and how to control their display with design options, including show, collapse, and position settings.

Group and ungroup data within a pivot table to view sales by custom categories such as morning, afternoon, and evening shifts, and by beverages.

Explore how pivot tables aggregate restaurant sales data into concise reports for decision making. Learn to use sum, count, and average, and adjust value field settings to reveal insights.

Explore how to add a cumulative total (running total) to a pivot table, use sum or count, show running totals horizontally, and handle errors by displaying blanks.

Explore how to apply and interpret percentages in Excel pivot tables, including percentage of total, column total, row total, grand total, and week-to-week trends for insightful dashboards.

Discover how to identify top and bottom performers using pivot tables by applying value filters, including top three, bottom three, and top or bottom five percent.

Create customized calculations in pivot tables using calculated fields to compute metrics like average sale per unit, per week, or per day, without altering the raw data.

Format the pivot table values to look professional and uniform by adjusting decimal places, applying number formats, and correcting errors for clearer dashboards.

Learn how to keep your pivot table formatting intact on refresh by applying auto fit column width and preserve cell formatting on update, ensuring colors and sizes stay consistent.

Design and customize pivot tables by applying styles from the design tab, tweaking banded rows and columns, borders, and grand totals to achieve the look you want.

Learn how to apply conditional formatting to pivot tables to visualize business intelligence, using top/bottom rules, data bars, and color scales to highlight top and bottom performers.

Learn to filter pivot tables with built-in tools, including two-by-two grid, value filters, and label filters, to slice data by category and item name.

Learn to filter pivot tables with slicers, selecting group, city, and week to view specifics; adjust slicer columns and styles, and set don't move or size with cells.

Filter pivot tables by dates using date filters and timelines to analyze May 2013 and June 2013 data and slices from the previous lecture.

Learn how to connect slicers to multiple pivot tables by building relationships and using report connections, so a single set of filters controls several pivot tables.

Master pivot table sorting by arranging groups and categories with alphabetical and reverse orders, using drag-and-drop or auto sort to reset to default settings.

master custom sort in pivot tables by creating and importing custom lists to order items like snacks before drinks, then refresh to apply the sequence.

Sort pivot table values by largest to smallest and by grand totals within categories, using more sort options and auto sort to preserve or refresh the desired order.

Explore drilling into pivot table data by double-clicking to view detailed records, filter top items, and inspect weekly sales trends, while securing data for external recipients with pivot options.

Learn how to use the getpivotdata feature to pull exact values from a pivot table by week, item name, and category, while handling missing data with iferror.

Transform pivot table data into bar, line, and pie charts, using slicers and filters to tailor visuals and present clear insights to diverse audiences.

Explore how to auto refresh pivot tables by enabling refresh data when opening the file and implement a macro‑based workaround via the developer tab to refresh across the workbook.

Learn how to securely share pivot table reports by creating internal and external files, hiding underlying data, and applying professional formatting with consistent column widths and fixed layouts.