ID: Q151551
The information in this article applies to:
PivotTables are analysis tools that you may find helpful in summarizing data from a questionnaire or survey.
If you create a table of survey results in Microsoft Excel, a PivotTable is useful when the answers are Yes, No, and Undecided. This is also true when answers are in the form of a range of values, for example, a scale in which 9 is the highest value and 1 is the lowest value. In both cases, you may want to find the percentage of answers to the overall total responses.
This article provides two examples that demonstrate how a PivotTable in Microsoft Excel can help to summarize the results of a survey.
To create the first example in which you use a PivotTable to analyze the responses given to several Yes, No, Undecided questions, follow these steps:
1. In a new Microsoft Excel worksheet, enter the following values:
A1: Question B1: Rating
A2: 1 B2: Yes
A3: 1 B3: Undecided
A4: 1 B4: No
A5: 1 B5: No
A6: 1 B6: No
A7: 2 B7: Yes
A8: 2 B8: Yes
A9: 2 B9: Undecided
A10: 2 B10: Undecided
A11: 2 B11: No
A12: 3 B12: Yes
A13: 3 B13: Yes
A14: 3 B14: Undecided
A15: 3 B15: No
2. Click in any of the cells in the table. On the Data menu, click
PivotTable (in Microsoft Excel 97, click PivotTable Report).
3. The PivotTable Wizard - Step 1 of 4 dialog box lists options for the
source of the PivotTable data. Make sure "Microsoft Excel List or
Database" is selected, and then click Next.
4. The PivotTable Wizard - Step 2 of 4 dialog box displays the range of
the data. Microsoft Excel may select the correct range ($A$1:$B$15 in
the example). If the select range is incorrect, type or select the
range, and then click Next.
5. The PivotTable Wizard - Step 3 of 4 dialog box displays the basic
PivotTable structure. Outside and to the right of the structure, the
field names from the data range and the question and rating
information is listed. Click the Question field box. Click and drag
the field box to the PivotTable section labeled ROW.
6. Next, drag the Rating field box to the PivotTable section labeled
COLUMN.
7. For the fields outside the PivotTable structure, drag the Rating field
to the Data section.
The Rating field is still in the COLUMN section, and a "Count of
Rating" field is in the DATA section.
8. Click the "Count of Rating" field box in the Pivot Table DATA section.
Double-click the "Count of Rating" field box to open the PivotTable
Field dialog box.
9. In the Name box, replace "Count of Rating" with "Percentage of
Rating." Click Options, and under Show Data As, click "% of row."
Click OK.
To complete the PivotTable, use the following appropriate method for your version of Microsoft Excel.
In the PivotTable Wizard - Step 4 of 4 dialog box, under PivotTable Starting Cell, click cell $D$1. If you do not specify a cell, a new worksheet is created with the PivotTable. Clear "Grand Totals for Columns" and click Finish.
In the PivotTable Wizard - Step 4 of 4 dialog box, click Existing Worksheet to create a PivotTable on an existing worksheet. In the box beneath Existing Worksheet, type "$D$1" (without the quotation marks). Click Options. Clear "Grand Totals for Columns" and click OK. Click Finish.
The resulting PivotTable contains the percentage of Yes, No, and Undecided answers for each of the three questions reported in the table. Note that of the responses to question 1, 60 percent of the total questioned replied No, 20 percent replied Yes, and 20 percent replied Undecided. The same kind of breakdown is provided for questions 2 and 3.
To create the second example and use a PivotTable to analyze responses to product satisfaction questions, follow these steps:
1. In a Microsoft Excel worksheet, enter the following values:
A1: Product_ID B1: Rating
A2: Product1 B2: 9
A3: Product1 B3: 9
A4: Product1 B4: 9
A5: Product1 B5: 8
A6: Product1 B6: 8
A7: Product1 B7: 8
A8: Product1 B8: 6
A9: Product2 B9: 6
A10: Product2 B10: 3
A11: Product2 B11: 3
A12: Product2 B12: 3
A13: Product2 B13: 3
A14: Product2 B14: 2
A15: Product2 B15: 1
2. Click any of the cells in the table. On the Data menu, click
PivotTable (in Microsoft Excel 97, click PivotTable Report).
3. In the PivotTable Wizard - Step 1 of 4 dialog box, make sure
"Microsoft Excel List or Database" is selected, and then click Next.
4. In the PivotTable Wizard - Step 2 of 4 dialog box, the range should be
$A$1:$B$15. Click Next.
5. In the PivotTable Wizard - Step 3 of 4 dialog box, drag the Product_ID
field box that is right of the basic PivotTable structure to the
COLUMN section of the PivotTable. Drag the Rating field box to the ROW
section. Then, drag the Rating field box to the DATA section. This
field is displayed as "Sum of Rating" in the data section.
6. Double-click the "Sum of Rating" field box in the DATA section to
open the PivotTable Field dialog box. In the Name box, change "Sum
of Rating" to "Percentage of Rating." Under Summarize By, click Count.
7. Click Options and click "% of column" under Show Data As. Click OK.
8. Click Number to open the Format Cells dialog box. Under Category,
click Percentage. Under Decimal Places, change the option to 0 (zero),
and then click OK.
9. In the PivotTable Field dialog box, click OK.
To complete the PivotTable, use the following appropriate method for your version of Microsoft Excel.
In the PivotTable Wizard - Step 4 of 4 dialog box, first click in the PivotTable Starting Cell box. Then, click cell $D$1. Click to clear the option for "Grand Totals for Rows," and then click Finish.
In the PivotTable Wizard - Step 4 of 4 dialog box, click Existing Worksheet to create the PivotTable on the existing worksheet. In the edit box beneath Existing Worksheet, type "$D$1" (without the quotation marks). Click Options. Click to clear the "Grand Totals for Rows" check box and click OK. Click Finish.
The resulting PivotTable contains the percentage that each rating received per product in the data table. When you view the PivotTable, it is easy to analyze the highest percentage rating. Furthermore, you can analyze the results to determine the overall response to the products. With Product1 in the example, ratings 8 and 9 both share a 43 percent return of the total responses for that product. This indicates a generally highly favorable response to the product. Product2 is seen unfavorably with a rating of 3, which receives an overwhelming 57 percent of the responses to Product2.
For more information about PivotTables in Microsoft Excel 97 or Microsoft Excel 98, on the Help menu, click Microsoft Excel Help, type the following and click Search:
pivottables
For more information about PivotTables in Microsoft Excel version 7.0, on the Help menu, click Answer Wizard, and then type the following:
pivottables
For more information about PivotTables in Microsoft Excel version 5.0, on the Help menu, click Search for Help On, and then type:
pivottables
Keywords : kbualink97 xlpivot
Version : MACINTOSH:5.0,5.00a,98; WINDOWS:5.0c,7.0,7.0a,97
Platform : MACINTOSH WINDOWS
Issue type : kbinfoLast Reviewed: January 8, 1999