HOW TO: Use STDEV or STDEVP with a PivotTable in Excel 2000

This step-by-step article describes how to use the STDEV function and the STDEVP function with a PivotTable in Excel 2000.

The PivotTable STDEV and STDEVP functions in Microsoft Excel calculate standard deviation based upon a set of data. You use STDEV when the standard deviation is based upon a sampling of the total data. You use STDEVP when the standard deviation is based upon the entire population of data given as arguments.

The standard deviation functions are a measure of the dispersal of values from the average (the mean). The standard deviation functions provide a useful measure of the consistency of data and can improve reliability in forecasting or trend analysis.

This article provides an example of how to use the STDEVP function in a PivotTable. The data is a fictional rendering of sales data for two quarters over a period of three years. The data is limited to two quarters in a year for the sake of keeping the sample small. The resulting PivotTable returns the deviation for each quarter based upon monthly sales revenue. This result will be returned with STDEVP and further expanded upon by returning the percentage difference in comparison to the same quarter of the previous year.

Calculate the Standard Deviation

To use the STDEVP worksheet function to calculate the standard deviation of a data set, follow these steps:

1. In a new Microsoft Excel worksheet, type the following values:

       A1: Year   B1: Quarter  C1: Month   D1: Net Revenue
       A2: 98     B2: 1        C2: Jan     D2: $514,731
       A3: 98     B3: 1        C3: Feb     D3: $514,850
       A4: 98     B4: 1        C4: Mar     D4: $515,816
       A5: 98     B5: 2        C5: Apr     D5: $516,057
       A6: 98     B6: 2        C6: May     D6: $516,920
       A7: 98     B7: 2        C7: Jun     D7: $517,082
       A8: 99     B8: 1        C8: Jan     D8: $520,945
       A9: 99     B9: 1        C9: Feb     D9: $521,751
      A10: 99    B10: 1       C10: Mar    D10: $522,098
      A11: 99    B11: 2       C11: Apr    D11: $522,921
      A12: 99    B12: 2       C12: May    D12: $523,324
      A13: 99    B13: 2       C13: Jun    D13: $523,594
      A14: 00    B14: 1       C14: Jan    D14: $527,254
      A15: 00    B15: 1       C15: Feb    D15: $527,431
      A16: 00    B16: 1       C16: Mar    D16: $528,162
      A17: 00    B17: 2       C17: Apr    D17: $528,851
      A18: 00    B18: 2       C18: May    D18: $528,996
      A19: 00    B19: 2       C19: Jun    D19: $529,536
   					

2. Select any cell in the table.
3. On the Data menu, click PivotTable and PivotChart Report.
4. In step 1 of the wizard, accept the defaults, and then click Next.
5. In step 2 of the wizard, ensure that the Range is $A$1:$D$19, and then click Next.
6. In step 3 of the wizard, click Layout.
7. Drag the QUARTER field (button) to the PivotTable section labeled ROW.
8. Drag the YEAR field to the PivotTable section labeled COLUMN.
9. Drag the NET REVENUE field to the PivotTable section labeled DATA.
10. Again, drag the NET REVENUE field to the PivotTable section labeled DATA.
    
    SUM OF NET REVENUE and SUM OF NET REVENUE2 field boxes appear in the DATA section.
11. Double-click the SUM OF NET REVENUE field box.
    
    The PivotTable Field dialog box opens.
12. In the Name box, type DEVIATION FROM PREVIOUS YEARS QUARTER.
13. In the Summarize by list, click StdDevp. Click Options.
14. In the Show data as list, click % Difference From.
15. In the Base field box, click Quarter, and then click (previous) in the Base item box.
16. Click Number, type 0 in the Decimal places box, and then click OK twice.
17. Double-click the SUM OF NET REVENUE2 field box.
    
    The PivotTable Field dialog box opens.
18. In the PivotTable Field dialog box, type QUARTERLY REVENUE in the Name box, and then click OK.
19. Click OK to return to step 3 of the wizard, and then click Finish. The PivotTable includes the standard deviation data.

Analyze the Results

In this example, the STDEVP is a reflection of the deviations from the average of revenue based upon the underlying monthly data of a quarter. The data used includes all of the data, not just a sampling; this is why you use the STDEVP worksheet function instead of STDEV.

In this example, you compare deviations of the same quarter from a previous year by using the % Difference From option. Excel performs an STDEVP function on all quarters. However, when you use the % Difference From option, there is no previous quarter to compare to the first quarter. Therefore, the first quarter of each year is blank.

For more information about using PivotTables to summarize date, click Microsoft Help on the Help menu, type summarize and calculate data in a pivottable or pivotchart report in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.