How to Analyze Survey Data in Excel: 6 Quick Methods

Method 1 – Creating a Survey Form for Feedback

Create all the questions regarding your products.
Create five questions to get the feedback from the customers

<a href= How to Analyze Survey Data in Excel " width="569" height="596" />

Take the response from the customers.
Go for the analysis of that survey data in Excel.

Method 2 – Counting Blank and Non-Blank Feedback from Survey Data in Excel

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.

<a href= How to Analyze Survey Data in Excel " width="831" height="401" />

Several customers skip some of the questions without giving any feedback.
We need to count the blank and non-blank cells at first.
Select cell C16.

Write down the following formula in the formula box.

=COUNTBLANK(C5:C14)

<a href= How to Analyze Survey Data in Excel " width="830" height="513" />

Press Enter to apply the formula.

<a href= How to Analyze Survey Data in Excel " width="830" height="474" />

<a href= How to Analyze Survey Data in Excel " width="835" height="473" />

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.

<a href= How to Analyze Survey Data in Excel " width="836" height="479" />

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.

<a href= How to Analyze Survey Data in Excel " width="833" height="479" />

Method 3 – Counting All Feedback from Survey

We need to increase the visibility of our dataset.
Select row 15.

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

Write down the following formula.

=COUNTIF(C5:C14,$C$6)

<a href= How to Analyze Survey Data in Excel " width="833" height="587" />

Press Enter to apply the formula.

<a href= How to Analyze Survey Data in Excel " width="834" height="553" />

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. The function first enters into the range of cells C5 to C14. It searches the criteria you give in the formula. We give a criterion cell C6 which means Strongly Agree. The function searches this criterion in the whole range of cells. It returns the total number of times it appears. Two customers give this response in the survey data.

We need to calculate the total number of times Agree appears in the survey data.
Select cell C21.

Write down the following formula in the formula box. Here, we have taken the criterion Agree. Take cell C5 as the criterion and make it constant by pressing F4.

=COUNTIF(C5:C14,$C$5)

<a href= How to Analyze Survey Data in Excel " width="833" height="590" />

Press Enter to apply the formula.

<a href= How to Analyze Survey Data in Excel " width="832" height="553" />

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.
We get the final calculation of all the feedback. See the screenshot.

<a href= How to Analyze Survey Data in Excel " width="829" height="555" />

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.

<a href= How to Analyze Survey Data in Excel " width="825" height="558" />

Method 4 – Calculating Percentage of Each Feedback

Write down the following formula in the formula box.

=C20/$C$24

Press Enter to apply the formula.

<a href= How to Analyze Survey Data in Excel " width="824" height="479" />

Write down the following formula in the formula box.

=D20/$D$24

Press Enter to apply the formula.

<a href= How to Analyze Survey Data in Excel " width="823" height="478" />

Write down the following formula in the formula box.

=E20/$E$24

Press Enter to apply the formula.

<a href= How to Analyze Survey Data in Excel " width="826" height="482" />

Calculate the percentage in a similar way for questions 4 and question 5.
You will get the following results. See the screenshot.

<a href= How to Analyze Survey Data in Excel " width="826" height="486" />

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.

<a href= How to Analyze Survey Data in Excel " width="821" height="477" />

Method 5 – Using Estimated Results to Analyze Survey Data

Select the row header of the dataset.

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

Go to the open worksheet.
Then, select cell B4.
Go to the Home tab in the ribbon
From the Clipboard group, select the Paste option.

Have the result in the screenshot.
We changed the Customer ID to Questions.

<a href= How to Analyze Survey Data in Excel " width="265" height="267" />

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.

Go to the Survey Result
Select cell C4.
Go to the Home tab in the ribbon
From the Clipboard group, select the Paste option.

Get the total number of feedback in the transpose format.

<a href= How to Analyze Survey Data in Excel " width="300" height="272" />

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.

Go to the Survey Result.
Select cell D4.
Go to the Home tab in the ribbon.
From the Clipboard group, select the Paste option.

<a href= How to Analyze Survey Data in Excel " width="396" height="326" />

Get the percentage of feedback in the transpose format.

<a href= How to Analyze Survey Data in Excel " width="777" height="269" />

Method 6 – Creating Excel Chart to Analyze Survey Data

<a href= How to Analyze Survey Data in Excel " width="778" height="266" />

Go to the Insert tab in the ribbon.
From the Charts option, select Recommended Charts.

It will provide a bar chart. But that’s what we want.
We need to add more data. 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 Edit.

Click on OK in the Select Data Source dialog box to apply the changes.

There we have our desired bar chart.

<a href= How to Analyze Survey Data in Excel " width="471" height="280" />

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.

<a href= How to Analyze Survey Data in Excel " width="474" height="279" />

You can change the chart type by right-clicking on it.
Right-click on the chart and a Context Menu box will appear.
Select the Change Chart Type option.

Select the Column chart from the Recommended Charts option.
Select the first type of column chart.
Click OK.

We have our required column chart for survey data.

<a href= How to Analyze Survey Data in Excel " width="474" height="281" />

Download Practice Workbook

Download this practice workbook.

Related Articles

How to Tabulate Data in Excel
How to Tally Survey Results in Excel
How to Encode Survey Data in Excel
How to Create a Questionnaire in Excel
How to Display Survey Results in Excel
How to Analyze Survey Data with Multiple Responses in Excel
How to Analyze Satisfaction Survey Data in Excel