How to Show the Average of Data in a Pivot Table

Display the average of data in a pivot table by changing the Value Field Setting.

Excel pivot tables let you quickly summarize, group and filter large quantities of data. This Excel functionality is useful when you need to view a segment of your data. Once the data is placed into a pivot table, the information can be quickly summarized. Instead of using an addition summary, pivot tables give you the option of displaying other summaries like averages, products, variances, counts, minimums and maximums.

Step 1

Open Excel 2007 and select a workbook with data you want displayed in a pivot table. Select the "Office" button and click "Open." Browse your computer for the workbook and click it. Select "Open." The workbook opens to display the data in the spreadsheet.

Step 2

Select the worksheet that contains the data you want summarized. Select all of the data in the worksheet by pressing "Control" and "A" on your keyboard. Select the "Insert" tab and select "Pivot Table." Select "Pivot Table" again. The Create Pivot Table appears. In the "Choose the data that you want analyze" option, select the option for "Select a table or range." Notice the entire data range is selected. Select "New Worksheet" in the "Choose where you want the Pivot Table report to be placed." Click "OK." A blank pivot table appears.

Step 3

Add fields to your pivot table by clicking on the checking the box near the field name in the "Pivot Table Field List." The fields will appear in the "Row Labels" section of your pivot table. Move these fields to the "Column Labels" to display the data vertically instead of horizontally.

Step 4

Place a check in the field that you want to see averaged. The field will appear in the "Row Labels" of the "Pivot Table Field List." Drag this field to the "Sum Values" section. Click on the drop down arrow on the field and select "Value Field Setting." Select "Average" and click "OK." The pivot table is updated to display the average values for the fields selected.