Microsoft Excel Pivot Tables - In-Depth Excel Training

Explore what pivot tables are in Excel, what they can do, how to create them by three methods, prepare source data, and view underlying details.

Pivot tables enable flexible, automated analysis by rearranging rows and columns, filtering data, and summarizing without altering the source, with drill-down and expanding or collapsing levels for totals by category.

Explore how pivot tables summarize data with a data field, using sum or count, and organize totals by region, sales reps, and plan type as items, in the data area.

Prepare and format data for pivot tables by ensuring a single rectangular range or expandable Excel table with clear headings, proper data types, and no completely blank rows or columns.

Explore how pivot tables in Excel evolved from Lotus 1-2-3, with key changes in 2007 ribbon, 2010 enhancements like slicers and multi-threading, and 2013 data model improvements.

Learn to create pivot tables quickly with Excel 2013's quick analysis tool, previewing layouts and summarizing data by region, sales reps, and plan types using totals, charts, and spark lines.

Explore how to create pivot tables using Excel’s recommended pivot tables, preview layouts, and choose options by region or plan to summarize orders, commissions, and turn time in three clicks.

Create pivot tables manually by selecting data as a table and dragging fields into rows, columns, and values to summarize data such as region, sales rep, and order total.

Explore how pivot tables summarize data, drill down for underlying records with a double-click, and create region-specific pivot tables with show report filter pages.

Explore general pivot table tools in Excel, including naming pivot tables, moving them between worksheets, and managing the pivot table field list, plus expand/collapse and field headers to organize data.

Identify and manage pivot table source data across worksheets and workbooks, control refresh, and optimize pivot cache to accurately pivot data and improve performance.

Update pivot table data sources to maintain accurate summaries as data changes, whether adjusting the current worksheet range or connecting to an external database.

Learn how Excel pivot tables manage changing source data, and how to refresh on demand or on file open using the analyzed tab and pivot table options.

Understand how pivot cache powers pivot tables by storing a memory-resident snapshot of source data, affecting refresh, memory usage, and file size, and learn when to share or separate caches.

Understand how the pivot cache stores source data and how to create a separate cache for a pivot table using the wizard, including removing source data to reduce file size.

Learn how pivot tables transform raw sales data into concise summaries by pivoting fields in rows, columns, and values, using the field list to analyze sales by region and rep.

Learn how to apply traditional formatting to pivot tables in Excel, modify design, rename fields, repeat labels, and adjust number formats to create meaningful, well-presented data insights.

Learn to format pivot tables quickly using the design tab and pivot table styles gallery, with four on/off options and themes that unlock 84 styles and 1,344 formatting options.

Modify pivot table design with layout options in the design tab, choosing compact, outline, or tabular layouts and adjusting subtotals, grand totals, and blank rows.

Rename pivot table field names for clarity by editing the active field in the analyze tab; ensure unique names and avoid duplicates or conflicts with source data fields.

Adjust pivot table layouts with the design tab, switching among compact, outline, and tabular forms. Enable repeat all item labels in Excel 2010 and later and set totals appropriately.

Apply consistent number formatting in pivot tables by using value field settings to set accounting formatting and zero decimals, ensuring all totals reflect the same style.

Explore how to sort and filter pivot tables with basic and visual tools, including slicers, timelines, and manual grouping by date to reveal focused data.

Master sorting and filtering in pivot tables by creating custom groups, including date-based year and month groupings, while understanding the pivot cache and how naming ranges can isolate caches.

Master pivot table sorting with ascending or descending orders, drag-and-drop custom orders, and predefined lists for row and column labels across pivot tables, worksheets, and workbooks.

Learn to filter pivot tables in Excel with page and report filters, row and column filters, and text based search to focus on relevant data.

explore how to filter pivot tables using slicers, including multi-select regional and sales rep filters, customize styles and columns, and manage slicers with clear filters for intuitive visual filtering.

learn to filter pivot tables by date with timelines, selecting years, quarters, or custom ranges for order date and adjusting the timeline to refine results.

Group data in pivot tables to simplify analysis, using manual or range-based grouping, name groups (like Team 1), and use expand or collapse; pivot charts mirror the grouping.

Learn to group dates in pivot tables by years, quarters, or custom ranges to simplify analysis while preserving monthly or yearly insights, using date serial numbers.

Explore how pivot tables simplify complex data through automated and custom calculations, including subtotals, grand totals, and calculated fields and items, all powered by the pivot cache.

Learn to customize pivot table outcomes by changing summary calculations from sum or count to average, and to display multiple calculations from the same field for deeper insights.

Explore transforming pivot table calculations with show values as, using percent of grand total and percent of row/column totals to analyze regional corporate, family, government, and private sales.

Create a new calculated item in a pivot table to combine north and east into a northeast region, applying a 3% increase for insightful sales analysis.

Discover how pivot charts visually represent pivot table data to simplify analysis, compare with traditional charts, and leverage the pivot layout and cache for clear, memorable insights.

Select a cell in the pivot table, create a pivot chart via insert or tools options, use the default column chart, and move it to its own sheet if desired.

Discover how pivot charts relate to standard charts, rely on the pivot cache, and use the field list and filters to present targeted data visually.

Learn how pivot tables in Excel simplify and summarize data, with drag-and-drop fields, formatting, pivot charts, sorting, filtering, slicers, timelines, and show values as calculated fields, calculated items.