Odd Pivot Table Behavior

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,943
Hello Everybody!

I have a pivot table that is misbehaving. I have a huge table with lots of columns of numeric data that I made a pivot table from. Usually when I click on the checkbox next to a pivot table field to add it to the Values area of the pivot table it shows up as Sum of Column X. However some are showing up with just the column name and when I right-mouse click on them in the pivot table I get Field Settings... rather than Value Field Settings... for those that show up as Sum of Column X. Why?

I found that when I drag & drop those odd fields into the Values area they show up as Count of Column Y in the pivot table and when I right-mouse click on them in the pivot table it now has the Value Field Settings... option and I can change Count to Sum, which I couldn't do when I just checked the checkbox in the PivotTable Fields area.

Anyone know why this behavior is happening?

Thanks,

Brian
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You get Count Of rather than Sum Of if there are ANY blank cells or text values in the column. Excel the treats the whole column as a text field, so you are seeing Field Settings rather than Value Feld Settings, because Excel doesn't think it's a Value field. Find and fix the non-numerical entries. Or live with having to change the Count Of to Sum Of manually!
 
Upvote 0
Thanks Claire! It was about empty cells. Odd that I haven't run into this before.
 
Upvote 0
You obviously have very well structured data as a rule ;).
Glad I was able to help.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top