Excel in Microsoft Excel 3: Pivot Tables Intro & Masterclass

Create your first PivotTable in minutes, using a sample workbook to summarize file sizes by path and by file extension, with fields in rows and values to show sums.

Explore pivot table menus in Excel 2010, learn how analyse or options reveal the field list inside a pivot table, toggle it on and off, and access key options.

Explore how pivot charts visually represent pivot tables, stay connected to them, and update together; choose pie or column charts and see differences in Excel 2010.

Learn pivot tables and pivot charts in Excel, explore menus and chart features, and complete a practice activity using the HPI dataset to total houses sold by region.

Learn to create a pivot table and pivot chart in Excel by placing region names in rows and sales in values, then choose a pie or column chart.

Ensure source data for pivot tables has a top headers row with labeled columns, no gaps in columns, and no merged cells, and select the full data range.

Learn to add extra values to pivot tables by placing them as columns or rows, including 1m%Change, and understand the values behavior and sigma notation.

Explore the recommended pivot tables feature in Excel 2016 and 2010, and learn why it may be less helpful than manually creating pivot tables with the pivot table button.

Move a pivot table in Excel by selecting the entire pivot table and cutting or using the move pivot table feature to place it in a new or existing sheet.

Learn how PivotTables summarize large data, why they don't auto-update, and how to refresh single or all PivotTables to keep analysis current in Excel.

Double-click the grand total to reveal the full data behind the pivot table, then paste values to convert it to a standard chart, showing a focused, shareable view.

Learn to sort pivot table regions manually by dragging to reorder and automatically by a to z or by volume, with options to sort columns or values.

Create a pivot table from the HPIAdmins data to sum houses sold by region and sort regions reverse alphabetically and by total sales to spot the least sold region.

Explore adding additional row fields in pivot tables to answer questions, such as the total bytes per extension for each file path, and learn how subtotals and nesting organize results.

Compare pivot table report layouts, compact, outline, and tabular, and weigh their pros and cons, learning how to display fields like file extension and path side by side.

Customize blank rows in pivot table report layouts by inserting after each item, using compact view or tabular form, and adjust wrap text or shrink to fit.

Group records in a pivot table by selecting regions, using RegionName and SalesVolume, create region groups such as Midlands and London, and rename RegionName2 for readability, with a refresh reminder.

Use the pluses and minuses in pivot tables to collapse groups and focus on higher-level totals, then expand specific items on demand for on the fly detail or presentations.

Discover how pivot tables rotate data by moving file extensions to columns, compare bytes per path, and decide when columns or rows best convey the analysis.

Add page fields, or filters, to a pivot table using the report filter for a prominent view of criteria, with file extensions like .m4b, .mp3, and .me.

apply and manage pivot table filters using visible drop-downs in the filter, row, and column sections; utilize text, value, date filters, wildcards, and top values.

Learn to update pivot table sources when new rows appear by changing the data source range, and use an 'Is Data' field to filter blanks and prevent broken pivots.

Convert your data range to a table to create a dynamically expanding data source; insert table via ctrl+t (or ctrl+l) and enjoy automatic growth as you add rows.

Explore advanced pivot table options, including naming the pivot table for formulas and compact mode labels. Learn to ungroup, refresh data sources, clear selections, and control layout and sorting.

Explore practice activity 3 on the GDP spreadsheet by building a pivot table with year down, season across, and index values; group decades and apply winter and decade filters.

Create a pivot table from the GDP spreadsheet, with year in rows and seasons in columns to summarize values. Group decades and filter for winter and the 1990s and 2000s.

Master pivot table value field settings to switch aggregations (sum, count, min, max, average), rename fields (even with non-printing spaces), and understand how blanks affect counts.

Learn to summarize file counts by file extension in a pivot table using sumifs and vertical lookups, and apply repeat all item levels to keep labels consistent and auditable.

Use conditional formatting to hide duplicates in a column while keeping them available for formulas in a pivot table, with A5=A4 or an offset rule.

Toggle grand totals in pivot tables to verify sums and spot issues; move fields between rows and columns to see where totals appear.

Discover how subtotals work in pivot tables across tabular, outline, and compact layouts, and customize them with field settings for sum, average, or count, including when they appear.

Show items with no data keeps empty columns visible when filtering, preventing broken formulas and preserving a stable layout; access it via field settings, layout and print.

Explore field settings in pivot tables, from manual filters and including new items to layout options, repeat item levels and labels, and commands to insert page breaks and rename fields.

Create a pivot table from the GDP data with year, season, and type as row fields in a tabular layout, showing sum, min, and max for the figure.

Explore hidden pivot table options in the layout and format tab. Rename the pivot table, merge and centre cells with labels, and move the file extension from rows to columns.

Learn totals and filters in pivot tables, including grand totals, multiple label and value filters, and custom lists for reliable sorting; plus alt text for accessible tables.

Master the pivot table display tab, using expand/collapse buttons, tool tips, show items with no data, field captions, and the classic layout for efficient field dragging and external data sorting.

Explore how to print pivot tables in Excel, including showing expand and collapse buttons, repeating row labels on each page, and setting print titles for clear, consistent printed reports.

Learn how the data tab handles source data in pivot tables, including saving, refreshing, show details, and retention settings to manage file size and data integrity with external data sources.

Practice activity 5 covers pivot table options in Excel, including data tab settings to reduce file size, print titles, and options to auto fit column widths and hide errors.

Discover how slicers act as visual filters for pivot tables, how to insert them, filter by region or date, and why multiple slicers unlock more insights in presentations.

Learn how pivot table filters and slicers expose only viable date and region options, preventing impossible selections, and master multiple-item selection with control, shift, and toggle.

Create multiple pivot tables from the same data, insert a pivot chart, and synchronize them with slicers and report connections so filters affect all tied pivot tables.

Discover how slicers streamline pivot table dashboards, customize settings like columns, headers, and hiding items with no data, and connect slicers across pivot tables using visual basic for applications.

Create and connect three pivot tables from a GDP dataset with year and season arranged across and down, then add slicers for year and season to build a linked dashboard.

Explore how date fields behave as pivot table values, including blanks and date formatting, and why sums fail for dates while min, max, or count provide meaningful aggregates.

Group dates by time period in pivot tables to summarize file counts by year, month, day, and even hours or minutes, using date created fields in Excel 2010 and 2016.

Learn to use timelines in pivot tables to filter data by year, quarter, or month, and connect multiple pivot tables for synchronized time-based analysis.

Create a pivot table with date down, region across, and sales volume as sum. Group dates by year and month, and filter to quarter two 2008 to quarter three 2009.

Format numbers in pivot tables using the value field settings to apply precise number formats for clarity. Learn standard and custom formats—number, currency, accounting, date, time, percentage, fractions—to tailor figures.

Master custom number formatting in Excel for pivot tables and charts, including decimals, thousands separators, and locale-specific rules. Learn when to use zero, hash, and comma for precise displays.

Format dates, times, and text in Excel with custom codes such as d, dd, m, mm, y, and h. Use brackets for hours, add text literals, and leverage pivot tables.

Learn custom formatting in Excel using four sections for positive, negative, zeros, and text, separated by semicolons and colored highlights. Use in pivot tables to display yes or over 100.

Apply conditional formatting in pivot tables to highlight values using rules such as between, greater than, top or bottom, and above or below average, and manage formats across selected cells.

Master conditional formatting in excel with data bars, color scales, and icon sets to reveal magnitude and KPI indicators, using rules and formulas like modulus for even numbers.

Format pivot tables for printing by sizing columns, manually narrowing the first columns, and controlling layout with landscape orientation and adjusted margins for a clean, printable result.

Explore pivot table styling in the design tab by duplicating a style to create 'My Pivot Style' and apply it as workbook default, using the Word guide for element mappings.

Create a pivot table from hpi admin workbook, arrange region names down and first date across, filter January, format average price in thousands with a K, and apply data bars.

Unlock pivot table insights by calculating percentages of the grand total, row total, and column total for regional sales across years, with formatting and data bars to visualize shares.

Master advanced pivot table techniques to compute percentages of total, including the 1999 base, the East Midlands region base, and percentages of grand, parent, and row/column totals.

Learn to create cumulative and running totals in pivot tables, showing year-by-year and region totals, with percentage running totals and resets when grouping by decades, and a grand total.

Explore pivot tables to analyze regional sales by year, focusing on 1999, using rank, index, and conditional formatting to highlight top regions and significant values.

Learn how to compute difference from a base year or previous year in pivot tables, use a base field, and compare regions to reveal year-over-year and regional gaps.

Explore pivot table calculations using calculated fields to convert currency, compute average price in dollars, and derive total money from sales.

Explore calculated items in pivot tables, creating a 'first few months' line for Jan–Jun, and the risk of duplicate totals. Also learn calculated fields, list formulas, and solve order.