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 table’s row, column and filter areas. Download the free workbook, and use the macro in your own files.

Pivot Fields and Pivot Items

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.

How to Use the Macro

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 warning message, and the macro stops running.

Then, to run the macro, click the button on the worksheet in the sample file.

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

Set the Maximum Number of Items

The List All Pivot Fields macro will create an itemized list with:

• all the pivot fields in the layout
• all the pivot items in each field

For a large pivot table, that list could take a long time to document, or it could exceed the total number of rows on a worksheet.

To prevent those problems, the macro asks you to enter a maximum number of items.

• 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
• 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.

Then, click OK, to create the list.

Pivot Items Over the Maximum

If you DID NOT set a maximum number of pivot items, you won’t see any messages while the macro runs.

If you DID set a maximum, you might see messages like the one below. For each message:

• click Yes, to see each pivot item listed, or
• click No, to see a single item for the pivot field, with a count of the items

The Completed List

When the macro has finished, you’ll see the completed list for your pivot table.

In the screen shot below, Date field is first. 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.

Get the Macro to List All Pivot Fields and Pivot Items

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.

You can code in your Personal Workbook, or to any other workbook. Then, just the Macros command on the Excel Ribbon, to quickly list all pivot fields and pivot items.

_________________