Pivot Table Show Details Sheets

Excel Pivot Table Show Details

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 Pivot Table 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.

Continue reading Pivot Table Show Details Sheets

Quick Ways to Unpivot Excel Data

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.

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Quick Ways to Unpivot Excel Data

Macro to List All Pivot Fields and Pivot Items

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

How to Create a Pivot Table Style

How to Create a Pivot Table Style

You don't have to stick with the default formats for your pivot tables. You can create a pivot table style with your own colours, and other formatting options that you like. Here's how to get started, and a video with a simple formatting change that you can make.

Continue reading How to Create a Pivot Table Style

Macro to Remove Pivot Table Calculated Fields

Macro to Remove Pivot Table Calculated Fields error

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

Running Total to compare years

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

Pivot Table Time Problems

pivot table time rounding

It's pivot table time! First, we'll take a look two common pivot table time problems - rounding and totals. Then I'll show you a couple of ways to save time when working with pivot tables.

Continue reading Pivot Table Time Problems

Count Unique in Excel Pivot Table

Distinct Count in Pivot Table http://blog.contextures.com/

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. I'll hand things over to Roger, so he can describe the steps.

Continue reading Count Unique in Excel Pivot Table

Change Pivot Chart Layout

Fix Pivot Chart Column Colors http://blog.contextures.com/

Recently, someone asked me why all the columns in their pivot chart were the same colour. The chart showed several months of data, for multiple regions, and every column was blue.

In the screen shot below, I've created a similar pivot chart, from fake sales data.

Continue reading Change Pivot Chart Layout

Show Rank in Excel Pivot Table

Add Rank to Excel Pivot Table http://blog.contextures.com/

A pivot table is a great way to quickly summarize number, to see how your business is doing. But sometimes those numbers are too large to analyze in your head, so let the pivot table give you a bit of help, with the pivot table Rank calculation.

Continue reading Show Rank in Excel Pivot Table