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
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.
Next, the macro inserts a new sheet in the workbook, with headings in the top row – Location, Field, Item and Visible.
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.
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.
The Completed List
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.
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.
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. Then, just the Macros command on the Excel Ribbon, to quickly list all pivot fields and pivot items.