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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
