Standard Deviation in Pivot Tables in Excel

by

When dealing with numerical data in Excel, you might need to calculate the standard deviation within a Pivot Table.

The standard deviation is a statistical calculation that measures the degree of variation or scattering in data points.

A smaller standard deviation means the data points are closer to the mean, while a larger standard deviation indicates greater variability in the data.

This tutorial shows two techniques for getting standard deviation in Pivot Tables in Excel.

Method #1: Using the ‘Value Field Settings’ Dialog Box to Get the Standard Deviation in Pivot Table

In Excel, the “Value Field Settings” dialog box provides customization options for the calculations and summarization carried out on a value field within a Pivot Table.

We can use the “Value Field Settings” dialog box to change the calculation in a Pivot Table to standard deviation.

One way to access the “Value Field Settings” dialog box is through the “PivotTable Fields” task pane, which is used to create, modify and manage Pivot Tables.

Let’s consider the following Pivot Table showing the sum of the scores of members of two teams on a test:

Example pivot table

Note When you create a Pivot Table with numerical data, the default summary calculation is usually “Sum,” as seen in the example above.

We want to use the Pivot Tables Fields task pane to change the default sum calculation in the Pivot Table to standard deviation.

We use the below steps:

  1. Click any cell on the Pivot Table to activate the “PivotTable Fields” task pane on the right side of the Excel window.
activate the PivotTable Fields task pane

Notice the “Values” section at the bottom of the task pane, currently showing “Sum of Score.”

Alternatively, you can activate the “PivotTable Fields” task pane by clicking the “Field List” button on the “Show” group of the “PivotTable Analyze” contextual tab that appears when you select any cell on the Pivot Table.

Click on the field list icon to activate the pivot table field task pane
  1. Click the down arrow on the “Sum of Score” button on the “Values” section of the “PivotTable Fields” task pane and click “Value Field Settings” on the shortcut menu that appears.
Click on value field settings option

Once you click the “Value Field Settings” option, the following “Value Field Settings” dialog box appears in the Excel window:

Value field settings dialog box

Alternatively, you can open the “Value Field Settings” dialog box by right-clicking the Pivot Table and selecting “Value Field Settings” on the context menu.

Right click and click on value field settings option
  1. On the “Value Field Settings” dialog box, open the “Summarize Values By” tab, scroll down the “Summarize value field by” list box,  select “StdDevp,” and click “OK.”
Select the StdDevp option

Note: We have selected the “StdDevp” function because we have data for the entire population of members from both teams, and we want to determine variability within that population. If we had data from a sample of the team members, we would use the “StdDev” function on top of the “StdDevp” function on the list box.

After completing the above steps, we get the standard deviation of the scores of the two teams:

Standard deviation shown in pivot table

Note: By default, Excel applies the general number format to the calculated standard deviation. You can change the formatting by selecting the cells containing the standard deviation values, right-clicking, and choosing “Format Cells” from the context menu.

Click on the format cells option

In the “Format Cells” dialog box, select the desired format for the standard deviation values and click “OK.”

Change the format in the format cells dialog box

The standard deviation values for the two teams will be displayed in the chosen format:

Format in pivot table has been changed
Also read: Change Count to Sum in Excel Pivot Table

Method #2: Use VBA Code to Get the Standard Deviation in Pivot Tables

We can use the Excel VBA code to calculate the standard deviation in Pivot Tables in Excel.

This method is convenient if you want to calculate the standard deviation in many Pivot Tables.

Suppose we have the following Pivot Table showing the sum of the scores of members of two teams on a test:

Example pivot table

We want to apply Excel VBA code to change the summary calculation in the Pivot Table to standard deviation.

We use the following steps:

  1. Press the “Alt + F11” shortcut to launch the Visual Basic Editor.
  2. Open the “Insert” menu and choose “Module” to insert a module in the Visual Basic Editor.
Insert a module
  1. Copy the following Excel VBA code and paste it into the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub StdDevonValueFields()
    Dim ptb As PivotTable
    Dim pfd As PivotField
    Dim wsh As Worksheet

    Set wsh = ActiveSheet
    Set ptb = wsh.PivotTables(1)
    Application.ScreenUpdating = False

    ptb.ManualUpdate = True
    For Each pfd In ptb.DataFields
        pfd.Function = xlStDevP
    Next pfd
    ptb.ManualUpdate = False

    Application.ScreenUpdating = True
    Set pfd = Nothing
    Set ptb = Nothing
    Set wsh = Nothing
End Sub

The code should appear in the code window as depicted below:

Copy paste the code in the vba code module
  1. Save the file as an “Excel Macro-Enabled Workbook (*.xlsm).”
  2. Press the “Alt + F11” shortcut to switch to the active worksheet containing the Pivot Table.
  3. Press “Alt + F8” to open the “Macro” dialog box, select the “StdDevonValueFields” macro on the macro list, and click “Run.”
Select the macro and run the code

The sub-procedure is executed, and we get the standard deviation of the scores of the teams in the Pivot Table as shown below:

Standard deviation shown in pivot table

Explanation of the Code

The code in this method sets the aggregation function of all data fields in the first Pivot Table of the active sheet to calculate the standard deviation.

It optimizes performance by temporarily turning off screen updating and manual updating of the Pivot Table.

Here’s the breakdown of the code:

  • Declare variables:
    • ‘ptb’ (PivotTable): This variable stores the PivotTable object.
    • ‘pfd’ (PivotField): This variable stores the PivotField object.
    • ‘wsh’ (Worksheet): This variable stores the active worksheet.
  • Set the active sheet:
    • ‘Set wsh = ActiveSheet’: Assigns the currently active sheet to the ‘wsh’ variable.
  • Set the PivotTable:
    • ‘Set ptb = wsh.PivotTables(1)’: Assigns the first Pivot Table in the active sheet to the ‘ptb’ variable.
  • Turn off screen updating:
    • ‘Application.ScreenUpdating = False’: Temporarily turns off screen updating to improve performance.
  • Enable manual update for the Pivot Table:
    • ‘ptb.ManualUpdate = True’: Enables manual updating for the Pivot Table to prevent automatic recalculation after each change.
  • Iterate through each data field in the Pivot Table:
    • ‘For Each pfd In ptb.DataFields’: This loop iterates through each PivotField object in the Pivot Table’s data fields collection.
  • Set the function of each data field to standard deviation:
    • ‘pfd.Function = xlStDevp’: Sets the aggregation function for the current PivotField to calculate the standard deviation. Note: Use the “StdDevp” function if you have data for the entire population. If you have sample data, use the “StdDev” function.
  • Turn off the manual update for the Pivot Table and recalculate:
    • ‘ptb.ManualUpdate = False’: Turns off the manual updating and triggers recalculation of the Pivot Table.
  • Enable screen updating:
    • ‘Application.ScreenUpdating = True’: Enables screen updating, restoring the normal display behavior.
  • Reset and release variables:
    • ‘Set pfd = Nothing’: Resets the ‘pfd’ variable to release the allocated memory.
    • ‘Set ptb = Nothing’: Resets the ‘ptb’ variable to free allocated memory.
    • ‘Set wsh = Nothing’: Resets the ‘wsh’ variable to free allocated memory.
Also read: How to Group by Months in Excel Pivot Table?

STDEV vs STDEVP in Pivot Tables

The key difference between STDEV and STDEVP lies in the way they treat the data.

STDEV is used for analyzing a sample of the population, while STDEVP is used for analyzing the entire population.

Therefore, your choice of metric depends on the sample size:

  • If you are working with a small sample (less than 30) and want to infer conclusions about the entire population, use STDEV (sample standard deviation).
  • If your dataset represents the whole population, you should use STDEVP (population standard deviation).

Keep in mind that using the incorrect function can lead to inaccurate results.

Understanding Bias when Calculating Standard Deviation

Another factor to consider when choosing between STDEV and STDEVP is the potential for bias.

Bias occurs when a sample deviates from the population mean. To account for this deviation, the STDEV function uses the sample mean and an n-1 denominator, where n represents the sample size.

This adjustment effectively reduces the chance of biased results.

With large sample sizes, the difference between STDEV and STDEVP becomes negligible.

However, it’s still good practice to use the appropriate function based on your data:

  • Use STDEV if your objective is to minimize bias when working with sample data.
  • Choose STDEVP when dealing with the entire population, as the population mean is considered unbiased.

This tutorial has explained two techniques for getting the standard deviation in Pivot Tables in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment