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.

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: Creating a Survey Form for Feedback

Before going to analyze any survey data in Excel, you need to create a 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

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: Counting Blank and Non-Blank Feedback from Survey Data in Excel

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.

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)

Press Enter to apply the formula.

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

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.

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.

Step 3: Counting 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)

Press Enter to apply the formula.

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

🔎 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)

Press Enter to apply the formula.

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

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.

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.

Step 4: Calculating 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 analyze 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.

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.

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.

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.

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.

Step 5: Using 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.

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.

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.

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

Step 6: Creating Excel 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.

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.

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.

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.

Download Practice Workbook

Download this practice workbook.

Survey Data.xlsx

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.

<< Go Back to Survey in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

3 Comments

An Feb 17, 2023 at 7:19 PM

This is helpful. Thank you.
- Reply
  Shamima Sultana Feb 20, 2023 at 10:06 AM
  
  Hello, An!
  
  You are most welcome. To get helpful content follow ExcelDemy.
  
  Regards
  ExcelDemy
  - Reply
    Janice Mar 9, 2023 at 4:04 PM
    
    Thankyou ..it helped me a lot