Author Archives: Debra

Refresh an Excel Pivot Table on a Protected Sheet

When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables and pivot charts. However, even with that option turned on, you can’t refresh an Excel pivot table on … Continue reading

Posted in Refresh | Leave a comment

Excel Pivot Table Printing Macro

Usually, pivot tables are used for exploring data in the workbook. Occasionally though, you might need to print out a pivot table, to include in a report. To help with that, use the pivot table printing macro below, and visit … Continue reading

Posted in Print and Extract | Leave a comment

Pivot Table Report Filter Sort Macro Saves Time

When a pivot field is in the row or column area, you can sort the pivot items in that fields, using the built-in commands. For fields in the Report Filter area, there isn’t an easy way to sort the pivot … Continue reading

Posted in Sort and Filter | Leave a comment

List All Pivot Fields and Pivot Items in Excel

While you work on a big pivot table in Excel, you might need to document which fields are in the layout. To make that job easy, use this macro to list all pivot fields and pivot items in any pivot … Continue reading

Posted in Programming | Leave a comment

Pivot Table Custom Subtotals in Excel

In a pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. The innermost field doesn’t show subtotals, but you can force them to appear, by creating pivot table custom subtotals. Learn more … Continue reading

Posted in Group and Total | Leave a comment

Remove Pivot Fields With a Macro

Instead of manually taking fields out of a pivot table, you can remove pivot fields with a macro. The sample macro below will remove all the row fields, and there are more examples, and a free workbook, on my website.

Posted in Programming | Leave a comment

Macro to Make a List of Pivot Tables

In a complex Excel file, you might have several lists, and multiple pivot tables based on those lists. To keep things organized, use this macro to make a list of pivot tables in the active workbook. The code is shown … Continue reading

Posted in Programming | Leave a comment

New Pivot Items at End of List

Why do new pivot items appear at the end of the lists, when you add them to an Excel pivot table? It’s hard to find those new items, if they aren’t sorted alphabetically. Keep reading, to see why that happens, … Continue reading

Posted in Sort and Filter | 1 Comment

Distinct Count in Excel Pivot Table

When you create a pivot table to summarize data, Excel automatically creates sums and counts for the fields that you add to the Values area. In addition, you might want to see a distinct count (unique count) for some fields, … Continue reading

Posted in Calculations | 4 Comments

Excel Pivot Table Grand Total Headings

When a pivot table has grand totals, Excel automatically names those totals. I’ll show you some examples, with details on which grand total headings you can change, and which ones you can’t.

Posted in Group and Total | Leave a comment