List All Pivot Table Formulas

A few months ago, I shared my code for listing all the formulas in an Excel workbook. The code creates a new worksheet, with details on each formula’s worksheet name, cell address, the formula and the formula in R1C1 format.

formulalist02

Pivot Table Formulas

If you create calculated fields or calculated items in a pivot table, those formulas are not included in the list of worksheet formulas.

However, you can manually create a formula list, for any pivot table, by using a built-in pivot table command.

List the Formulas in Excel 2010

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

pivotformulaslist01

A new sheet is inserted in the workbook, with a list of calculated fields, followed by a list of calculated items.

pivotformulaslist02

Only the formulas in the selected pivot table are listed. If you want to see all the formulas, in all the pivot tables, you’d have to repeat these steps for each pivot table.

Macro Lists All Formulas in All Pivot Tables

Instead of creating the lists manually, you can use programming to create the list for you. I’ve posted sample code and a workbook on my Contextures site, and you can download that to test the macro.

The code adds a new sheet to the workbook, with a list of all the pivot table formulas, with their location and formula.

pivotformulaslist

To download the sample file, go to the Pivot Table Calculated Fields page, and go to the “List All Formulas For All Pivot Tables” section.

Watch the List Formulas Video

To see the steps for creating a list of formulas, for the selected pivot table, please watch this short video tutorial.

__

Or, watch on YouTube:  Get a List of Excel Pivot Table Formulas

______________

2 comments to List All Pivot Table Formulas

  • Dennis McCreery

    Hello Debra,

    Thanks so much for your Excel tips, information, and blog. You generate vast amounts of positive energy for the Excel community and I appreciate it!

    I have a question that I've not been able to find the answer to: I have a spreadsheet that has several pivot tables in it. I'd like to port the views created by those pivot tables to a SQL server. Is there a way to see the underlying SQL queries that generate the pivot data? I have your PowerPivot Premium product but don't see anything in the user interface that provides this data.

    Best regards,

    Dennis McCreery

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>