How to Calculate a Field in a Pivot Table
PivotTables can analyze massive amounts of data in a few seconds. A data analysis tool that comes with Microsoft Excel, PivotTables have become an indispensable tool in many professions that need to glean insights from large amounts of data. Calculating fields in a PivotTable is a key process in using this tool. You can use tools already located in Excel to perform this function.
Other People Are Reading
Instructions
-
-
1
Open the Excel workbook with the data table from which you want to create a PivotTable. To do this, you can either double click on the Excel file or open the Excel application and select "File" and "Open" to choose the file from your computer.
-
2
Click on "Insert" and find the "Tables" panel. Within the "Tables" panel, there is "PivotTable" button. Click the "PivotTable" button.
-
-
3
Once the "Create PivotTable" dialog box opens, click in the "Table/Range" field. This is where you input the data you want to put in the PivotTable.
-
4
Select the data table--including the labels at the top of the data columns--in one selection in the "Table/Range" field. Select where you want to place the PivotTable in the next section of the dialog box. The choices are "New Worksheet" and "Existing Worksheet." Click OK.
-
5
Click on the PivotTable to activate it then select the field you wish to calculate. Your field name will appear in the active field box on the toolbar. In earlier versions, the field will be highlighted.
-
6
Go to the "Options" tab under the "PivotTable Tools" and "Fields, Items, & Sets." Choose "Calculate Field" from the list that pops up. Give the field a name and press "OK."
-
7
Type an Excel formula in the formula box for the calculation. All Excel based formulas can be used. All parts of the formula that refer to the spreadsheet or PivotTable must use the field names. "If" and "true or false" statements can also be used in these formulas.
-
8
Select "Insert Field" then "OK." Your calculated field will be added as an addition data field and appear in the field display plane. If you need to delete the calculated field delete the field from the list of fields and from the PivotTable. You can click on the field and drag and drop the calculated field anywhere on the table.
-
1
Tips & Warnings
When typing an Excel formula in the formula box, please note that cell references or range names are not supported in these formulas. A list of fields is located under formula. You can double click on the fields to add them to the formula where needed.
References
Resources
- Photo Credit number background image by kuhar from Fotolia.com
