pivot table - unable to add calculated item - Excel says it's grouped but why?

Occasional Contributor

Hi all,


I want to understand why Excel consideres a pivot table grouped and how this could be resolved?

So I have a table with 10 columns, and I want to make a pivot table where only 3 cols are affected. Highlighted with blue, cols D, G and H.



Here is my pivot:

- columns D and G as rows

- H as values



Now if I want to add a calculated field, say, the ratio of "víz" and "limonádé", it tells me that I can not do that because the Pivot Table report field is grouped.


Now if I simply copy-paste only these columns to a separate ranges and create the very same pivot table from this, Excel has no problems with creating the calculated item.




So why can I add calculated Item from the second, and why can't I add a calculated item from the first table? I obviously did not add a manual grouping, it is just putting multiple fields into the Rows.


Thank you for your explanation!


sample files attached:

sample - pivot item issue - 0.xlsx --> I created some pivot tables before this one, I deleted everything not needed. I tried to rebuild the cash, did not work.

sample - pivot item issue - success.xlsx --> I made a copy of the data sheet itself, and I wanted to make a pivot to reproduce the issue - and I failed... I can create a calculated item here. Hm...

6 Replies


In general it's better to work with data model, but if with cached PivotTable calculated item could be added:


Perhaps you may share sample file?

yes, I can do that when the dataset is just 3 columns wide. But I have 7 more columns as in my screenshot. What I can not understand: why is adding a calculated item possible when the source is just 3 columns wide, and why is it no longer possible when I have 7 more columns. I can not attach the file here, but just try adding a new column and make a pivot with that.
I uploaded two files and a bit of explanation at the end of my original post, please check for clarification.
best response confirmed by szilvia_vf (Occasional Contributor)


In on of the tables you have field Year


which most probably appeared when you added Date (jelentkezés dátuma) to the PivotTable. You may add date again (Month and/or Year will appear), ungroup it and after that remove from Rows. Year shall disappear. After that you may add desired calculated item.

you're the best! Thank you so much!