How to Find Calculated Fields, Items, and Sets in Excel PivotTables
Question details
The user needs to locate the missing Fields, Items, & Sets command to create custom calculated fields in an existing PivotTable.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to create calculated fields within a PivotTable in Microsoft 365 Excel but unable to find the required command.
- Observed behavior
- The Fields, Items, & Sets command appears to be missing or hidden, which prevents the user from adding new calculated fields to the PivotTable.
Verify that you have an active PivotTable selected in your worksheet, and note that calculated fields cannot be created if your PivotTable is connected to an OLAP database or the Excel Data Model.
Use PivotTable Analyze Tools to Add Calculated Fields
Activate the contextual ribbon tools by selecting your PivotTable to access the calculations menu.
The command for creating calculated fields only appears when a PivotTable is actively selected. The exact name of the tab may vary slightly depending on your version of Microsoft Excel (e.g., PivotTable Analyze vs. Options).
Click on any cell inside your existing PivotTable to display the PivotTable Tools tabs on the top ribbon.
Click on the 'PivotTable Analyze' (or 'Options' in older versions) tab that appears at the top of the Excel window.
Find the 'Calculations' group on the ribbon, then click on the 'Fields, Items, & Sets' button.
Select 'Calculated Field' from the dropdown menu. Enter a customized name and the mathematical formula you want to apply, then click OK.
Create PivotTable Calculated Fields Easily with WPS Spreadsheet
WPS Office Spreadsheet offers an intuitive and seamless way to manage PivotTables. You can quickly add calculated fields and items without digging through complicated menus, making your data analysis tasks faster.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your raw data.
- 2. Insert a PivotTable: Highlight your data range, go to the Insert tab, and click on PivotTable to create a new summary table.
- 3. Access PivotTable Tools: Click anywhere inside the generated PivotTable to reveal the specialized PivotTable Tools tab on the ribbon.
- 4. Open Calculations: Navigate to the Options tab under PivotTable Tools and click on 'Fields, Items, & Sets' to select 'Calculated Field'.
- 5. Apply Formula: Type a name for your new field, input the desired formula based on existing data, and click OK to instantly update your table.
Frequently Asked Questions
Why is the Fields, Items, & Sets button grayed out in Excel?
This button is disabled if your PivotTable is based on the Excel Data Model (Power Pivot), an OLAP source, or if it uses multiple consolidation ranges. Calculated fields are only supported for standard PivotTables.
How do I edit or delete an existing calculated field?
Click Fields, Items, & Sets, and choose Calculated Field. In the dialog box, use the 'Name' dropdown menu to select the existing calculated field. You can then modify its formula and click Modify, or click Delete to remove it.
What is the difference between a calculated field and a calculated item?
A calculated field applies a formula across entire fields (columns) to create a new data field in the PivotTable. A calculated item applies a formula to specific items within a single field to create a new sub-row or sub-category.
Where is the PivotTable Options tab in Microsoft 365?
In Microsoft 365, the older 'Options' tab has been renamed to 'PivotTable Analyze'. You will find it on the ribbon whenever a cell inside a PivotTable is actively selected.
