We noticed that JavaScript is disabled in your browser. We suggest enabling it for a better experience.
We noticed you're using an older version of Internet Explorer. We suggest you update to the latest version for a better experience.
Skip to main content
  • Home
  • Analyze
  • Graph responses to survey questions in Microsoft Excel
Print

Graph responses to survey questions in Microsoft Excel

If you’ve created a form containing the following fields:

  • Radio button
  • Checkbox
  • Radio matrix
  • Checkbox matrix

You may want to present your response data in a graph.

example of a pie chart

You can create graphs of respondent’s answers to these fields by exporting your form responses to Microsoft Excel and taking the following steps. 

The technique for graphing responses to each field type is slightly different. Make sure to follow the right set of instructions for the field you're using in your form.

Graph a radio button field

  1. Create a form containing a radio button field.
    field.png
  2. Publish the form.
  3. When responses have come in, export your data to an Excel file.
  4. When you open this file in Microsoft Excel, you'll see the responses to your radio button field represented in one of the columns.
    radio column.png
  5. To graph this data, in the Insert tab of the Excel ribbon, select Pivot Chart.
    insert pivot chart.png
  6. In the Create Pivot Chart dialog, use the arrow button in the Table Range field to highlight your radio button response data, including the column header.
    select range.gif
  7. Select New worksheet, then select OK.
  8. To create a graph, or "pivot chart", drag your data into the Axis and Values boxes in the Pivot Chart Fields panel. 
    radio make chart.gif
  9. Congratulations! You've graphed your radio button response data.
    chart.png
    Your can right-click on this chart and use the Change Chart Type... option to select other chart types.
    radio pie.png

Graph a checkbox field

  1. Create a form containing a checkbox field.

    2020-10-13_11-19-53.png

  2. Publish the form.

  3. When responses have come in, export your data to an Excel file.

  4. When you open this file in Microsoft Excel, you’ll see that the responses to your checkbox field is represented in two columns. One comprises of comma separated values, while the other is in JSON format.
    excel JSON2.png
    You can use Excel to transform this data into something it can graph.
  5. Select all of the data in the JSON column, including the header.
    Select data.gif
  6. In the Data tab in the Excel ribbon, select From Table/Range.
    from table range.png
  7. This will open a Power Query Editor window. In the Transform tab of this window, select Parse > JSON.
    parse json.png
    This will parse your JSON data into a series of records.
    parsed json.png
  8. Select the expand button at the top of your parsed data.
    parsed json break.png
  9. In the window that opens, uncheck the Use original column name as prefix box and select OK.
    uncheck.png
    This will expand your records into a series of lists.
    list.png
  10. Select the expand button at the top of your data again, and select Expand to New Rows.
    expand.png
    This will expand your lists into their component values.
    expanded list.png
    This expanded data can be used to create a graph.
  11. Once your data has been expanded as above in the Power Query Editor, Select the Home tab in the Editor ribbon, then Close & Load > Close & Load To...
    close and load to.png
  12. In the Import Data dialog, select Pivot Chart and New Worksheet, then select OK.
    Pivot chart.png
  13. To create your graph, or "pivot chart", drag your data into the Axis and Values boxes in the Pivot Chart Fields panel.
    make chart.gif
  14. Congratulations, you've graphed the responses from a checkbox field.
    checkbox chart.png
    You can right-click this chart in Excel and use the Change Chart Type... option to select other chart types.
    pie chart.png

Graph a radio matrix field

  1. Create a form containing a radio matrix field.

    Radio Matrix Field.png

  2. Publish the form.

  3. When responses have come in, export your data to an Excel file.

  4. When you open this file in Microsoft Excel, you’ll see that the responses to your checkbox field is represented in two columns. One comprises of comma separated values, while the other is in JSON format.
    radio matrix excel.png
    You can use Excel to transform this data into something it can graph.
  5.  Select all of the data in JSON column, including the header.
    radio matrix select range.gif
  6. In the Data tab in the Excel ribbon, select From Table/Range.
    from table range.png
  7. This will open a Power Query Editor window. In the Transform tab of this window, select Parse > JSON.
    radio matrix parse json data.png
     This will parse your JSON data into a series of records.
    radio matrix json records.png

  8. Select the expand button at the top of your parsed data.

    radio matrix expand records.png

  9. In the window that opens, uncheck the Use original column name as prefix box and select OK.

    radio matrix uncheck box.png
    This will expand your records into a series of lists.
    radio matrix lists.png
  10. Select all three of your lists by holding Shift and selecting the headers.
    radio matrix select lists.gif
  11. In the Power Query Editor ribbon's Transform tab, select Unpivot Columns.
    radio matrix unpivot columns.png
    This will re-order your data into two columns corresponding to the questions and answers in your form's radio matrix field.
    radio matrix question answer.gif
    At this stage you may want to rename these columns "question" and "answer", or something similar, to make it easier to follow the next steps.
  12. Once your data has been prepared as above in the Power Query Editor, select the Home tab in the Editor ribbon, then Close & Load > Close & Load To...
    radio matrix close and load to.png
  13. In the Import Data dialog, select Pivot Chart and New Worksheet, then select OK.
    radio matrix choose pivot chart.png
  14. To create your graph, or "pivot chart", go the the Pivot Chart Fields panel, and drag your "question" data into the Filter field, and your Answer data into BOTH the Axis and Values fields.
    radio matrix drag and drop.gif
  15. You'll see that you've now created a graph of your radio matrix data.
    radio matrix pivot chart.png
  16. To choose between the different questions in your graph, use the Question dropdown menu.
    radio matrix select question.gif
  17. You may also want to re-order your answer options.

    radio matrix sort answers.gif

  18. You can right-click this graph in Excel and use the Change Chart Type... option to select other chart types.
    radio matrix other graph.png

Graph a checkbox matrix

  1. Create a form containing a checkbox matrix field.

    field.png

  2. Publish the form.

  3. When responses have come in, export your data to an Excel file.

  4. When you open this file in Microsoft Excel, you’ll see that the responses to your checkbox field is represented in two columns. One comprises of comma separated values, while the other is in JSON format.
    checkbox matrix excel.png
    You can use Excel to transform this data into something it can graph.
  5. Select all of the data in the JSON column, including the header.
    checkbox matrix select.gif
  6. In the Data tab in the Excel ribbon, select From Table/Range.
    from table range.png
  7. This will open a Power Query Editor window. In the Transform tab of this window, select Parse > JSON.
    json parse.png
    This will parse your JSON data into a series of records.
    jason records.png
  8. Select the expand button at the top of your parsed data.
    parsed json break.png
  9. In the window that opens, uncheck the Use original column name as prefix box and select OK.
    checkbox matrix uncheck.png
    This will expand your records into a series of lists.
    checkbox matrix lists.png

  10. Select all three of your lists by holding Shift and selecting the headers.

    checkbox matrix select lists.gif
  11. In the Power Query Editor ribbon's Transform tab, select Unpivot Columns.
    radio matrix unpivot columns.png
    This will re-order your data into two columns corresponding to the questions and answers in your form's checkbox matrix field.
    checkbox matrix unpivoted list.png
    The second column will still consist of lists of answers, which need to be expanded. 

  12. Select the expand button at the top of the Value column, and choose Expand To New Rows. 

    checkbox matrix expand to new rows.png
    This will expand your lists into their component values.
    checkbox matrix expanded.png
    At this stage you may want to rename these columns "question" and "answer", or something similar, to make it easier to follow the next steps.
    checkbox matrix question answer.gif
  13. Once your data has been prepared as above in the Power Query Editor, select the Home tab in the Editor ribbon, then Close & Load > Close & Load To...
    checkbox matrix close and load to.png
  14. In the Import Data dialog, select Pivot Chart and New Worksheet, then select OK.
    radio matrix choose pivot chart.png
  15. To create your graph, or "pivot chart", go the the Pivot Chart Fields panel, and drag your "question" data into the Filter field, and your Answer data into BOTH the Axis and Values fields.
    radio matrix drag and drop.gif
  16.  You'll see that you've now created a graph of your radio matrix data.

     checkbox matrix initial pivot.png
    However, this initial chart includes all of your respondent answers, regardless of the questions asked.

    Checkbox matrix tally together.png
    In the example above, ice cream flavours that respondents like and don't like are tallied together - not ideal!

  17. To display seperate questions in your graph, use the Question dropdown menu to select a question to filter your results by.

    checkbox matrix filter by question.gif

  18. You can right-click this graph in Excel and use the Change Chart Type... option to select other chart types.

    checkbox matrix other graph.png

     

     

     

     

     

     



     

     

 

 

Was this helpful?

Related topics