XL: How to Use STDDEV or STDDEVP with a PivotTable

ID: Q152984

The information in this article applies to:

SUMMARY

The PivotTable STDDEV or STDDEVP functions provide standard deviation statistics against data. STDDEV is best used when data is a sampling of the overall data. STDDEVP, on the other hand, is for use when the information included is all the data (entire population). 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 ensure reliability in forecasting or trend analysis.

This article provides an example of using the STDDEVP function in a Pivot Table. 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 will return the deviation for each quarter based upon monthly sales revenue. This result will be returned with STDDEVP and further expanded upon by returning the percentage difference in comparison to the same quarter of the previous year.


MORE INFORMATION

The following steps provide an example:

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


  2. Click in any of the cells in the table and click PivotTable on the Data menu. In Microsoft Excel 97 or in Microsoft Excel 98 Macintosh Edition, click PivotTable Report on the Data menu.


  3. In Step 1 of 4 of the PivotTable Wizard, options are listed for the source of the PivotTable data. "Microsoft Excel list or database" should be the default option selected. Click Next.


  4. Step 2 of 4 displays the range of the data. Microsoft Excel may have the correct range already selected, $A$1:$D$19. If this is not the case, you can either type in the range, or you can use the pointer to select the range. Click Next.


  5. Step 3 of 4 displays the basic PivotTable structure and outside of the structure to the right, the field names from the data range: YEAR, QUARTER, MONTH, and NET REVENUE. Click the QUARTER field box and drag the field to the PivotTable section labeled ROW.


  6. Drag the YEAR field box to the PivotTable section labeled COLUMN.


  7. Drag the NET REVENUE field to the PivotTable section labeled DATA. Again, drag the NET REVENUE field to the PivotTable section labeled DATA.

    A SUM OF NET REVENUE and SUM OF NET REVENUE2 field box should be in the DATA section.


  8. Move the pointer over the SUM OF NET REVENUE field box in the Pivot Table DATA section. Double-click the SUM OF NET REVENUE field box. This step opens the PivotTable Field dialog box.


  9. In the Name box, replace SUM OF NET REVENUE with DEVIATION FROM PREVIOUS YEARS QUARTER. In the Summarize By list, click StdDevp. Click Options, and in the Show Data As list, click % Difference From. In the Base Field box, click Quarter, and in the Base Item box, click "(previous)."


  10. Click Number. In the Decimal Places box, change the decimal places to 0 (zero), and then click OK.


  11. In Step 3 of 4, double-click the SUM OF NET REVENUE2 field box. This step opens the PivotTable Field dialog box.


  12. In the PivotTable Field dialog box, change the name of SUM OF NET REVENUE2 to QUARTERLY REVENUE, and then click OK.


  13. In Step 3 of 4, click Next.


  14. In Step 4 of 4, click Finish.


Analyzing the Results

The PivotTable now reflects the desired result. In this scenario, the STDDEVP is a reflection of the deviations from the average of revenue based upon the underlying monthly data of a quarter. The quarter reflects the whole revenue amount and not a sampling, hence the use of STDDEVP over STDDEV. The result is taken a step further by performing a comparison of deviations of the same quarter from a previous year, accomplished by using the % Difference From option. Microsoft Excel performs a STDDEVP function on all quarters. However, when using the % Difference From option, there is no previous quarter to the first quarter for comparison. Therefore, the first quarter of each year is blank.

When creating the PivotTable, there is a relatively easy aspect to determining what it will be displayed. In this PivotTable and all PivotTables, Step 3 of 4 in the PivotTable Wizard allows you to visualize the result. The page, row, and column fields will, by default, always present a single instance of every unique item in the data table field item supplied. The PivotTable Data Field will summarize the fields placed there by indexing the page, row, and column fields for the appropriate data.


REFERENCES

Microsoft Excel 7.0

For more information about PivotTable functions in Microsoft Excel, click Answer Wizard on the Help menu and type:

pivot table summary functions

Microsoft Excel 5.0

For more information about PivotTable functions in Microsoft Excel, click the Search button in Help and type:

Pivottable field command (data menu)

Additional query words: 5.00a 5.00c 97 XL97 8.00 98 XL98 


Keywords          : kbualink97 xlpivot 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS 
Issue type        : kbhowto

Last Reviewed: July 30, 1999