Article ID: 151551 - View products that this article applies to.
This article was previously published under Q151551
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.
Example 1To create the first example in which you use a PivotTable to analyze the responses given to several Yes, No, Undecided questions, follow these steps:
Microsoft Excel 5.0 and 7.0In 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.
Microsoft Excel 97 and Microsoft Excel 98In 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. 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.
Example 2To create the second example and use a PivotTable to analyze responses to product satisfaction questions, follow these steps:
Microsoft Excel 5.0 and 7.0In 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.
Microsoft Excel 97 and Microsoft Excel 98
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. 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.
Excel 97 or Excel 98 Macintosh EditionFor 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:
Microsoft Excel 7.0For more information about PivotTables in Microsoft Excel version 7.0, on the Help menu, click Answer Wizard, and then type the following:
Microsoft Excel 5.0For more information about PivotTables in Microsoft Excel version 5.0, on the Help menu, click Search for Help On, and then type:
Article ID: 151551 - Last Review: August 17, 2005 - Revision: 1.3
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Contact us for more help