Pivot Table Resources

Pivot Table Tutorials

There are numerous articles and tutorials about Pivot Tables on the Peltier Tech blog. This is a nearly complete list of Pivot Table posts.

Explore Your Data With Pivot Tables

Pivot tables are one of Excel’s most outstanding features. They allow fast dynamic and interactive analysis of data from the workbook or from any number of external sources. This tutorial shows the power of pivot tables for fast and detailed analysis.

Preliminary Data Exploration with Excel Pivot Tables

In this tutorial we first normalize the data, then generate easy Marginal Means Plots and Interaction Plots to quickly explore the data.

Making Regular Charts from Pivot Tables

Pivot charts are powerful: they are dynamic, interactive, and can be pivoted. However, sometimes you need something that a regular old chart can do, that pivot charts cannot. This tutorial shows how to create a regular chart from a pivot table.

Create and Update a Chart Using Only Part of a Pivot Table’s Data

This tutorial shows how to use a little VBA to create and maintain a chart that uses only part of a pivot table’s data.

Update Regular Chart when Pivot Table Updates

When a  pivot table is pivoted or updated, it may have different numbers of rows and columns, and a regular chart must be updated manually to keep up. Or you could use a little VBA to keep the chart in line with the pivot table.

Using Pivot Table Data for a Chart with a Dual Category Axis

You can easily lay out a pivot table so that two nested fields can be used to populate the labels in a dual category axis.

Grouping by Date in a Pivot Table

If you collect daily data, the day-to-day variation may overwhelm longer-term trends. You can group dates in your pivot table by larger units (weeks, months, years) and perform broader analysis.

Clean Up Date Items in An Excel Pivot Table

Grouping a pivot table by dates can make the table and related chart easier to read. This article shows how to remove extraneous date entries.

Referencing Pivot Table Ranges in VBA

You can create great analyses by merging the power of pivot tables with the flexibility of VBA. This tutorial removes much of the confusion about how to refer to parts of the pivot table in code.

Copy a Pivot Table and Pivot Chart and Link to New Data

If you copy a chart to a new worksheet, it still reflects data from the old worksheet. You can try to fix all the links, but even this tedium will not help with a pivot table. But the approach described here will do the trick.

Pivot Table Conditional Formatting with VBA

Use VBA to format cells within a pivot table according to various conditions.

Dynamic Chart using Pivot Table and VBA

This article shows how VBA can update a regular chart when its pivot table data changes.

Dynamic Chart using Pivot Table and Range Names

This article shows how dynamic range names without VBA can update a regular chart when its pivot table data changes, as long as the number of charted series is unchanged.

Pivot Chart Formatting Changes When Filtered

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter. Here’s what you can do about it.

Pivot Table Books

Here are a few books about pivot tables, available from Amazon and elsewhere. I’ve selected these books because I know the authors and I’ve used the books, so I can vouch for their quality. Disclosure: if you purchase one of these books using my link, I’ll get a teeny commission.

Beginning PivotTables in Excel 2007: From Novice to Professional

Microsoft Excel Most Valuable Professional Debra Dalgleish explains what PivotTables are, how you can benefit from using them, how to create them and modify them, and how to use their enhanced features. Using a Pivot Table in Microsoft Excel 2007 is a quick and exciting way to slice and dice a large amount of data. Debra carefully explains the benefits of using Pivot Tables for fast data analysis, provides a step-by-step approach to those new to Pivot Tables, and offers tips and tricks that cannot be found elsewhere.

Excel Pivot Tables Recipe Book: A Problem-Solution Approach

Debra Dalgleish, Microsoft Excel Most Valuable Professional since 2001, and an expert and trainer in Microsoft Excel, brings together a one-stop resource for anyone curious about representing, analyzing, and using their data with PivotTables and PivotCharts. Debra presents tips and techniques in this collection of recipes that can’t be found in Excel’s Help section, while carefully explaining the most confusing features of PivotTables to help you realize their powerful potential.

Excel 2013 Pivot Table Data Crunching (MrExcel Library)

This book will help you leverage all the amazing flexibility and analytical power of Pivot Tables. You will learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then you’ll discover how to build a comprehensive, dynamic pivot table reporting system for any business task or function. Microsoft Excel Most Valuable Professionals Bill Jelen and Mike Alexander include step-by-step instructions, real-world case studies, plus complete and easy recipes for solving your most common business analysis problems.