Fix a Disabled Calculated Field in an Excel PivotTable
Question details
The user needs to enable or replace a calculated field in an Excel PivotTable.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user creates a PivotTable from data converted to an XLSX table and wants to add a calculated field.
- Observed behavior
- Calculated Field is sometimes disabled. Later downloads allowed the option to be selected, so the behavior may be intermittent.
Select a cell inside the PivotTable and check whether its source is a worksheet table, the Excel Data Model, or an OLAP connection. Saving downloaded data as XLSX does not by itself make the source OLAP.
Recreate the PivotTable without the Data Model
Use this method when the data is available as a worksheet table and you need a traditional calculated field.
Click a cell in the worksheet table that contains the data you want to summarize.
Choose Insert > PivotTable. In the creation dialog, select the table or range and clear Add this data to the Data Model if it is selected.
Click inside the new PivotTable, then choose PivotTable Analyze > Fields, Items & Sets > Calculated Field. Enter a name and formula, then select OK.

Use a measure for a Data Model PivotTable
Use a measure when the PivotTable must remain connected to the Excel Data Model.
Troubleshoot Excel if a worksheet-based PivotTable is affected
Try these checks if Calculated Field remains disabled in a newly created PivotTable that does not use the Data Model or an OLAP source.
Work with spreadsheet data in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Excel files. It can help you move everyday spreadsheet work to WPS, although it does not repair Excel's disabled Calculated Field command.

Frequently Asked Questions
Why is Calculated Field grayed out for my PivotTable?
The PivotTable may use the Excel Data Model, an OLAP source, or another connection that does not support traditional calculated fields. Select a cell inside the PivotTable and check its source before recreating it.
Where is Calculated Field in Excel?
Select a cell inside a supported PivotTable, then choose PivotTable Analyze > Fields, Items & Sets > Calculated Field.
Can I calculate values in a Data Model PivotTable?
Yes. If your Excel edition supports it, use Power Pivot > Measures > New Measure to create a DAX measure instead of a traditional calculated field.
Does converting database data to XLSX make it an OLAP source?
No. An XLSX file is not automatically an OLAP source. Check how the PivotTable was created and whether it uses the Data Model or an external connection.
What if excel /safe is not recognized?
On Windows, make sure Excel is closed and enter excel /safe in the Windows+R Run dialog. If it is still not recognized, you can skip that test and use Settings > Apps to repair Office.




