Let’s take a look. Here we have the product sales data we’ve looked at previously, with an empty pivot table, ready to use. Let’s start off by adding Product as a row label. Now let’s add Total Sales as a Value, and set the format to Accounting. By default, numeric fields that are placed into the values area are summed, so we now have the total sales summed per product. Notice that the field is labeled “Sum of Total Sales” both in the pivot table and in the Values area of the Field List. However, we can easily change from Sum to one of several other math functions. One way to do this is to select one of the values in the pivot table, and navigate to the Options tab in the Pivot Table Tools area. There, you’ll see an item called Summarize Values By. This menu contains 6 basic math functions, including SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, as well as other options like Product and Standard Deviation. Using this menu, we can switch to COUNT. Or AVERAGE. Or MAX. Notice that the label for the field changes in each case. Another way to change the math for a value field is to right-click a value in the table and use the menu called Summarize Values By. This gives you the same options available on the ribbon. Finally, you can reach these same options by accessing the Field Settings dialog, using one of the methods we looked at earlier. When you place a text field into the Values area, you’ll get the count of that field, instead of a sum. For example, if we add the Product field as a value, we get a count of each record that contains a value for product. Because each row in the source data is an order, the product table now shows orders per product. Note that Excel will allow you to change the function used for text values, but the result is not useful. When you are working with text values, you’ll want to use the COUNT function.
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.
