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.
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.
In this tutorial we first normalize the data, then generate easy Marginal Means Plots and Interaction Plots to quickly explore the data.
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.
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.
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.
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.
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.
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.
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.
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.
Use VBA to format cells within a pivot table according to various conditions.
This article shows how VBA can update a regular chart when its pivot table data changes.
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.
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.
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.
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.
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.