We’ll use a sample dataset that represents the sales information of a particular salesperson. The dataset has 3 columns: SalesPerson, Region, and Sales.

**How to Use a Calculated Field in a Pivot Table**

**Part 1 – Create a Pivot Table**

We’re going to use the dataset given below.

- Select the cell range from where you want to create a
**Pivot Table**. We selected the cell range**B3:D12**. - Open the
**Insert**tab and under**PivotTable**select**From**__T__able/Range

- A dialog box will pop up. Choose the location or cell to place your
**PivotTable**. We selected.__N__ew Worksheet - Click
**OK**.

- A new sheet with the
**PivotTable**will open. - Choose the fields from
**PivotTable Fields**that you want to display in the**PivotTable**layout. We selected the SalesPerson in Rows and Sales in Values.

- You will get the selected fields in the
**PivotTable**layout.

**Part 2 – Inserting a Simple Calculated Field in a Pivot Table**

We want to add a field named Bonus depending on the Sales information. The bonus amount will be 5% of the sales.

- Select B4 from the
**Pivot Table**. - Open the
**PivotTable Analyze**tab and go to**Calculations.** - From
**Fields, Items, & Sets**, select**Calculated Field**

- A dialog box will pop up. Insert
**a Name**and a**Formula**. We used**Bonus**in**Name**. - Insert the following formula in
**Formula**.

`=Sales*0.05`

- Click
**Add**.

- Click
**OK**.

- You will get the
**Calculated Field**named Bonus in the**PivotTable**.

- All
**Bonuses**are calculated automatically.

**Part 3 – Adding Complex Calculated Fields in Pivot Table**

We’ll calculate the Commission based on Sales. If a sales amount is greater than (>) $5,000 the salesperson will get 8% of the commission.

- Select any cell from the
**Pivot Table**. We chose C4. - Open the
**PivotTable Analyze**tab, go to**Calculations**, and from**Fields, Items, & Sets,**select**Calculated Field**

- A dialog box will pop up. From there insert
**Name**and**Formula**. We put**Sales Commission**in**Name**. - Use the following formula in
**Formula**.

`=IF(Sales>5000,Sales*8%, 0)`

- Click
**Add**.

- Click
**OK**.

- You will get the
**Calculated Field**named**Sales Commission**in the**PivotTable**.

**Part 4 – Modify an Existing Calculated Field**

We want to modify the field Sales Commission. We want to provide a 7% commission where the sales value is greater than $4,500.

- Select any cell from the
**Pivot Table**. - Open the
**PivotTable Analyze**tab, go to**Calculations**, choose**Fields, Items, & Sets,**and select**Calculated Field**.

- A dialog box will pop up. Select
**Sales Commission**from**Name**to see the existing Formula.

- Replace the formula with:

`=IF(Sales>4500,Sales*7%, 0)`

- Click
**Add**.

** **

- Click
**OK**.

- You will get the modified values in the
**Calculated Field**named**Sales Commission**in the**PivotTable**.

**Part 5 – Drawback of Calculated Fields in Pivot Table**

Look at the Sum of Sales Commission which shows $3,014. Let’s calculate the SUM manually using **the SUM function**.

- Select cell
**C13**. - Insert the following formula.

`=SUM(C4:C11)`

- Press the
**Enter**key to get the SUM. - You will get the SUM of the Sales Commission.

The Grand Total from the Calculated Field is 3,014 and the Grand Total we got from the SUM function is 2,548.

This means the Grand Total of the Calculated Field is incorrect for the Sales Commission field. The Grand Total is not the SUM but the 7% of the Grand Total of Sales, because the Calculated Field uses the same calculation in the SubTotal and Grand Total rows.

**Part 5.1 – Ways to Avoid Calculation Problem of Calculated Field**

You can use the **Filter **option to avoid the calculation problem.

- Select the
**Row Labels**and expand the**Filter**options. - From
**Value Filters**, select**Greater Than**.

- In
**Show items for which,**provide the conditions you already used in your**Calculated Field**. We selected “**Sum of Sales,” “is greater than,” and “4500”**. - Click
**OK**.

- You will get the Grand Total of the values which met the condition applied on the
**Calculated Field**. - The Grand Total of Sales Commission is 2,548.

- In cell
**C13,**use the following formula.

`=SUM(C4:C9)`

- The
**SUM**function will add all the available values of the selected range**C4:C9**.

- Both Grand Total and SUM are equal.

You can also remove the Grand Total from the sheet.

- Right-click on
**Grand Total**. - Select
**Remove Grand Total**.

- The
**Grand Total**is removed from the sheet.

*You can calculate the Grand Total outside the PivotTable just as we did to get the SUM of the Sales Commission.*

**Part 6 – Get a List of All the Calculated Field Formulas**

- Open the
**PivotTable Analyze**tab, go to**Calculations,**go to**Fields, Items, & Sets**, and select**List Formulas**.

- All the used formulas will appear in a new sheet.

**Part 7 – Temporarily Remove a Calculated Field**

We want to remove the **Sum of Bonus** **Calculated Field** temporarily.

- Select any cell from the
**Calculated Field**that you want to remove. We selected cell**C3**. - Right-click and select
**Remove “Sum of Bonus”**.

- The Calculated Field Sum of Bonus is removed.

- Though the Sum of Bonus field is removed from the
**PivotTable**layout, it is still available in**PivotTable Fields**. You can use it again if you want.

You also can uncheck the **Calculated Field **from the **PivotTable Fields** to remove the **Calculated Field **temporarily.

**Part 8 – Permanently Remove a Calculated Field from a Pivot Table**

- Select any cell from the
**Calculated Field**that you want to remove permanently. - Open the
**PivotTable Analyze**tab, go to**Calculations**, then, from**Fields, Items, & Sets**, select**Calculated Field**.

- A dialog box will pop up. Select
**Bonus**in**Name**to see the existing**Formula**. - Click on
**Delete**.

- Click
**OK**.

- The
**Sum of Bonus**field is removed permanently from the**PivotTable**layout as well as from the**PivotTable Fields**.

**Practice Section**

We’ve provided a practice sheet in the workbook to practice these explained examples.

**Download the Practice Workbook**

## How to Use Calculated Field in Excel Pivot Table: Knowledge Hub

**<< Go Back to Pivot Table Calculations | Pivot Table in Excel | Learn Excel**

Very Good Condition & Application of Excel Pivote

Hello

Fazlay RabbyThanks for your compliment! You are very welcome. We are glad that you found the article helpful.

Regards

Lutfor Rahman ShimantoExcelDemy