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.
To make things easier, we have created a pivot table in a new worksheet.
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
- Position the cursor in one cell in the pivot table.
- Next, click “Options” in the ribbon.
- Then click the button “Calculations”.
- After that, click the option “Fields, Items, & Sets”.
- And then choose the “Calculated Fields” in the drop-down list.
- In the “Insert Calculated Fields” window, input a name of the new field into the text box.
- 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.
- After you input the formula, click “OK”. Thus, the new field will also appear in the pivot 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.
Method 2: This method is also very simple with only 2 steps.
- Click the field in the “Values” area.
- In the menu, choose the option “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
- Repeat the 5 steps in the “Add Calculated Fields” of this article.
- Next click the small arrow of “Name” in the “Insert Calculated Field” window.
- In the drop-down menu, choose the field that you want to delete.
- And then click the button “Delete”.
- 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.
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