Excel Tutorial: Changing the Function of a Pivot Table

Last week we told you how to create a pivot table in Microsoft Excel. This week we expand your pivot table knowledge with a new tutorial: changing the function of a pivot table.

Changing the Function of a Pivot Table

When fields are placed in the Value area of a Pivot Table an assumption is made that a numeric field is to be added and a text field is to be counted, as shown below.

Excel tutorial Changing the function of a pivot table 1

There are several functions you can use in a Pivot Table, Sum, Average, Max, Min, and Count. If you wish to change the function, click on the arrow to the right of the field, in the values box, and choose Value Field Settings.

Excel tutorial Changing the function of a pivot table 2

Select the appropriate function from the Summarize box and click on OK.

Excel tutorial Changing the function of a pivot table 3

Using more than one Function 

Perhaps you would like to sum and average the data.

In the example shown, we would drag the Salary field a second time into the Values area.  Notice the Column box, also shows Values, this is to indicate that the answer is to be shown in a Column.

Excel tutorial Changing the function of a pivot table 4

Set the Value Field Settings for the second field to Average.

Excel tutorial Changing the function of a pivot table 5

Finish it all off by changing the Column Headings to explain the data being viewed. Just type on top of them.

Excel tutorial Changing the function of a pivot table 6

Pivot tables are covered in depth as part of our Microsoft Excel courses so call us today on 01273 622272 to find out about our range of Excel courses or visit the Excel blog for more hints and tips.

Tags

, , , , ,
Andy Trainer


Connect with Andy on Google.

View all posts by Andy Trainer

Leave a Comment