Microsoft Excel: Pivot Tables to Dashboard- Beginner's Guide

Insert your first pivot table from a data range using audit firm data source. Use the field list to organize data in rows, columns, and filters for 2019 service revenue.

Prepare pivot table data by converting the source to a tabular format with clear headers, no blanks, proper data types, and no totals.

Explore pivot table fundamentals, including the field list, rows, columns, values, and filters, and learn to summarize data by sum, count, or average for insights.

Explore pivot table fundamentals, including analyze and design tools, field list visibility, filters and slicers, and practical formatting to produce clean dashboards.

Learn how to keep pivot tables current by refreshing within defined data, using change data source for outside range updates, and leveraging tables that auto expand with growing data.

Explore customizing pivot table styles with the design tab, applying templates, adjusting headers and shading, and selecting layouts—compact, outline, and tabular—for reusable formats across workbooks.

Explore the compact form pivot table layout, activate it, manage nesting and row labels, and use expand/collapse, inner fields, and printable plus minus options.

Explore the outline form layout for pivot tables, creating separate columns for each row field to enable per-field sorting and filtering, with options to repeat labels and insert blank rows.

Master grand totals and subtotals in pivot tables, adjusting placement and layout. Use the design tab to show or hide subtotals and control row and column grand totals, including removal.

Learn how the show in tabular form lays out pivot table data in a tabular block, with years in columns and profit in values, for easy analysis and export.

Learn to use pivot tables to sort, filter, and group data for revenue and profit analysis by managers and departments, with outline or compact layouts and flexible sorting options.

Master the manual selection box in pivot tables to filter rows and columns by selecting or deselecting clients, using search, add current selection to filter, and keep only selected items.

Use label filters in pivot tables to filter by text criteria such as equals, contains, and begins with, with wildcards, across rows and columns, including date options like last year.

Apply value filters in pivot tables using criteria like greater than, between, and top ten to spotlight revenue, costs, and profit across rows and columns.

Enable filters per field in pivot tables to apply a label filter and a value filter at once, for example client names starting with S and revenue greater than 50,000.

Explore the pivot table filters area and add fields like manager, country, city, and department; learn to use report filter pages to create separate tabs for each filter value.

Learn to group data in pivot tables by selecting items, using group options, and renaming grouped managers to analyze portfolios, then ungroup when needed.

Learn how to group dates in pivot tables in Excel, including year, quarter, month, and week groupings, with steps to customize date ranges and use slicers for clear filters.

Group numeric fields in pivot tables to create a frequency distribution of invoices, using 0 to 42,000 in 5,000 increments, and note that non-numeric fields in values area show counts.

Learn to apply show values as to extend pivot table analysis beyond the source data, including percentage of total, running totals, rank, and subtotal.

Master calculating pivot table values as a percentage of column, row, or grand total to analyze revenue shares across years.

Learn to show values as a percentage of a base item and base field in a pivot table, using previous year comparisons to analyze service revenue changes over time.

Demonstrate calculating pivot table values as percentage of parent, using managers and services, to analyze each manager's service shares within their portfolio.

Learn to show values as difference from and percentage difference from in pivot tables, using year and previous year comparisons for 2018–2021 sales.

Learn how to apply running total and percentage running total in pivot tables to analyze year-to-date trends, showing how values accumulate over time for revenue and expenses.

Rank pivot table values by showing revenue as a rank, choosing smallest-to-largest or largest-to-smallest, using service or manager as the base field. See how ranks reveal top services and managers.

Learn how to use index values in pivot tables to measure relative importance against a base. Set values as index, format decimals, and interpret price or cost changes in analysis.

Create calculated fields and calculated items in a pivot table, build profit formulas from revenue and cost, and use show values and sorting to gain deeper data insights.

Learn why a calculated field in a pivot table beats outside formulas for revenue, cost, and profit calculations, especially on refresh and when the pivot expands.

Edit calculated fields in a pivot table by changing their name and formula, apply updates, and observe changes in the field list; note deletion is irreversible.

Learn to create pivot table calculated fields using formulas to compute a 1% bonus on gross revenue above a threshold, and see how totals and filters affect the result.

Learn why calculated fields belong in pivot tables, not the source data, to keep margins accurate when sorting or grouping, and to ensure correct profit margins.

Explore calculated items in pivot tables using non-numeric fields to group services as other revenue, and understand limitations such as grouping conflicts and double counting.

Learn to manage pivot table calculated items, like other revenue and New York percentage, and set their calculation order using the analyze menu in fields, items, and sets.

Identify and manage all pivot table calculations with the list formulas feature, which generates a separate sheet listing every calculated field and item, their formulas and solve orders.

Learn to visualize pivot table data with pivot charts, which update dynamically with filters and sorting, and serve as the foundation for dashboards.

Learn to create pie and donut charts from pivot tables to visualize manager revenues. Copy pivot tables, sort by revenue, apply department filters, and insert a pivot chart for dashboards.

Build a pivot table of top managers by top services and visualize with a stacked bar chart. Switch rows and columns to view by service line and sort results.

Disable automatic resizing of pivot charts caused by autofill changes by setting their size properties to not resize, and adjust pivot table options to keep charts fixed when columns change.

Explore chart layouts and styles in the design tab for pivot charts, applying quick layouts, adding data labels and axis titles, and customizing colors and formatting.

Connect slicers and timelines to pivot tables and charts to create dynamic dashboards with unified filters. Name pivot tables and slicers for report connections and link them across visuals.

Learn to use slicers and timelines with pivot tables to filter by managers, sort by profits, customize slicer styles, and build interactive dashboards linked across pivot tables.

Explore how conditional formatting enhances pivot table visuals by highlighting revenue with color scales, data bars, and icon sets, and apply rules to emphasize high and low values.

Apply conditional formatting in a pivot table to visualize data with data bars or icon sets while keeping the numbers invisible, yielding a professional dashboard look.

master dynamic conditional formatting in pivot tables as you change views by moving fields among rows, columns, values, and filters, choosing among three formatting options.

Create your first dashboard by combining pivot tables, charts, slicers, and timelines, learn to group dates by month, filter by year, and finalize a dynamic, print-ready layout.

Create a professional dashboard by adding a header, title, and brand colors, polish visuals, and link slicers for a dynamic dashboard that fits on one page.