Excel Pivot Tables with Excel 2016: From Basic to PowerPivot

Discover how Excel pivot tables transform large data sets into meaningful insights through fast filtering, grouping, and dynamic perspectives that dramatically boost analysis speed and efficiency.

Learn when to use Excel pivot tables to handle large, changing data, reveal relationships and groupings, and quickly create flexible reports by region, product, time, and customers.

Explore the four areas of an Excel pivot table—the rows area, values area, columns area, and filters area—and how they organize and filter sales data by product and region.

Identify the limitations of Excel 2016 pivot tables when sharing with Excel 97–2003, and use compatibility mode and the compatibility checker to ensure your workbooks run properly.

Prepare a clean data source for Excel pivot tables by ensuring a table with column headings, no blank rows or columns, and proper formatting for text, dates, and numbers.

Prepare the data source for an Excel pivot table by ensuring column headings, converting data into a table with no blank rows or columns, and formatting dates and numbers correctly.

Demonstrates cleaning and structuring a data source for Excel pivot tables by creating missing columns (product name, region, market, month, sales), and outlining manual versus formula-driven data preparation.

Learn to create a basic pivot table in Excel 2016 by selecting data, inserting the pivot table on a new worksheet, and configuring fields and layouts for analysis.

Learn to build a basic pivot table by adding fields to rows, columns, and values, and filter data with slicers and timelines to analyze sales by region and product category.

Learn to filter pivot tables using slicers in Excel 2016, insert slicers for account manager and state or province, connect to multiple pivot tables, use multi-select, and remove slicers.

Learn to refresh pivot table data sources in Excel 2016, updating a private cache and handling changes or range expansions, with refresh options for single or multiple pivot tables.

Apply three time-saving pivot table techniques in Excel 2016: control automatic layout updates, start over with one click, and relocate pivot tables for faster reporting.

Learn to enhance a pivot table by applying a readable style, formatting numbers as currency without decimals, filling blank cells, and renaming labels to region, market, and state.

Learn to change pivot table layouts in Excel 2016, including subtotals and grand totals. Explore compact, outline, and form views, and how to show or hide field headers.

Learn to customize pivot table appearance using built-in styles and themes, preview styles, duplicate and rename a custom style, and apply document themes to refresh the look.

Explore changing pivot table summary calculations in Excel 2016 by adjusting the values area, applying sum, count, average, max, min, and other statistical functions via field settings.

Learn to add or remove subtotals in pivot tables, customize subtotals for market and region fields, and apply none or custom options including average, max, and min.

Learn to create pivot table reports and group date fields by months, quarters, years, weeks, or days, and to group numeric data into value ranges for frequency distributions.

Learn to manipulate pivot table field lists in Excel 2016 by dragging and moving fields, adjusting layout, sorting and filtering, and relocating fields among rows, columns, filters, and values.

Explore pivot table sorting in Excel 2016, including manual sort, ascending and descending orders, and sorting by product, category, or sum of revenue; how sorting updates with filters and fields.

Power Pivot enables handling far more than a million rows, multi-table data modeling, and data import from text, Access, SQL Server, and Excel, with Data Analysis Expressions for calculated fields.

Activate the Power Pivot add-in in Excel by using the File tab, Options, and Manage Add-ins in Office 365, then enable it to appear on the ribbon tab list.

Load a large data file into Power Pivot from an Excel workbook, build a data model with tables and relationships, and link Excel data to avoid duplicates.

Build a Power Pivot data model by establishing a relationship between the main and store information tables, then add a DAX calculated column for dates and create a PivotTable report.

Create a scrollable list in Excel using a scroll bar form control, link it to a cell, and use an offset function to display corresponding data from census 2001 data.

Create an interactive Excel pivot table dashboard using form controls and VBA to refresh data, view top or bottom 20 customers by sales, and customize with a scroll bar.

Create a user interface with form controls to manage pivot table filters using macros. Link a scrollbar to show top or bottom customers and refresh data.