How to add a calculated field in a PivotTable.
PivotTables in Excel provide strong functionality to summarize and aggregate data contained in a worksheet. In this article, we will learn how to add a calculated field in a Pivot table.
Follow the step-by-step instructions provided below on how to create a PivotTable and then create a calculated field for Cost.
The data set used for this exercise contains Category, Sub-Category, Sales, and Quantity. If you need this data set for your own practice, please request in the comments and I will email it to you.
Steps to create a calculated field in a Pivot Table
- Click on any cell in the source data, from the ribbon, navigate to Insert tab and select PivotTable. From the PivotTable drop-down, select Table/Range.
- The subsequent dialog will display the range; make sure to select ‘New worksheet’ to display the PivotTable. This will display the PivotTable interface on a new sheet.
- From the right PivotTable Fields, select Category, Sub-Category and Sales. This will display the data in the PivotTable.
- Select the Sum of Sales column in the PivotTable and from the Home > Number group, select $. This will display Sales with a $ symbol and 2 places of decimals.
- To add the calculated field, click anywhere on the PivotTable , navigate to PivotTable Analyze option (in the ribbon) and select the option for Fields, Items & Sets. Choose Calculated Field from the drop-down.
- In the subsequent Insert Calculated Field dialog box, provide the name and enter the formula by selecting the fields from the list.
This will add the calculated field in the PivotTable.
For a step-by-step approach to learning more advanced features and functions in Excel, take a look at the book Excel Basics to Advanced.
If you wish to practice more on Excel, visit MS excel Practice questions.
About the Author
Chandraish Sinha has 20+ years of experience in Information Technology. He is an accomplished author and has published 11 books covering Business Intelligence related topics such as, Tableau, Power BI and Qlik. Checkout his Amazon Author profile.
His latest book Excel Basics to Advanced covers all the aspects of MS Excel and provides exercises for self-learning.
Similarly, his recent book, Dashboarding with Tableau, covers all the features in Tableau and includes exercises for self-learning.
He has implemented IT solutions in different domains viz. Pharmaceutical, Healthcare, Telecom, Financial and Retail.
He blogs regularly on various IT topics. Check them out in the links given below:
