How to Add a Standard Calculation to an Excel Pivot Table

By Stephen L. Nelson, E. C. Nelson

This Excel pivot table shows coffee sales by state for an imaginary business that you can pretend that you own and operate. The data item calculated in this pivot table is sales. Sometimes, sales might be the only calculation that you want made. But what if you also want to calculate average sales by product and state in this pivot table?

image0.jpg

To do this, right-click the pivot table and choose Value Field Settings from the shortcut menu that appears. Then, when Excel displays the Value Field Settings dialog box, select Average from the Summarize Value Field By list box.

image1.jpg

Now assume, however, that you don’t want to replace the data item that sums sales. Assume instead that you want to add average sales data to the worksheet. In other words, you want your pivot table to show both total sales and average sales.

Note: If you want to follow along with this discussion, start over from scratch with a fresh copy of the worksheet.

To add a second summary calculation, or standard calculation, to your pivot table, drag the data item from the PivotTable Field list box to the Σ Values box. This example shows how the roast coffee product sales by state pivot table looks after you drag the sales data item to the pivot table a second time.

You may also need to drag the Σ Values entry from the Columns box to the Row box. (See the Columns and Rows boxes at the bottom of the PivotTable Field List.)

image2.jpg

After you add a second summary calculation — this shows as the Sum of Sales $2 data item — right-click that data item, choose Value Field Settings from the shortcut menu that appears, and use the Value Field Settings dialog box to name the new average calculation and specify that the average calculation should be made. You can see how the Value Field Settings dialog box looks when you make these changes.

image3.jpg

This pivot table now shows two calculations: the sum of sales for a coffee product in a particular state and the average sale. For example, in cell B6, you can see that sales for the Best Blend of the Arabia coffee are $6,186 in California. And in cell B7, the pivot table shows that the average sale of the Best Blend of Arabia coffee in California is $476.

image4.jpg

If you can add information to your pivot table by using a standard calculation, that’s the approach you want to take. Using standard calculations is the easiest way to calculate information, or add formulas, to your pivot tables.