Excel Pivot Table Masterclass: Excel Reporting & Dashboards

Learn how to prepare pivot-ready data for pivot tables in Excel, ensuring descriptive headers, consistent column data, and first normal form with one record per row, avoiding totals and blanks.

Connect to your source data to prepare it for pivoting quickly, using insert pivot table, external data sources, or the data model with Power Pivot.

Discover how pivot tables based on a data range can miss new records after refresh, and why adjusting the data source to include added rows matters.

Using Excel tables enables auto expansion, structured table referencing, and easy pivot table integration; create tables via format as table or Ctrl+T, assign meaningful names, and apply built-in styles.

Convert your data to an Excel table, customize its style, and build a pivot table that dynamically expands with new rows using structured references for formulas.

Explore how Excel tables outperform ranges with auto expansion, support for pivot tables, data consistency at the source, and easy filtering that underpins modern Excel BI solutions.

Discover how to pull database data into Excel for pivot tables using table, pivot table, pivot chart, or create connection options, and choose between worksheet or data model loading.

Connect to an Access database via the data tab using the legacy connector, load data directly into a pivot table, and summarize large datasets without loading all rows.

Load data into a named table from a database, then create the pivot table from that table, enabling refresh and on-demand column additions.

Load data into the power pivot data model from a database, preview and filter unused columns, and insert a pivot table from the data model in Excel across 2010–2016.

Format pivot table values and adjust column names using the value field settings in Excel, ensuring formats persist after refresh. Explore custom number formats, including thousands separators and K.

Create a pivot table on a new worksheet, add units sold, total sales, and location, then rename fields and apply number formats, including thousand separators, for a clear presentation.

Learn the three pivot table layouts—compact, outline, and tabular—in Excel and how subtotals, grand totals, repeat item names, and blank rows affect readability and layout options.

Learn to change pivot table layouts in Excel by switching between outline, compact, and tabular forms, adjust report layouts, subtotals, and repeat item labels for clarity.

Explore how to group data in a pivot table, handling text, numbers, and dates, and learn manual grouping and related caveats.

Learn to create pivot tables, group dates by days, months, and years, and organize shifts while managing subtotals, grand totals, and the pivot cache across tables.

Explore pivot table styling with the styles gallery and design tab, applying office themes. Toggle header shading and bannered rows, preview live, then create and apply a custom style.

Create and customize pivot table styles in Excel by duplicating existing styles, adjusting row and column stripes, colors, and formatting, and applying them as document templates for consistent reporting.

Explore dynamic sorting in pivot tables, which updates in the values area and reflows on refresh, and static sorting, which you drag to preserve a chosen order.

Master pivot table sorting in Excel, including vertical and horizontal sorts, manual order, and sorting by values with right-click and more sort options to control grand totals.

Use Power Query to extract, transform, and load data from multiple sources into clean, pivot-ready tables, automating data refresh and avoiding tedious manual cleanup.

Learn to clean and transform a messy ASCII text file in power query, using import, trim, split columns, promote headers, locale-aware dates, remove errors, and build robust pivot-ready data.

Learn to append data tables with power query technology, creating long tables for pivot tables or power pivots, and avoid copy-paste errors with a single data refresh.

Master how to prep and append monthly data sets in Power Query. Set date and currency types using locale, then load a unified transactions table for reporting and dashboards.

Consolidate all files in a folder with Power Query by creating a folder parameter, renaming the files list, and applying pre- and post-aggregation transforms to clean and preserve file names.

Consolidate a folder of files in Excel using Power Query by parameterizing the folder path, combining files, transforming data, and loading to a pivot table with refresh workflows.

Consolidate data from external Excel files by selecting one data type—tables, named ranges, or worksheets—and append all tables into a single long table, expanding and transforming as needed.

Learn to consolidate multiple tables from an external Excel file into a single Power Query output, clean and date align data, and create pivot tables for reporting and dashboards.

Learn to get data from the active workbook by creating a new blank query, consolidating all tables, and filtering and expanding the content column as needed.

Consolidate multiple in-workbook tables with a blank query in Power Query, filter by underscores, rename the output to consolidated, and load a combined dataset while filtering out internal tables.

Learn to unpivot pivoted data in Excel by right-clicking headers and selecting pivot other columns to create attribute and value columns, then refresh the solution.

Unpivot simple data sets with Power Query, then pivot to create multiple views of sales data using dates and units, while cleaning and renaming tables for consistency.

Flatten data sets by merging two tables into one using vlookup, detailing what to look up, where to look, and which column to return.

Flatten and unify data from the inventory and sales tables using VLOOKUP to pull sales price and cost by SKU, enabling a single pivot-ready table with clear margins.

Power Query supports six ways to join tables, using staging queries, references, and merge steps to expand the other column and create one big flat table.

Merge tables in Power Query by creating connections and loading them as connections. Join on SKU number and expand fields to build a single joined dataset ready for pivot tables.

Learn six join types in Power Query within Excel, including left outer, right outer, full outer, inner, left anti, and right anti joins, by linking transactions to chart of accounts.

Learn how to merge a transactions table with the chart of accounts using composite keys in Power Query, create left, right, inner, and full joins, and reconcile for pivot tables.

Explore pivot table aggregations, including sum, count, average, min, max, and standard deviations, and how the values area and data context shape what appears, and how blanks suppress context fields.

Explore how to change aggregations in a pivot table by switching from sum to count using value field settings, and learn how refresh and drag operations affect results.

Explore how to present pivot table data using show values as options, including running totals, differences, and percentages, to reveal trends, sales buildup, and item rankings.

Learn to create vertical and horizontal running totals in a pivot table using show values as running total, based on category or date, with controlled formatting.

Duplicate the base field in a pivot table, then use show values as to display difference from or percent difference from, selecting base field and base item.

Explore how to add and customize difference from and percent difference from previous in a month-based pivot table, including grouping by sales group and comparing months.

Rank items in an Excel pivot table using show values as, selecting a ranking method and base field, then apply vertical row or horizontal column layouts.

Rank items in pivot tables by group and category using show values as rank, adjust layout to tabular form, and sort by group and category ranks.

Learn to show the top or bottom X records in a pivot table using the values filter, then switch to tabular view for easier filtering and reveal the top/bottom results.

Flip the pivot table to tabular form. Apply value filters for top or bottom x items by units sold; sort from largest to smallest to reveal top and bottom sellers.

Create calculated fields in an Excel pivot table using the analyze tab, fields and sets, and verify syntax by double-clicking field names. Modify or delete as needed.

Explore how to create and manage calculated fields in pivot tables, apply GST calculations, and troubleshoot when simple calculations become complex, improving your Excel reporting and dashboards.

Explore calculated items in excel pivot tables and learn how to create and add fields with formulas. Recognize how grouped items can duplicate data and avoid inflation.

Explore calculated items in pivot tables, grouping beer subcategories into 'beer products,' which can inflate sales and misplace categories; the instructor argues against this method and suggests alternatives.

Explore classic pivot table filtering with filter fields to slice data and focus on specific locations. Understand where controls sit and the good, bad, and ugly of these filters.

Build a new pivot table, add category and date fields, and format sales as accounting numbers. Drill into location with filters and switch to tabular form for clearer analysis.

Explore filtering with slicers in Excel pivot tables and Excel tables, learn to create them from Insert tab or Analyze tools, and customize headers, styles, sizes, and alignment.

Add and align pivot table slicers, resize them precisely, and lock their position by preventing movement or resizing. Use multi-select and custom styles for clarity.

Explore timelines, date-specific slicers for standard and Power Pivot pivot tables, learn to create them from the pivot tools, and customize captions, styles, and sizing for BI dashboards.

Create and customize a pivot table timeline by inserting a timeline, configuring dates, and drilling through months, days, and years, while aligning, sizing, and comparing with slicers.

Explore the show details feature in pivot tables to drill into source records. Disable it to avoid accidental double clicks creating extra worksheets and out of sync data.

Demonstrates the show details feature in pivot tables, drilling down to reveal underlying records, and explains how to enable or disable it, refresh data, and manage extra sheets.

Toggle automatic column width updates, prevent hashmarks, and manage preserving versus resetting formats; hide filter buttons and expand/collapse buttons to keep pivots tidy during refresh and filtering.

Toggle pivot table options to turn off autofill column widths, set custom header widths, and manage slicers for clean, dynamic reporting.

Learn how to apply conditional formatting to pivot tables and integrate them into dashboards, choosing rules for the pivot, entire column, or a data subset to keep visuals updating.

Build a dashboard by creating a pivot table to summarize units sold and sales dollar. Apply conditional formatting with data bars and use slicers and a timeline for interactivity.

Learn to keep multiple pivot tables in sync using a slicer and report connections, linking slicers and timelines to multiple pivots across worksheets for unified filters.

Link a slicer to multiple pivot tables to keep dashboards in sync, using report connections to tie PBT summary and PBT sales by month.

Learn to extract key information from pivot tables using get pivot data, protect formulas with iferror, and pull slicer selections into reports for focused dashboards.

Build a key stats summary card from a pivot table to show burger sales as a percentage of food sales, with formatting and error handling to keep visuals reliable.

Discover how to create pivot charts from pivot tables by duplicating the pivot table, selecting chart types, switching row/column fields, and using slicers for a polished dashboard.

Build pivot charts from pivot tables to create a monthly sales breakdown. Refine visuals, add a chart title, and link slicers to a dashboard for interactive insights.

Learn to refresh pivot table data with manual refresh, refresh all, and automatic options for external data sources and pivot caches, plus VBA macros for scheduled local refresh.

Explore refreshing external data in Excel pivot tables connected to an Access database, including background refresh, refresh intervals, and refresh on open, with attention to data connections and properties.

Explore how to refresh local data sets in pivot tables by enabling refresh on open, understanding the pivot cache, and using a VBA macro to automate updates.

Learn how pivot tables can expose raw payroll data and protect it by adjusting pivot options, unchecking save source data, and using static pivot cache when sharing dashboards.

Learn how pivot caches can expose confidential data, and how to prevent data leakage by disabling save of source data, enabling refresh on open, and clearing the cache.