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:

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.

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.”

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

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