List Pivot Table Calculated Fields in Excel

With a built-in pivot table command, you can quickly list pivot table calculated fields and calculated items. This helps if you’re starting to use a pivot table that someone else built, or even for documenting a pivot table that you built yourself.

List Pivot Table Calculated Fields

Follow these steps to list pivot table calculated fields and calculated items details:

1. Select any cell in the pivot table.
2. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab
3. In the Calculations group, click Fields, Items & Sets
4. Click  List Formulas.

List of Formulas

After you click the List Formulas command, a new sheet is added to your workbook, with a list of the calculated fields and calculated items.

The list shows the Solve Order, field or item name, and the formula.

Change the Solve Order

In most pivot tables, the solve order doesn’t have to be changed – just keep the default order that was created. In a few cases, you might need to change the solve order, to get the results that you need.

This video shows how to create calculated items, and changing the solve order for those items.

For more details on Solve Order, and how to change it, go to the Calculated Items page on my Contextures website.

Shared Pivot Cache

All pivot tables that share the same pivot cache will also share the same calculated fields and calculated items. Use the List Formulas command on any one of the pivot tables, to see the details.

If you aren’t sure which pivot tables use which pivot cache, use a macro to list all the pivot tables, with their location and pivot cache index number.

Macro to List Pivot Table Calculated Fields

If you have several pivot tables in a workbook, in different pivot caches, you can use a macro to quickly list the formulas for all the pivot tables.

Get the macro code on my Contextures website, and download the Excel file with the macro, on that page.

The macro checks each worksheet for pivot tables, then lists the sheet name, pivot table name, formula type (calculated field or calculated item), formula name, and the formula.

Video: List Pivot Table Formulas

Watch this short video to see a pivot table with calculated field and calculated items, and how to create a list of those formulas.

More Information

Visit my Contextures website to learn more about pivot table calculated fields and calculated items, and the difference between Calculated Items and Calculated Fields,

____________________________

List Pivot Table Calculated Fields in Excel

____________________________