Excel Pivot Table Masterclass: Excel Reporting & Dashboards

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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.

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.