ExcelInsider Team
In Excel, pivot tables can be used to accomplish a lot of things. As pivot tables do their own calculations automatically, if we want to make a change and add more formulas, we have to do it using calculated fields. However, if the Calculated Field option is greyed out, we cannot use this wonderful feature in our worksheet. In this article, we will find the reasons why the calculated field option is greyed out and provide solutions to the error.
➤ Go to the original data source.
➤ Create a new pivot table by going to Insert > PivotTable.
➤ Make sure that the “Add this data to the Data Model” checkbox is not checked, and press OK.
➤ Now the calculated field option will be available.
The most probable reason for the calculated field not being available is that the data had been added to the data model. To fix this, we need to lose the data model connection and create an old-fashioned pivot table.
Steps for Fixing a Greyed-Out Calculated Field in Pivot Tables
In this tutorial, we have a pivot table with student grades. There are names of the students, and the numbers they managed to secure in Math, English, Science, and History. We want to create a calculated field with the average numbers of each student; however, it cannot be done because the calculated field option is greyed out. Let’s investigate the issue and fix it.
Step 1: Isolate the Issue
In a pivot table, we must select the proper cell to make sure that the options show up properly. We will start with that to locate the issue.
➤ Go to B3 cell.
➤ In the PivotTable Analyze tab of the ribbon, go to the Calculations group, and select Fields, Items, & Sets.
➤ The calculated field option is greyed out. Even more so, the Change Data Source option is not available as well. As a result, we cannot find the data source unless we already know about it. Luckily, we do for this dataset.
Note:
The Change Data Source option can be available, but the Calculated Field can not be. However, if the data source cannot be changed, it is a proper indication that we cannot add a calculated field.
Step 2: Create a New Pivot Table
The issue with the existing pivot table is that it was created from Power Pivot, and it has a DAX model attached to it. We need to create an entirely new pivot table to add calculated fields. Follow the steps below:
➤ Go to the source dataset that can be used to create a new pivot table.
➤ Select the source table and go to the Insert tab of the ribbon.
➤ Go to Tables > PivotTable.
➤ In the new window, leave everything unchanged, and do not check the box that says “Add this data to the Data Model”. Just press OK to create the new pivot table.
➤ Check all the required boxes from the PivotTable Fields section.
➤ Select a cell of the pivot table; we are choosing A3 here.
➤ Go to the PivotTable Analyze tab on the ribbon, and find the Calculations group.
➤ Now, if you go to Fields, Items, & Sets, the Calculated Field option will not be greyed out anymore.
Frequently Asked Questions
Why is the formula not working in a PivotTable?
Calculated fields in a pivot table do not support complex functions. The best you can do is use mathematical expressions to create a basic calculated formula. If you want to use a formula that includes complex functions, create a new column in the source data and update the pivot table.
Why is the calculated item greyed out in Excel?
Because you haven’t chosen the right cell that you want to calculate. In the pivot table, if you select a cell that cannot have a numerical value to calculate, the Calculated Item will be greyed out. If you have chosen the right cell, and it is still greyed out, it is because the data is added to a data model.
Why is the calculated field not working?
First, check that the data you are using to calculate can actually be used for calculation. A good practice is to try the formula outside of the pivot table first, then add it to a calculated field. If it’s still not working, and you have verified your data, it might be because you have nested functions in the formula, and those don’t work in a calculated field.
How to enable formulas in PivotTable?
There are three ways to do it. First, you can use a calculated field to add a field that uses formulas. This is equivalent to adding a column to the source data, but it is done for calculations, not for raw data. The second method is adding a calculated item, which is like adding rows to the source data. The third method is adding measures, but that requires the pivot table to be part of a data model.
What are Power Pivot calculated columns?
Power Pivot works kind of similarly to pivot tables. In a calculated column, you can calculate the data from other columns and add new data to the data model. You can compare it to the calculated field in a regular pivot table.
Wrapping Up
In this article, we have learned how to fix the calculated field option in a pivot table when it is greyed out. It might be frustrating to know that you have to create a whole new pivot table just to have that option back, but there is no other way to de-grey the calculated field option. You can download the practice file for free that we used to visualize the steps in this article.
