Changing PivotTable Calculations using the Value Field Settings

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

You will always find Excel either summarizing your data by adding the values together (SUM) or Counting the number of items. Nobody is stopping you from changing the type of calculation that your work demands to include averages, maximum, minimum, standard deviation and others.

img 5a009826d31d6 - Changing PivotTable Calculations using the Value Field Settings

Change the calculation from the Value Field settings.

  1. Add a column to your PivotTable by dragging a field from the list to the Columns section (as usual)
  2. Excel will make a new column using either the Sum or Count calculation depending on the source values.
  3. Click on the selector next to the new column item and choose Value Field Settings …

    img 5a00983dc7446 - Changing PivotTable Calculations using the Value Field Settings

  4. Summarize value field by … and choose the calculation you want. We’ve chosen ‘Min’ or minimum.

    img 5a00985740e6b - Changing PivotTable Calculations using the Value Field Settings

  5. If necessary, click ‘Number Format’ to change the way the values look.

Change an existing PivotTable field

To change an existing field / column use the steps above or access the Value Field settings by right-clicking on any cell in the column.

img 5a009872421ad - Changing PivotTable Calculations using the Value Field Settings

Summarize / Calculation options

  • Sum
  • Count
  • Average – mean
  • Max – maximum / highest value
  • Min – minimum / lowest value
  • Product – multiplies all the values together.
  • Count Numbers – counts only if the value is numeric. Blank, empty, text or error cells are not counted.

Standard Deviation and Variance

The last four options help to show the amount of variance or deviation between the values.  The higher the Standard Deviation or Variance, the wider spread of the source values.

  • StdDev – Standard Deviation, same as function S
  • StdDevp – same as function P
  • Var – Variance, same as function VAR.S
  • Varp – same as function VAR.P
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address