You can either manually enter the field names or double click on the field name listed in the Fields box.Īs soon as you add the Calculated Field, it will appear as one of the fields in PivotTable Fields list. In this case, the formula is ‘= Profit/ Sales’. In the Formula field, create the formula you want for the calculated field. Note that you can choose from the field names listed below it.Give it a name by entering it in the Name field.In the Insert Calculated Filed dialog box:.From the drop-down, select Calculated Field.Go to Pivot Table Tools –> Analyze –> Calculations –> Fields, Items, & Sets. Here are the steps to add a Pivot Table Calculated Field: Suppose you have a Pivot Table as shown below and you want to calculate the profit margin for each retailer: Let’s see how to add a Pivot Table Calculated Field in an existing Pivot Table. For example, if the metrics change or you need to change the calculation, you can easily do that from the Pivot Table itself.Īdding a Calculated Field to the Pivot Table Once you add a Calculate Field, you can use it like any other field in your Pivot Table. It’s scalable as it will automatically account for any new data that you may add to your Pivot Table.It doesn’t require you to handle formulas or update source data. There are a lot of benefits of using a Pivot Table Calculated Field (as we will see in a minute): Consider Calculated Field as a virtual column that you have added using the existing columns from the Pivot Table. This is the most efficient way to use existing Pivot Table data and calculate the desired metric. But when I changed it from customers to regions, the formula gave an error. As shown below, I calculated the Profit Margin when there were retailers in the row. But if you change the Pivot table, the calculation may not update accordingly and might give you the wrong results or errors. This can be an option if your Pivot Table structure is unlikely to change.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |