Percent of True Items in a Pivot Table Field

Percent of True Items in a Pivot Table Field

Sometimes you may want to use a pivot table to summarize data whose values are either true or false. For example, whether congressional representatives have law degrees, whether cities have chlorinated water supplies, or whether students are taking classes at the honors level. I’m thinking of traits which exist not in opposition to another trait, e.g., blue eyes versus brown, but, in isolation, e.g., “has brown eyes.” In many cases, such as pulling from a database, these types of items are only filled if they’re true, otherwise they are left blank.

While struggling to summarize some data like this in a single column of a pivot table, I had the following realization:

The percentage of True items in a list is the average of zeros and ones, where True is represented by 1 and False by 0.

For example, assume you have a list of students in different classes, some of whom are taking the class at the honors level. This is indicated in an”Honors” column that’s either marked True or left blank. (Note that the following would work exactly the same if the blanks were instead marked False.)

The Clunky Way

To show the percent at the honors level, you could pivot on the Honors column as it is, but you’d have to show both the True and blank values, as in the pivot table below:

Pivot True and False

In this pivot table, the Values field is Students, “Summarize Values By” is set to “Count” and “Show Values As” is set to “% of Row Total”.

With this setup you’re stuck with using two pivot table columns. If you uncheck “(blank)” in the Honors dropdown, the pivot table reports 100% for every class, since it’s now filtered to only the True items.

The Un-Clunky Way

So instead let’s add a helper column to our data. I called it “Honors for Average” in the picture below. It just multiplies the adjacent Honors column cell by one, resulting in either 0 or 1.

helper column added

We can now pivot on the Honors for Average column. In the pivot table below, Class is in the Row area and the Value field is Honors for Average. “Show Values As” is set to the default of “No Calculation” and, most important, “Summarize Values By” is set to “Average.”

pivot-True only

Then, to finish it up, I changed the title to something more meaningful.

title changed

I think this is a much clearer and more concise way to represent this type of data.

7 thoughts on “Percent of True Items in a Pivot Table Field

  1. Pingback: Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non-MVP) Edition | - Learn Microsoft Excel Online

  2. In the pivot table under the “Clunky way”, the percentages in the Honors True column are different from the percentages in the pivot table under the “Un-Clunky way”.
    Are you sure the Unclunky way does the right thing?

  3. Howdy there Paranam Kid! Thanks for your question.

    Yes, I’m sure it works, and even went back and confirmed it. I must have changed the data slightly between the two ways.

    • In that case, it might be useful to amend the screenshots so they match, otherwise you might confuse the keen reader, or sow a seed of doubt in their mind as to the accuracy of your discourse. It’s your choice 😉

      • I’m glad I get to choose! You are right though. Actually, looking at it I see that there were other problems with the post. Thanks for pointing them out.

        • Doug I have 1 more, less knit-picky question: instead of having a helper column could you not just put 1’s and 0’s in the “Honors” column, with 1 being True & 0 being a blank?

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>