Skip main navigation

Changing the Value settings

With Pivot tables, the default values are a sum of the selected fields. This can be changed. In this article, Paula explains more.
Changing The Value Settings
© Paula Guilfoyle
When we add values to our pivot table, Excel’s default value field settings are set to SUM. However, there are often times when you need to find information other than sum. Such as Average, Min, or Count. We can quickly change the summarization to a different aggregation by using Value Field Settings.

To change the value displayed in the pivot table, right-click anywhere on the pivot table, and select Value Field Settings.

From Value Field Settings we can add a Custom Name to our Pivot table.

Value Field Settings

We can also change how the data is summarized. By selecting any of the options available under Summarize Value By, your pivot table values will update.

Value Field Settings

The summarize options have been taken from Excels standard functions and are:

  • Sum
  • Count
  • Average
  • Max
  • Min
  • Product
  • Count Numbers
  • StdDev
  • StdVevp
  • Var
  • Varp

Also, in Value Field Setting you will find Number Format. It is from here that you would select the number formatting type for the entire pivot table. This is very useful when you want to set custom formatting or any other formatting of the values in the pivot table.

format cells

© Paula Guilfoyle
This article is from the free online

How to Use Pivot Tables to Analyse Data in Excel

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now