How to add a field to a pivot table more than once

There may be times when you want to add the same field to a Pivot Table more than once. For example, you might want to show a sum with a count, or a sum with a percentage.

Let’s take a look.

Let’s start off by adding Product as a Row Label. Then let’s add Total Sales as a Value. As usual, we get the sum of Total Sales. If we add Total Sales again to the Values area, we get two instances of the field, both summed. We can now change the 2nd instance to Count instead of Sum, and change the field names to something shorter and more meaningful.

We can even add the same field again, and set the math function to Average.

This same approach is useful in cases where you’d like to show a value field in a different way, for example, as a percent of total.

Let’s remove the second and third instances of Total Sales and build a percent of Total View.

We’ll add Total Sales back as a sum, and this time, let’s change Show Value As to Percent of Total. Now we have Total Sales displayed as both a sum and a percentage.

As before, we can rename this field to something shorter and more meaningful.

It’s worth noting that you can’t actually add a field more than once to a column or row label area. For example, if we try to add the Product field to the row labels area again, nothing happens.

If we try to add it to the column label area, it’s actually moved from Row Labels to Column Labels. And if we add it again to Row Labels, it’s moved back.