Std Dev in Excel Pivot Tables | MyExcelOnline

It is very easy to use a Pivot Table to summarize data for you by giving the sum, count, or average. But did you know it can give you the standard deviation and variance as well? This is crucial if you want to understand how volatile your data is!

Let us explore how to get the std dev in Pivot Table, this shows the amount by which members of a group are different from the average value for the group.

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


STEP 1: Here is our Pivot Table. Drag UNITS SOLD to the Values Area

Std Dev in Excel Pivot Tables | MyExcelOnline

STEP 2: This will default to Sum of UNITS SOLD. Let us change that by clicking on the arrow and selecting Value Field Settings

Std Dev in Excel Pivot Tables | MyExcelOnline

STEP 3: Select StdDevp and click OK.

We will use the StdDevp function as we have the complete data (population) used in the calculation. When only a portion of the data is used, then StdDev should be used instead.

Std Dev in Excel Pivot Tables | MyExcelOnline

Now you have your Standard Deviation! You can see for ICE CUBES on the month of June the Standard Deviation is quite low. This means that the values on the units sold for that month is fairly close to the average of 34,739.

std dev in Pivot Table

Did you know that the square of the standard deviation will give you the variance value? Click here to know more!

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn