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.
Source DataNew to Excel? Watch our Free Excel for Beginners Playlist on YouTube to get started with step-by-step video tutorials.
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.
PivotTable and Fields.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.
Selecting the Calculated field option.In the subsequent Insert Calculated Field dialog box, provide the name and enter the formula by selecting the fields from the list.
Inserting the calculated field.This will add the calculated field in the PivotTable.
Calculated field added in the PivotTableFor a step-by-step approach to learning more advanced features and functions in Excel, take a look at the book Excel Basics to Advanced.
Chandraish Sinha is the Founder and President of Ohio Computer Academy, a leading institution committed to delivering high-quality IT education. With a passion for teaching and a belief in his company’s mission—Inspire, Educate & Evolve—Chandraish brings over 25 years of experience in the Information Technology industry.
Explore more of his work on his Amazon Author Profile.
Post a Comment