Welcome, Excellers. Almost everyone who has used Excel has had to work with Pivot Tables at some stage. I will show you how to list all the calculated fields and items in your Pivot table in today’s article. You may have inherited an Excel workbook, and this list is handy to get a sense of how the Pivot Table works. Or, listing the calculated fields and items is an excellent way to track your history of how you built the Pivot Table and to follow your logic

The Basic Pivot Table.

First, I created a basic Pivot Table to demonstrate how to list all Pivot Table calculated fields and items. I have a data table with some test data. The data contains sales values and volumes for department stores for their shoe departments. As a refresher, or if you are new to Excel Pivot Tables, creating a Pivot Table is straightforward. I am using Excel 365, so your version may slightly differ, but it is generally the same process.

Next, select the data set for your Pivot table. I am using an Excel data table in this example.

• Put your cursor somewhere in your data list (why not use my example data in the Download below?)
• Select the Insert Tab then the Pivot Table Icon in the Tables Group.
• Select From Range/Table.
• Put your Pivot Table on a New Worksheet or the current one. For this tutorial, select the first option
• Click Ok
• Select the Store for Rows and Sales Value for the data area.

We now have a Pivot Table to work with.

Create A Pivot Table Calculated Field.

Now we have a Pivot Table to work let’s quickly recap on how to create a calculated field. If you are new to Pivot Tables this is a quick tutorial, if you are used to this function then it more of a recap. Bu first let’s recap some more basics around adding extra fields to Pivot Tables.

What Is A Calculated Field?.

A calculated field an addtional new field that is derived from calculations based on existing Pivot Table fields in your Pivot Table. Thats is to say you can create extra fields using the sum of other Pivot Table Fields. These extra fields are useful and you can use any of the Excel mathematical equations, like /*+-% on your field list.

Limitations Of Calculated Fields

Modify Calculated Fields

Delete Calculated Fields

Create A Pivot Table Calculated Item

Extra Facts About Pivot Table Calculated Fields and Items.

I have also created a Calculated Field ‘Average Selling Price’.

And a Calculated Item which is Grey and Henrietta Street Total Sales

So let’s go ahead and list our formulas

• Click in any cell in the Pivot Table
• Pivot Table Tools – Analyse
• Calculations – Fields Items & Sets
• List Formulas

Excel inserts a a new worksheet, with a list of Calculated Fields and Items. The output from my example can be seen below:

Easy, we can see all of the details of our formulas. If you want to download the sample Excel workbook for this blog post you can so so here

Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.