3 Useful Tips on Creating and Managing Calculated Fields in Excel Pivot Table

Pivot table can sum up the values of columns when it is created. And to calculate the values of rows, you can use the calculated fields manually.

The image below shows the sales volume of two products.An Example for Calculated Fields

To make things easier, we have created a pivot table in a new worksheet.A Pivot Table

You can see that the total sales volume of the months will be automatically calculated. But the total sales volumes of two products of each month don’t appear in this table. Hence, you can add the calculated fields into the pivot table.

Add Calculated Fields

  1. Position the cursor in one cell in the pivot table.
  2. Next, click “Options” in the ribbon.
  3. Then click the button “Calculations”.
  4. After that, click the option “Fields, Items, & Sets”.
  5. And then choose the “Calculated Fields” in the drop-down list.Calculated Field
  6. In the “Insert Calculated Fields” window, input a name of the new field into the text box.
  7. And then input the formula into the text box of “Formula”. Here we need to calculate the total values of sales volume, so we input the summation formula. Besides, if you need to refer to the existing fields, you can also choose a field and then click the button “Insert Field”. In addition, if you need to calculate other values, you can also input the corresponding formula into the formula.Insert Calculated Field
  8. After you input the formula, click “OK”. Thus, the new field will also appear in the pivot table.

The Field in the Table

Hide Calculated Fields

Sometimes if you don’t need to use the fields that you have created, you can hide it. There are two methods to hide fields.

Method 1: Uncheck the options in the “PivotTable Field List”. Thus, the field will disappear in the pivot table.Uncheck the Field

Method 2: This method is also very simple with only 2 steps.

  1. Click the field in the “Values” area.Click the Field
  2. In the menu, choose the option “Remove Field”.Remove Field

And then the field will disappear.

The next time if you need to refer to the field, you can also check the corresponding option in the “PivotTable Field List”. And it will appear again.

Delete Calculated Fields

  1. Repeat the 5 steps in the “Add Calculated Fields” of this article.
  2. Next click the small arrow of “Name” in the “Insert Calculated Field” window.
  3. In the drop-down menu, choose the field that you want to delete.Delete Field
  4. And then click the button “Delete”.
  5. After that, click “OK” in the window. Thus, the field has been deleted in the pivot table.

Protect Your Excel against Malicious Attacks

Due to the importance of Excel, many criminals have set their target to the Excel files in people’s computer. They will either stole the whole file from your computer or delete some of the important data and information. Hence, you will need an Excel recovery tool to get back all your precious materials. It can surely be a good helper if you meet with Excel problems.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published.