You are currently offline, waiting for your internet to reconnect

How to Analyze Questionnaire or Survey with a PivotTable

This article was previously published under Q151551
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.
SUMMARY
PivotTables are analysis tools that you may find helpful in summarizingdata from a questionnaire or survey.

If you create a table of survey results in Microsoft Excel, a PivotTableis useful when the answers are Yes, No, and Undecided. This is also truewhen answers are in the form of a range of values, for example, a scale inwhich 9 is the highest value and 1 is the lowest value. In both cases, youmay want to find the percentage of answers to the overall total responses.

This article provides two examples that demonstrate how a PivotTable inMicrosoft Excel can help to summarize the results of a survey.
MORE INFORMATION

Example 1

To create the first example in which you use a PivotTable to analyze theresponses given to several Yes, No, Undecided questions, follow thesesteps:

  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.
  10. In the PivotTable Wizard - Step 3 of 4 dialog box, click Next.
To complete the PivotTable, use the following appropriate method for yourversion of Microsoft Excel.

Microsoft Excel 5.0 and 7.0

In the PivotTable Wizard - Step 4 of 4 dialog box, under PivotTableStarting Cell, click cell $D$1. If you do not specify a cell, a newworksheet is created with the PivotTable. Clear "Grand Totals for Columns"and click Finish.

Microsoft Excel 97 and Microsoft Excel 98

In the PivotTable Wizard - Step 4 of 4 dialog box, click ExistingWorksheet to create a PivotTable on an existing worksheet. In the boxbeneath Existing Worksheet, type $D$1.Click Options. Clear "Grand Totals for Columns" and click OK. ClickFinish.

The resulting PivotTable contains the percentage of Yes, No, and Undecidedanswers for each of the three questions reported in the table. Note thatof the responses to question 1, 60 percent of the total questioned repliedNo, 20 percent replied Yes, and 20 percent replied Undecided. The samekindof breakdown is provided for questions 2 and 3.

Example 2

To create the second example and use a PivotTable to analyze responses toproduct 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.
  10. In the PivotTable Wizard - Step 3 of 4 dialog box, click Next.
To complete the PivotTable, use the following appropriate method for yourversion of Microsoft Excel.

Microsoft Excel 5.0 and 7.0

In the PivotTable Wizard - Step 4 of 4 dialog box, first click in thePivotTable Starting Cell box. Then, click cell $D$1. Click to clear theoption 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 ExistingWorksheet to create the PivotTable on the existing worksheet. In the editbox beneath Existing Worksheet, type $D$1.Click Options. Click to clear the "Grand Totals for Rows" check box andclick OK. Click Finish.

The resulting PivotTable contains the percentage that each rating receivedper product in the data table. When you view the PivotTable, it is easy toanalyze the highest percentage rating. Furthermore, you can analyze theresults to determine the overall response to the products. With Product1in the example, ratings 8 and 9 both share a 43 percent return of thetotal responses for that product. This indicates a generally highlyfavorable response to the product. Product2 is seen unfavorably with arating of 3, which receives an overwhelming 57 percent of the responsesto Product2.
REFERENCES

Excel 97 or Excel 98 Macintosh Edition

For more information about PivotTables in Microsoft Excel 97 or MicrosoftExcel 98, on the Help menu, click Microsoft Excel Help, type the followingand click Search:
pivottables

Microsoft Excel 7.0

For more information about PivotTables in Microsoft Excel version 7.0, onthe Help menu, click Answer Wizard, and then type the following:
pivottables

Microsoft Excel 5.0

For more information about PivotTables in Microsoft Excel version 5.0, onthe Help menu, click Search for Help On, and then type:
pivottables
5.00a 5.00c 7.00a 8.00 97 98 XL98 XL97 XL7 XL5 XL
Properties

Article ID: 151551 - Last Review: 08/17/2005 18:17:45 - Revision: 1.3

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • kbinfo kbualink97 KB151551
Feedback