How to Review Calculated Field and Calculated Item Formulas in an Excel Pivot Table

By Stephen L. Nelson, E. C. Nelson

Sometimes you may need to review formulas in your Excel pivot table. If you click the Analyze tab’s Fields, Items & Settings command and choose List Formulas from the submenu that appears, Excel adds a new sheet to your workbook. This new sheet identifies any of the calculated field and calculated item formulas that you add to the pivot table.

image0.jpg

In Excel 2007 or Excel 2010, you click the PivotTable Tools Options tab’s Formulas button and then choose List Formulas from the menu in order to display the new sheet and its list of calculated fields.

For each calculated field or item, Excel reports on the solve order, the field or item name, and the actual formula. If you have only a small number of fields or items, the solve order doesn’t really matter.

However, if you have many fields and items that need to be computed in a specific order, the Solve Order field becomes relevant. You can pick the order in which fields and items are calculated. The Field and Item columns of the worksheet give a field or item name. The Formula column shows the actual formula.