How to Analyze Survey Data in Excel (with Quick Steps)

Sometimes, companies or other organizations need to take survey data. By using those survey data, they analyze their business and plan for further development. That’s why survey data is really important in terms of customer feedback. Microsoft Excel gives you a better platform where you can easily analyze your survey data. This article will mainly focus on how to analyze survey data in Excel. I hope you find this article very informative and gather lots of knowledge regarding the survey data in Excel.


Download Practice Workbook

Download this practice workbook.


Step-by-Step Procedure to Analyze Survey Data in Excel

To analyze survey data in Excel, we will show all the step-by-step procedures through which you can create and analyze survey data with ease. All the steps are very effective and easy to use. Moreover, all the steps will give you profound details about the survey data.


Step 1: Create Survey Form for Feedback

Before going to analyze any survey data in Excel, you need to create to survey form using google forms where we can any survey form and take feedback from the customers.

  • First, create all the questions regarding your products.
  • We create five questions to get the feedback from the customers

How to Analyze Survey Data in Excel

  • Then, take the response from the customers.
  • After that, you are good to go for the analysis of that survey data in Excel.

Step 2: Count Blank and Non-Blank Feedback from Survey Data

In this step, we take all the responses from the customer feedback survey data and set them in Excel. After that, count all the blank and non-blank feedback and also count the specific response from the customers Using the COUNTBLANK and COUNTA functions.

  • As we provide five questions to get responses from the customers, we set a dataset without this.
  • Our dataset is mainly focused on the question answers and the customers’ IDs.

How to Analyze Survey Data in Excel

  • Here, you can see several customers skip some of the questions without giving any feedback.
  • We need to count the blank and non-blank cells at first.
  • To do this, select cell C16.

  • Write down the following formula in the formula box.
=COUNTBLANK(C5:C14)

How to Analyze Survey Data in Excel

  • Press Enter to apply the formula.

How to Analyze Survey Data in Excel

  • Then, drag the Fill Handle icon up to cell G16.

How to Analyze Survey Data in Excel

  • Then, to count the non-blank feedback from the customers, select cell C17.

  • Write down the following formula in the formula box.
=COUNTA(C5:C14)

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell G17.

How to Analyze Survey Data in Excel

  • Next, count the total feedback using the SUM function. we need to add all the blank and non-blank cells.
  • Select cell C18.

  • Write down the following formula in the formula box.
=SUM(C16:C17)

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon up to cell G18.

How to Analyze Survey Data in Excel


Step 3: Count All Feedback from Survey

In this step, we count all the feedback from the customers. As you can see we give four different options to give their valuable opinions. Every customer has different opinions. We need to count them individually using the COUNTIF function.

  • First, we need to increase the visibility of our dataset.
  • Select row 15.

  • Next, go to the View tab in the ribbon.
  • From the Freeze Panes drop-down menu, select Freeze Panes.

  • Then, select cell C20.

  • Write down the following formula.
=COUNTIF(C5:C14,$C$6)

How to Analyze Survey Data in Excel

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell G20.

How to Analyze Survey Data in Excel

🔎 Breakdown of the Formula

COUNTIF(C5:C14,$C$6)

This formula denotes the total number of condition that appears in the given range of cells. Here, the function first enters into the range of cells C5 to C14. After that, it searches the criteria you give in the formula. Here we give a criterion cell C6 which means Strongly Agree. So, the function searches this criterion in the whole range of cells. Then, it returns the total number of times it appears. Here, Two customers give this response in the survey data.

  • After that, we need to calculate the total number of times Agree appears in the survey data.
  • At first, select cell C21.

  • Then, write down the following formula in the formula box. Here, we have taken the criterion Agree. So, we take cell C5 as the criterion and make it constant by pressing F4.
=COUNTIF(C5:C14,$C$5)

How to Analyze Survey Data in Excel

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell G21.

How to Analyze Survey Data in Excel

  • In the same way, we can count feedback for the other two options.
  • The basic difference between all the conditions is the criterion. Set the criterion according to the feedback options
  • After that, we get the final calculation of all the feedback. See the screenshot.

How to Analyze Survey Data in Excel

  • Now, we need to calculate the total number of feedback using the SUM function.
  • Select cell C24.

  • Write down the following formula.
=SUM(C20:C23)

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell G24.

How to Analyze Survey Data in Excel


Step 4: Calculate Percentage of Each Feedback

In this step, we will calculate the percentage of the specific feedback. Actually, this step will give us the overall look of the survey data. This step is the main step to analyse survey data.

  • First, select cell C27.

  • Write down the following formula in the formula box.
=C20/$C$24

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell C30.

How to Analyze Survey Data in Excel

  • Then, select cell D27.

  • Write down the following formula in the formula box.
=D20/$D$24

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell D30.

How to Analyze Survey Data in Excel

  • Then, select cell E27.

  • Write down the following formula in the formula box.
=E20/$E$24

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell E30.

How to Analyze Survey Data in Excel

  • Then, calculate the percentage in a similar way for questions 4 and question 5.
  • After that, you will get the following results. See the screenshot.

How to Analyze Survey Data in Excel

  • Now, we need to calculate the total percentage to check whether it is 100% or not.
  • Select cell C31.

  • Write down the following formula.
=SUM(C27:C30)

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell G31.

How to Analyze Survey Data in Excel


Step 5: Use Estimated Results to Analyze Survey Data

In this step, we will use the percentage values from the previous methods and other important values to get a final analysis of the survey data,

  • At first, select the row header of the dataset.

  • Then, go to the Home tab in the ribbon.
  • From the Clipboard group, select Copy. For keyboard shortcuts, you can use Ctrl+C.

  • Next, go to the open worksheet.
  • Then, select cell B4.
  • After that, go to the Home tab in the ribbon
  • From the Clipboard group, select the Paste option.

  • After that, in the Paste option, select Paste Special.

  • A Paste Special dialog box will appear.
  • In the Paste section, select Values.
  • Then, check on Transpose.
  • Finally, click on OK.

  • There we have the result in the screenshot.
  • Here, we have changed the Customer ID to Questions.

How to Analyze Survey Data in Excel

  • Then, go to the dataset and select row 24 where we have all the total feedback.

  • Copy the total feedback row by pressing Ctrl+C on the keyboard.

  • Next, go to the Survey Result
  • Then, select cell C4.
  • After that, go to the Home tab in the ribbon
  • From the Clipboard group, select the Paste option.

  • After that, in the Paste option, select Paste Special.

  • A Paste Special dialog box will appear.
  • In the Paste section, select Values.
  • Then, check on Transpose.
  • Finally, click on OK.

  • As a result, we get the total number of feedback in the transpose format.

How to Analyze Survey Data in Excel

  • After that, to get the percentage of the feedback from customers, you need to select the range of cells B27 to G31.

  • Copy the total feedback row by pressing Ctrl+C on the keyboard.

  • Next, go to the Survey Result.
  • Then, select cell D4.
  • After that, go to the Home tab in the ribbon.
  • From the Clipboard group, select the Paste option.

  • After that, in the Paste option, select Paste Special.

  • A Paste Special dialog box will appear.
  • In the Paste section, select Values.
  • Then, check on Transpose.
  • Finally, click on OK.

How to Analyze Survey Data in Excel

  • As a result, we get the percentage of feedback in the transpose format.

How to Analyze Survey Data in Excel


Step 6: Create Chart to Analyze Survey Data

Finally, in this step, we will create a chart to analyze survey data. As we have four different opinions to have from the customers, we may use a bar or column chart to express them.

  • First, select the range of cells D4 to G9.

How to Analyze Survey Data in Excel

  • Then, go to the Insert tab in the ribbon.
  • From the Charts option, select Recommended Charts.

  • Then, an Insert Chart dialog box will appear.
  • From there, select Clustered Bar chart.
  • Finally, click on OK.

  • It will provide a bar chart. But that’s what we want.
  • We need to add more data. To do this, select the chart, and it will open up the Chart Design option.
  • From there in the Data group, select the Select Data option.

  • A Select Data Source dialog box will appear.
  • In the Horizontal Axis Labels, click on Edit.

  • Change the Axis label range and select cells B5 to B9.
  • Then, click on OK.

  • After that, click on OK in the Select Data Source dialog box to apply the changes.

  • There we have our desired bar chart.

How to Analyze Survey Data in Excel

  • After that, we can change the Chart Style by clicking on the icon. See the screenshot.

  • There we have our final bar chart regarding the survey data.

How to Analyze Survey Data in Excel

  • If you don’t like the bar chart, then you can change the chart type by right-clicking on it.
  • Right-click on the chart and a Context Menu box will appear.
  • From there select the Change Chart Type option.

  • Select the Column chart from the Recommended Charts option.
  • Then, select the first type of column chart.
  • Finally, click on OK.

  • There, we have our required column chart for survey data.

How to Analyze Survey Data in Excel


Conclusion

To analyze survey data in Excel, we have shown step-by-step procedures. With these steps, you can easily analyze our survey data in Excel. We have also shown some graphical representations of our survey data. Moreover, it can be a complete solution in terms of survey data analysis. I hope you enjoy the whole article and find it very useful. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.

Durjoy Paul
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo