How to Change a Calculation in a Pivot Table in Microsoft Excel 2007

Learn how to change calculations in a Pivot Table.

Now, instead of calculated items or calculated fields, we can also just change the calculation that Excel is giving us in the pivot table. As an example, I am going to remove the region field and add the customer and product field. When customers are going down the side, I want products going across the top so I will drag those on the column labels. Now, you will notice that in cell A3, it says that we are looking at the sum of Revenue. Every pivot table that we have created so far in this lesson has been showing us the sum of revenue. It turns out that we have control over that calculation. You need to select one cell that contains Revenue and then choose the field settings button on the ribbon. You will notice that we have choices here that show a sum, account, an average, max, min, and product. There is also standard deviation and variance if you would scroll down. Another very interesting option is on the show values as tab. Right now, Excel is showing us the values as normal. There are several options in that dropdown including percentage of row, click okay. In this case, Excel will replace our revenue numbers with percentages that total across to a 100%. This allows you for any particular customer to see what percentage of product mix they buy. Other settings and field settings include percentage of column. In this case, for each product, for example B713 in column C, you can see which customers buy a certain percentage of that product. These calculations will total a 100% along the bottom. I am going to remove customer from the pivot table and put date along the left hand side, go back into field settings and change the running total in. I want to show a running total in the date field and now instead of percentages, I will see cumulative year to date figures.