Excel for Mac 3: Pivot Tables Intro & Masterclass

Create your first pivot table in Excel for Mac by selecting data and inserting a new workbook pivot table, then compute total size by path and by file extension.

Explore pivot table menus in Excel for Mac, including analyze and design, learn how to reveal the pivot table builder and field list, and customize group titles in preferences.

Learn how to create charts from pivot tables in Excel for Mac, noting that pivot charts aren't available and you must manually adjust pie charts and remove grand totals.

Create and connect pivot charts to pivot tables in Excel, then switch chart types like pie or column and see how chart and pivot table changes sync.

Practice creating pivot tables and charts to analyze regional house sales in the UK using the HPI dataset; verify with Greater Manchester total around 932,000.

Highlight the entire table, create a pivot table, drag region name to rows and sales volume to values, then add a chart (pie or column) to visualize the data.

Learn how to prepare source data for pivot tables in Mac Excel by using labeled headers for each column, avoiding gaps in rows and columns, and avoiding merged cells.

Color the source data to identify original sheets, then build pivot tables for regions showing houses sold or one month percentage change, with values in rows or columns (not both).

Learn to move pivot tables in Excel for Mac by selecting the entire table, using the analyze tab’s move pivot table, and relocating to a new or existing sheet.

Understand why pivot tables may not auto-update as data grows, and learn how to refresh one or all pivot tables, set refresh on open, and manage data source updates.

Extract details from a pivot table by drilling down to view the underlying data. Double-click to reveal totals, then convert pivot to values to share safely and protect sensitive information.

Explore manual and automatic sorting in pivot tables, reorganizing regions by name or sales volume to keep totals aligned, with steps to drag items or sort by smallest to largest.

Create and sort a pivot table from the HPI admins spreadsheet to calculate total houses sold by region, identify the least sold region, and inspect underlying data.

Explore extending Excel pivot tables with extra row fields and multiple value fields to compute total bytes per file extension and per file path.

Compare pivot table layouts in Excel for Mac: compact, outline, and tabular. Assess their advantages and drawbacks, including totals and blanks, and how file extension, paths, and file names display.

Insert or remove blank rows after items in pivot table layouts using design report layout. Compare compact and outline views and use shrink to fit to keep text in bounds.

Create and group pivot table row fields in Excel for Mac, merging East and West Midlands into Midlands, and London into a group, with rest of England for streamlined reporting.

Use the plus and minus buttons to collapse or expand groups in pivot tables, collapsing fields to a chosen path, and switch to compact view to hide details until needed.

Excel for Mac 3: pivot tables intro and masterclass teaches how to add columns to a pivot table. Turn file extensions into a column field and compare bytes by path extension.

Add page fields to a pivot table by moving a field from columns to filters, then filter to show only selected items like mp3 or m4v.

Learn how to apply filters in pivot tables and on worksheets, refining by columns or reports using file extensions like .mp3 and .m4v, with Mac and PC considerations.

Learn how to update a pivot table's data source in Excel for Mac by changing the source range, refreshing, and ensuring new items at the end are included.

Convert the data into a table, then insert a pivot table based on that table. The table expands automatically as you add new rows.

Explore esoteric options in the analyze menu, including naming pivot tables, renaming fields, adjusting paths and file extensions, and managing grouping, slicers, and data sources.

Create and analyze a GDP pivot table in Excel for Mac, arranging year and season, applying filters for winter, grouping decades, and consolidating categories into other to spot anomalies.

Build a pivot table with year down, season across, and values; group decades and consolidate categories into other, then filter to winter and the 1990s and 2000s.

Learn to refine a Mac Excel pivot table by changing aggregates to count, min, max, or average, and count non-blank entries to reveal file counts and sizes by extension.

Explore how pivot tables summarize file counts by directory, use plus and minus buttons to expand items, and repeat all item labels to support reliable sumifs calculations for mp3 extensions.

Use conditional formatting in excel for mac to hide duplicate values while preserving their presence for formulas, leveraging white text and the offset function.

Master grand totals in pivot tables by toggling on for rows, columns, or both. Use them to verify totals, but monitor formulas and data range when needed.

Toggle subtotals on and off in pivot tables, place them top or bottom across outline, tabular, and compact views, and customize with field settings (automatic, none, or custom).

Explore how the show items with no data option in field settings of pivot tables keeps formulas referencing the correct cells when filters hide data.

Create a pivot table from the GDP data, add year, season, and type as raw fields in a tabular layout, repeat the year field, and show min and max values.

Explore hidden pivot table options in Excel, including naming, expand/collapse buttons, printing, empty value handling, and repeating headers to improve reporting across pages.

Master row filters in pivot tables by label and by value, using begins with, contains, greater than, and between. Enable multiple filters per field in layout options to combine criteria.

Explore the layout options in the pivot table layout tab, including auto fit column widths on update, preserve cell formatting, merge and center cells with labels, and filter arrangement.

Learn how pivot tables manage underlying data, including when to save source data with the file, how to refresh, and how item retention and alt text support accessibility.

Explore pivot table options on Mac Excel, including source data settings, retain deleted items, and auto fit widths. Learn to print headings and manage errors for stable pivot table design.

Learn to use slicers as visual filters in Excel for Mac pivot tables, insert them via analyze, and combine slicers to filter by region and date.

Explore how to identify impossible filter combinations in pivot tables and use slicers for multi-select, visualizing valid data with shift and control selections.

Learn to connect slicers to multiple pivot tables via report connections, synchronizing date and region filters across sheets to create a cohesive dashboard.

Master slicer options for Mac, including naming, caption display, sorting, and data indicators. Connect slicers to pivot tables across the workbook to synchronize filters and dashboards.

Create and connect three pivot tables from the GDP data using slicers for year and season to build a dashboard-ready analysis with charts.

Explore how to work with date fields in pivot tables, comparing count, min, and max aggregation, and learn why sum is inappropriate for dates while formatting full date and time.

Group date fields in pivot tables by year, month, or quarter and use slicers to filter by year or month, turning raw dates into concise, period-based summaries.

Create a pivot table from the HP admins data with date down, region across, and sum of sales; group dates by year and month and add a slicer for 2008-2009.

Format numbers in a pivot table using custom number formats, exploring zero vs hash, decimal places, locale separators, and thousands and millions representations.

Explore how to apply custom number formatting in Excel for Mac, including axis formatting, thousands and millions separators, and fractional displays, with hands-on chart examples.

Learn how to apply custom date and text formats in excel for mac, using date codes, time formats, and punctuation to display dates correctly across locales, with practical examples.

Apply custom formatting in Excel to control positive, negative, zero, and text formats with colors and prefixes; learn to hide zeros and use in pivot tables.

Apply conditional formatting in pivot tables to highlight values with built-in rules and custom formats. Manage rules and priorities, including stop if true, to reveal data patterns.

Master conditional formatting in pivot tables using data bars, color scales, and icon sets to visualize data ranges and top or bottom thirds.

Discover how to style pivot tables in Excel for Mac 3 using the design tab, duplicate and customize pivot table styles, and apply banded rows and columns for clearer data.

Format pivot tables in Excel for Mac by ungrouping dates, filtering June 1997 to September 1998, and applying max, data bars, and a purple style.

Learn to build pivot tables in Excel for Mac and display data as percentages of total, column, row, or grand total using show data as settings.

Discover how to show data as percentage of a base item in a pivot table, using 1999 as 100% to compare regions such as East Midlands.

Learn to use pivot table calculations in Excel for Mac to compare data with difference from and percentage difference from, using a base year or region.

Learn to create a running total in a pivot table by using show data as running total, grouping by year, and noting the restart when the base field changes.

Use index to compare figures across rows and columns, identify significant hotspots with conditional formatting and heat maps, and explore advanced calculations in the pivot table tools.

Learn how to create calculated items in mac excel pivot tables, using the first six months example, and why these items often complicate filtering and grouping.