In Excel you can create a **Pivot Table** from any dataset, **Pivot Table** is useful when you need a new data point that can be obtained by using existing data points in the **Pivot Table**. Here you won’t need to go back and add it to the source data. Instead, by using a **Calculated Field** you can do this. A **calculatedfield** is created by using formulas in the **Pivot Table**. In this article, I’m going to explain how you can use the **calculated field **in **Pivot Table**.

To make the explanation understandable, I’m going to use a sample dataset that represents the sales information of a particular salesperson. The dataset has 3 columns; these are ** SalesPerson**,

**, and**

*Region***.**

*Sales***Table of Contents** hide

Download to Practice

8 Ways to Use Calculated Field in Pivot Table

1. Create A Pivot Table

2. Inserting Simple Calculated Field in Pivot Table

3. Adding Complex Calculated Field in Pivot Table

4. Modify an Existing Calculated Field

5. Drawback of Calculated Field in Pivot Table

5.1. Ways to Avoid Calculation Problem of Calculated Field

6. Get the List of All the Calculated Field Formulas

7. Temporarily Remove Pivot Table Calculated Field

8. Permanently Remove A Calculated Field from Pivot Table

Things to Remember

Practice Section

Conclusion

Related Articles

**Download to Practice**

**Uses of Calculated Field in Excel Pivot Table.xlsx**

**8 Ways to Use Calculated Field in Pivot Table**

**1. Create A Pivot Table**

Before diving into the adding of **Calculated Field **let me show you the process of creating a **Pivot Table**.

To create a **Pivot Table**, I’m going to use the dataset given below.

To start with, select the cell range from where you want to create a **Pivot Table**.

➤ I selected the cell range **B3:D12**.

Now, open the **Insert** tab >> from **PivotTable** >> select **From Table/Range**

A **dialog box **will pop up. From there choose the place to place your **PivotTable**.

⏩ I selected ** New Worksheet**.

Next, click **OK**.

➤Then, a new sheet of **PivotTable **will open.

Now, choose the field from **PivotTable Fields **that you want to display in the** PivotTable **layout.

⏩ I selected the **SalesPerson** in **Rows **and **Sales** in **Values**.

Hence, you will get the selected field in the **PivotTable **layout.

**Read More:** **How to Insert A Pivot Table in Excel**

**2. Inserting Simple Calculated Field in Pivot Table**

As my **PivotTable **is ready now, I’m going to show you the process of adding a simple** Calculated Field**.

Here, I want to add a field named **Bonus **depending on** Sales** information. The bonus amount will be **5% **of the sales individual **SalesPerson **achieved.

To begin with, select any cell from the **Pivot Table**.

➤ I selected cell **B4**.

Now, open the **PivotTable Analyze **tab >> go to **Calculations** >> from** Fields, Items, & Sets** >> select **Calculated Field**

A **dialog box **will pop up. From there insert** Name **and **Formula**.

⏩ I used **Bonus **in **Name**.

Type the following formula in **Formula**.

`=Sales*0.05`

Here, **Sales **values are multiplied by **5%**.

Now, click **Add**

As the **Formula **is added then click **OK**.

Therefore, you will get the **Calculated Field **name **Bonus **in the **PivotTable**.

Here all **Bonuses** of individual **SalesPerson **are calculated automatically just by creating a **Calculated Field**.

**Read More: How to Insert Table in Excel (2 Easy and Quick Methods)**

**3. Adding Complex Calculated Field in Pivot Table**

If you want, you also can add a complex **Calculated Field**. Where you can use different functions to calculate any field depending on the existing values of the **Pivot Table**.

To demonstrate the procedure, I’m going to use an **IF function** to calculate the **Commission **based on **Sales**. If any particular **SalesPerson’s Sales** amount is **greater than (>)** **$5000 **then he/she will get **8% **of the commission.

To begin with, select any cell from the **Pivot Table**.

➤ I selected cell **C4**.

Now, open the **PivotTable Analyze **tab >> go to **Calculations** >> from** Fields, Items, & Sets** >> select **Calculated Field**

A **dialog box **will pop up. From there insert** Name **and **Formula**.

⏩ I used **Sales Commission **in **Name**.

Type the following formula in **Formula**.

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

Here, in the **IF **function, I used **Sales>5000** as **logical_test**, **Sales*8%** as **value_if_true **and used **0 **as **value_if_false**. If the condition is met, then it will return the commission of **8%** of **Sales** otherwise **0**.

Now, click **Add**

As the **Formula **is added then click **OK**.

As a result, you will get the **Calculated Field **name **Sales** **Commission **in the **PivotTable**.

Here, all **Sales** **Commissions **of individual **SalesPerson **are calculated automatically just by creating a **Calculated Field**.

**4. Modify an Existing Calculated Field**

It may happen that you may need to modify or change the **Calculated Field**. In those cases, you won’t need to worry because there is an option named **Modify **in the **Calculated Field**.

Here, I want to **modify **the field **Sales Commission**. Instead of **8%**, I want to provide a **7%** **commission **where **sales value **is **greater than $4500**.

To start with, select any cell from the **Pivot Table**.

➤ I selected cell **C4**.

Now, open the **PivotTable Analyze **tab >> go to **Calculations** >> from** Fields, Items, & Sets** >> select **Calculated Field**

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

From the **dialog box**, you can **modify** your existing **Formula**.

⏩ Type the following formula in **Formula**.

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

Here, in the **IF **function, I used **Sales>4500** as **logical_test**, **Sales*7%** as **value_if_true **and used **0 **as **value_if_false**. If the condition is met, then it will return the commission of **7%** of **Sales** otherwise **0**.

Then, click **Add**

** **

As **Formula **is modified so click **OK**.

Finally, you will get the modified values in the **Calculated Field **name **Sales** **Commission **in the **PivotTable**.

Here all **Sales** **Commissions **of individual **SalesPerson **are modified automatically based on the given **Formula**.

**Read More:** How to Rename a Table in Excel (5 Ways)

**Similar Readings**

**Create Table in Excel Using Shortcut (8 Methods)****How to Edit a Pivot Table in Excel (5 Methods)****Update Pivot Table Range (5 Suitable Methods)****How to Insert or Delete Rows and Columns from Excel Table**- Excel Pivot Table Group by Week (3 Suitable Examples)

**5. Drawback of Calculated Field in Pivot Table**

Though **Calculated Field **is easier to use and calculates the fields’ values automatically, yet it has an issue while calculating the **SUM of the Calculated Amounts**.

Carefully look at the **Sum of Sales Commission **which shows **3014 **dollars. Let’s calculate the **SUM **manually using **the SUM function.**

To calculate the **SUM **of **Sales Commission**, select any cell to place your resultant value.

➤ I selected cell **C13**.

In cell **C13 **type the following formula.

`=SUM(C4:C11)`

Here, the **SUM **function will add all the available values of the selected range **C4:C11**.

Now, press the **ENTER **key to get the **SUM**.

Hence, you will get the **SUM **of the **Sales Commission**.

The **Grand Total **I’ve gotten from the **Calculated Field **is **3014 **and the **Grand Total **I’ve gotten from the **SUM **function is **2548**.

This means the **Grand Total **of the **Calculated Field **is **incorrect** for the **Sales Commission** field. Here, the **Grand Total **is not the **SUM** rather it’s the **7% **of the **Grand Total** of **Sales**. Because the **Calculated Field **uses the same calculation in the **SubTotal** and **Grand Total** rows, instead of showing a **SUM**.

**5.1. Ways to Avoid Calculation Problem of Calculated Field**

Now, I’m going to provide a couple of ways to avoid such types of misleading calculations of **Calculated Field**.

You can use the **Filter **option to avoid the calculation problem. By using the **Filter **option I’ll hide the values that are not **greater than** **4500**.

To apply the **Filter**, select the **Row Labels **and expand the **Filter **options.

From **Value Filters** >> select **Greater Than**.

In **Show items for which** provide the condition you already used in your **Calculated Field**.

➤ I selected “**Sum of Sales” “is greater than” “4500”**.

Then, click **OK**.

Hence, 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 **2548**.

If you want, you can recheck it for confirmation.

To calculate the **SUM **of **Sales Commission**, select any cell to place your resultant value.

➤ I selected cell **C13**.

In cell **C13 **type the following formula.

`=SUM(C4:C9)`

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

Now, press the **ENTER **key to get the **SUM**.

Hence, you will get the **SUM **of the **Sales Commission**.

Here both **Grand Total** and **SUM **are equal.

*♦ *In case you don’t require the **Grand Total **then you can remove the **Grand Total **from the sheet.

Select **Grand Total **then **right click on the mouse**.

A **context menu **will appear from there select **Remove Grand Total**.

Here, the **Grand Total **is removed from the sheet.

*If you want, you can calculate the Grand Total outside the PivotTable just as I did to get the SUM of the Sales Commission.*

**Read More:** **How to Get a Count in Excel Pivot Table Calculated Field**

**6. Get the List of All the Calculated Field Formulas**

The **Pivot Table **has a built-in command named **List Formulas** which helps us to get the **Formulas **that we used in **PivotTable**.

To get the list of formulas,

Open the **PivotTable Analyze **tab >> go to **Calculations** >> from** Fields, Items, & Sets** >> select **List Formulas**

All the used formulas will appear in a new sheet.

**Read More:** How to Convert Table to List in Excel (3 Quick Ways)

**7. Temporarily Remove Pivot Table Calculated Field**

In any case, you may wish to or need to remove a **Calculated Field** temporarily.

From the dataset given below, I want to remove the **Sum of Bonus** **Calculated Field** temporarily.

To begin with, select any cell from the **Calculated Field** that you want to remove.

➤ I selected cell **C3**.

Now, **right click on the mouse** >> from the **Context Menu** >> select **Remove “Sum of Bonus”**

Here, the **Calculated Field Sum of Bonus **is removed.

Though the **Sum of Bonus **field is removed from the **PivotTable **layout but 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.*

**Read More:** How to Remove Table Formatting in Excel (2 Smart Ways)

**8. Permanently Remove A Calculated Field from Pivot Table**

You also can remove a **Calculated Field **permanently if you are sure that you don’t need the field anymore.

To begin with, select any cell from the **Calculated Field** that you want to remove permanently.

➤ I selected cell **C4**.

Now, open the **PivotTable Analyze **tab >> go to **Calculations** >> from** Fields, Items, & Sets** >> select **Calculated Field**.

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

Now, click on **Delete**.

Next, click **OK**.

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

**Read More:** Calculated Field Sum Divided by Count in Pivot Table

**Things to Remember**

🔺 Remember to remove **0** from the **Formula **before inserting a formula for calculation. You can use **Formulas** that don’t require cell references.

🔺 While using **Calculated Field** you will need to check the **Sub Totals** and **Grand Totals**.

**Practice Section**

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

**Conclusion**

In this article, I have explained several ways how you can use **calculated field **in **pivot table**. I also explained the drawback as well as the reasons why the **calculated field **fails to work. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

## Related Articles

**How to Refresh All Pivot Tables in Excel (3 Ways)****[Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)****How to Make a Table in Excel (With Customization)****Use Formula in an Excel Table Effectively (With 4 Examples)**- How to Illustrate Relative Frequency Distribution in Excel

## FAQs

### How do I use calculated fields in a PivotTable? ›

**On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field**. In the Name box, type a name for the field. In the Formula box, enter the formula for the field. To use the data from another field in the formula, click the field in the Fields box, and then click Insert Field.

**Why can't I do a calculated field in PivotTable? ›**

When you use the data model you cannot add calculated fields and calculated items as these tools are for regular PivotTables only. Instead **you must use DAX functions to write calculated columns or measures**. Please consider my Power Pivot and DAX course to learn more about how to write calculated columns and measures.

**Which is the most efficient method to modify a calculated field in a PivotTable? ›**

First, select any cell in the pivot table. Then, **on the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, then choose Calculated Field**. Next, select the calculated field you want to work with from the name drop-down list. You can now update the formula as you like.

**How do I use a field more than once in a PivotTable? ›**

**Add multiple copies of a field in the Values area**

- In the PivotTable Field List, in the PivotTable Fields box, click and hold a field, and then drag it to the Values area in the layout section.
- Repeat step 1 to create as many copies of that field that you want to display in the Value area.

**How does calculated field work in Excel? ›**

A calculated field is a new field that performs calculations based on existing fields in your PivotTable. For example, you could use a calculated field to find the average ticket sales using an existing total sales field and a field containing the number of tickets sold.

**Why would you use calculated items in a PivotTable? ›**

A Calculated Item is a custom formula in an Excel pivot table, that **can use the sum of other items in the same field**. For example, calculate the sum of 2 other items in a field. Restrictions: Here are a few general restriction on using custom formulas: Cannot refer to the pivot table totals or subtotals.