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:
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:
- Click any cell on the Pivot Table to activate the “PivotTable Fields” task pane on the right side of the Excel window.
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 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.
Once you click the “Value Field Settings” option, the following “Value Field Settings” dialog box appears in the Excel window:
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.
- 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.”
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:
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.
In the “Format Cells” dialog box, select the desired format for the standard deviation values and click “OK.”
The standard deviation values for the two teams will be displayed in the chosen format:
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:
We want to apply Excel VBA code to change the summary calculation in the Pivot Table to standard deviation.
We use the following steps:
- Press the “Alt + F11” shortcut to launch the Visual Basic Editor.
- Open the “Insert” menu and choose “Module” to insert a module in the Visual Basic Editor.
- 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:
- Save the file as an “Excel Macro-Enabled Workbook (*.xlsm).”
- Press the “Alt + F11” shortcut to switch to the active worksheet containing the Pivot Table.
- Press “Alt + F8” to open the “Macro” dialog box, select the “StdDevonValueFields” macro on the macro list, and click “Run.”
The sub-procedure is executed, and we get the standard deviation of the scores of the teams in the Pivot Table as shown below:
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: