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

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education