Category: Pivot Table

How to Plan a Pivot Table in Excel

Setting up a pivot table is a bit like news reporting – you can give a quick summary of the Who, What, When, Where and How (Much) of your data. After you’ve been using pivot tables for a while, it’s easy to create a new report, and drop the fields into the right locations. But, if you’re just starting out, it’s not clear what to put where. I’ve put together a short guide on how to plan a pivot table.

Thanks to Anne Walsh for suggesting today’s topic! Anne has led Excel classes for many years, and she knows that people struggle to get their pivot tables set up correctly. Anne recently published Your Excel Survival Kit, which is jam-packed with useful Excel tips, from her extensive experience.

Continue reading “How to Plan a Pivot Table in Excel”

Pivot Table Show Details Sheets

When you double-click on a pivot table value cell, Excel creates a new sheet, with a list of all the records that make up that total value. The double-click runs Excel’s Show Details command – it’s a helpful troubleshooting feature, but can add clutter to a workbook, because of all the sheets that it creates.

To help you keep things tidy, I created a sample file with macros that label the Show Details sheets when you create them. Then, when the workbook closes, another macro will check for those sheets, and asks if you’d like to delete them. I updated the workbook this week, so take a look, if it’s something you need.

Continue reading “Pivot Table Show Details Sheets”

Quick Ways to Unpivot Excel Data

Before you can build a flexible pivot table, you might need to rearrange the data. For example, if the data has a separate column for each month’s sales, that won’t work well in a pivot table. You need to “unpivot” your data first. Here’s what that means, and 2 quick ways to unpivot Excel data.

Continue reading “Quick Ways to Unpivot Excel Data”

Macro to List All Pivot Fields and Pivot Items

If you’re setting up a big pivot table, it’s easy to lose track of what you’ve added, and what filters have been applied. To help you stay organized, I’ve created a macro to list all pivot fields and pivot items in the selected pivot table’s row, column and filter areas. You can download the sample file, and test the macro in your own files.

Continue reading “Macro to List All Pivot Fields and Pivot Items”

Macro to Remove Pivot Table Calculated Fields

Have you ever recorded a macro to remove pivot table calculated fields? Just turn on the recorder, right-click on the field and hide it, and turn off the recorder. Then, if you try to run that macro later, Kaboom! You get an error message, “Run-time error ‘1004’: Unable to set the Orientation property of the PivotField class”.

Good news – you can download my sample file that has a macro that actually removes those pesky calculated fields, without creating an error message. The video shows how it works.

Continue reading “Macro to Remove Pivot Table Calculated Fields”

Use Running Total to Compare Years in Excel

If you’re analyzing sales data from year to year, you can make a line chart that shows each month’s sales. That lets you see if there were any months with big differences, and shows how sales went up and down over the year. Another option is to use a Running Total to compare years in Excel. It’s quick and easy to set up with a pivot table and pivot chart.

Continue reading “Use Running Total to Compare Years in Excel”

Count Unique in Excel Pivot Table

In a previous article,  Roger Govier showed us a couple of different ways to get a Unique count with a Pivot Table.

  • Create a Pivot of a Pivot Tables results (fast)
  • Add a calculated column to the source data (much slower)

Now Roger has added another technique that you can use, in Excel 2013 and later. First, I’ll show the steps in a video, and then I’ll hand things over to Roger, so he can describe the steps.

Continue reading “Count Unique in Excel Pivot Table”