XL: How to Use STDDEV or STDDEVP with a PivotTable

ID: Q152984

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

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:

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

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.

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.

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.

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.

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

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.

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.

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)."

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

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

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

In Step 3 of 4, click Next.

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