Results 1 to 4 of 4

Thread: Odd Pivot Table Behavior

  1. #1
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,928
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Odd Pivot Table Behavior

    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
    “The early bird gets the worm, but the second mouse gets the cheese.”

  2. #2
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Odd Pivot Table Behavior

    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!

    Helping you to Excel

  3. #3
    Board Regular btadams's Avatar
    Join Date
    Jan 2003
    Location
    Richmond, Va
    Posts
    1,928
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Odd Pivot Table Behavior

    Thanks Claire! It was about empty cells. Odd that I haven't run into this before.
    “The early bird gets the worm, but the second mouse gets the cheese.”

  4. #4
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Odd Pivot Table Behavior

    You obviously have very well structured data as a rule .
    Glad I was able to help.

    Helping you to Excel

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •