How to Add a Calculated Field in PivotTable

Imagine you have PivotTable and you have revenue column on it and you want to calculate the tax of the revenue.

Here you need to add a calculated field for that.

For that you should follow these steps;

How to Add a Calculated Field in PivotTable;

1- select the PivotTable.

2- Click on the Analyze tab.

3- Click on the “Fields, Items, &Sets” key.

4- Click on the “Calculated Field…”.

5- Specify your desired name in the Name Field.

6- Enter your desired formula, in the formula section.

Here we wanted to calculate 20% of the revenue values because of that we wrote this formula.

=0.2*‘Sales USD’

7- Click on Add.

8- Select your created Field. here we chose Tax Field.

9- Click Ok.

Now you can see a new column was added in the PivotTable.

How to Put a Text into The PivotTable;

Here we are going to explain how to put a text into the PivotTable by using Calculated Field;

we are going to explain it by giving an example;

Here we have the names of employee that work for a company and we want to find the people who are eligible for increasing the income for next year. according to the company policy, only the income of people who their income are lower than 50000$ in a year will be increased for next year.

1- Select the PivotTable.

2- Click on the Analyze.

3- Click on the “Fields, Items, &Sets” key.

4- Specify your desired name, here “Eligible person” was chosen.

5- In the Formula section we wrote this formula.

=If(‘Income2020’>50000,1,0)

By that, we divided our employees into two groups.

In one group we have people with the yearly income which is higher than 50000$ and in the second group, we have people with the yearly income lower than 50000$.

6- Click “Add” key.

7- Select “Eligible person” and click Ok.

8- You can see in front of the people’name with the yearly income higher than 50000$ digit number one and with the yearly income lower than 50000$ digit number zero.

9- Right-click on the “Eligible column” and select “Value Field Settings”.

10- Click on the “Number Format”.

11- Click on the “Custom” and in the “Type” section write

“Not Eligible”;;“Eligible”

This formula said for positive number write “Not Eligible”

for negative number write nothing

for Zero number write “Eligible”.

12- Click on Ok.

13- Now you can see in the third column we have text instead of a digit.