Microsoft KB Archive/213920

= HOW TO: Analyze a Questionnaire or Survey with a PivotTable in Excel 2000 =

Article ID: 213920

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213920



For a Microsoft Excel 98 and earlier version of this article, see 151551.

IN THIS TASK
SUMMARY
 * How to Analyze Yes, No, and Undecided Survey Results
 * How to Analyze Quantitative Survey Results

REFERENCES



SUMMARY
This step-by-step article shows you how to analyze a questionnaire or survey with a PivotTable. 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 2000, a PivotTable is useful when the answers are Yes, No, and Undecided, 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), or some other quantifiable result. In these 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.

back to the top

How to Analyze Yes, No, and Undecided Survey Results
To create the first example in which you use a PivotTable to analyze the responses given to several Yes, No, and Undecided questions, follow these steps:   In a new Microsoft Excel worksheet, type 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                     Click in any of the cells in the table. On the Data menu, click PivotTable and PivotChart Report. In the PivotTable and PivotChart Wizard - Step 1 of 3 dialog box, click Microsoft Excel list or database, and then click Next. The PivotTable and PivotChart Wizard - Step 2 of 3 dialog box displays the range of the data. Microsoft Excel may select the correct range ($A$1:$B$15 in the example). If the range is incorrect, type A1:B15 in the Range box, and then click Next. In The PivotTable and PivotChart Wizard - Step 3 of 3 click Layout to display 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 are listed. Click the Question field box. Click and drag the field box to the PivotTable section labeled ROW. Next, drag the Rating field box to the PivotTable section labeled COLUMN.</li> 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.</li> 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.</li> In the Name box, replace "Count of Rating" with "Percentage of Rating." Click Options, and under Show data as, click % of row. Click OK twice.</li> In the PivotTable and PivotChart Wizard - Step 3 of 3 dialog box, click Existing worksheet to create a PivotTable on an existing worksheet. In the box beneath Existing worksheet, type D1, and then click Options. Click to clear the Grand totals for columns check box and click OK. Click Finish.</li></ol>

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.

back to the top

How to Analyze Quantitative Survey Results
To create the second example and use a PivotTable to analyze responses to product satisfaction questions, follow these steps: <ol>  In a Microsoft Excel worksheet, type the following values: <pre class="fixed_text">  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 </li> Click in any of the cells in the table. On the Data menu, click PivotTable and PivotChart Report.</li> In the PivotTable and PivotChart Wizard - Step 1 of 3 dialog box, click Microsoft Excel list or database, and then click Next.</li> The PivotTable and PivotChart Wizard - Step 2 of 3 dialog box displays the range of the data. Microsoft Excel may select the correct range ($A$1:$B$15 in the example). If the range is incorrect, type A1:B15 in the Range box, and then click Next.</li> In The PivotTable and PivotChart Wizard - Step 3 of 3 dialog box, click Layout. Drag the Product_ID field box that is to the right of the basic PivotTable structure to the COLUMN section of the PivotTable. Drag the Rating field box to the ROW section. Next, drag the Rating field box to the DATA section. This field is displayed as Sum of Rating in the DATA section.</li> 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.</li> Click Options, and then click % of column under Show data as.</li> Click Number to open the Format Cells dialog box. Under Category, click Percentage. In the Decimal places box, type 0 (zero), and then click OK twice.</li> In the PivotTable and PivotChart Wizard - Layout dialog box, click OK.</li> In the PivotTable and PivotChart Wizard - Step 3 of 3 dialog box, click Existing worksheet to create the PivotTable on the existing worksheet. In the edit box beneath Existing Worksheet, type D1 .</li> Click Options, click to clear the Grand totals for rows check box, click OK, and then click Finish.</li></ol>

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

back to the top

<div class="references_section">