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.

How the Code Works

This macro finds any pivot fields that are in the pivot table layout, and lists all the pivot items in each field. You can read more about pivot fields and pivot items on my website.

Before you run the macro, select any cell in a pivot table. At the beginning of the macro, the code checks to see if a pivot cell is selected. If not, you’ll see a message, and the macro stops running.

listpivotfieldsitems01

Next, the macro inserts a new sheet in the workbook, with headings in the top row – Location, Field, Item and Visible.

listpivotfieldsitems02

Set a Maximum Number of Items

The macro will list all the pivot fields in the layout, and all the pivot items in each field. If you’ve got a large table for your source data, it would be easy to have a total number of pivot items that’s greater than the number of rows on a worksheet.

So, the macro shows an input box, and asks you to enter a maximum number of items.

listpivotfieldsitems03

If you’re not worried about hitting the row limit, and you want to see all the pivot items for each field, leave the zero in the input box, and click OK. Otherwise, enter a number, and you’ll see a message for every pivot field that has more pivot items than you’ve set as the maximum.

I set 40 as the maximum, so now I can click Yes, to see each date listed, or click No, to see a single item for the Date field.

listpivotfieldsitems04

The Completed List – All Pivot Fields and Pivot Items

Here is the completed list for my pivot table. The Date field is first, and because I clicked No, it shows “100 items”, instead of listing each date on a separate row. All the dates are visible, so there is a “Y” in the Visible column.

Below that, the District field is listed, with a row for each of its pivot items. The visible pivot items are marked with a “Y”, and hidden items have a blank cell.

listpivotfieldsitems05

Get the Sample File

To get the code, you can go to the Excel Files page on my Contextures website. In the Pivot Table section, look for PT0044 – List Pivot Fields and Pivot Items. The zipped file is in xlsm format, so enable the macros when you open the file, if you want to test the code.

There is a button on the pivot table sheet, and you can use that to run the macro (Remember to select a cell in the pivot table first).

You could also store this code in your Personal Workbook, if you wanted to run it easily, in any open workbook.

listpivotfieldsitems06

_________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.