Mukesh Dipto
This tutorial will show you how to analyze satisfaction survey data in Excel. Sometimes we get a list of survey data in an Excel worksheet. From that worksheet, we need to analyze the survey data and create a report in Excel. In order to make that report from Excel data is the main theme of this article. So, without any further delay let’s see the process to do this.
Download Practice Workbook
You can download the practice workbook from here.
Step-by-Step Procedures to Analyze Satisfaction Survey in Excel
Throughout this article, we will follow step-by-step procedures to analyze satisfaction surveys in Excel. We will use the following dataset to demonstrate all the steps. In the dataset, we have feedback on 4 questions from different individuals. We have used ID numbers to specify different persons. Using this dataset we will analyze the satisfaction survey.
STEP 1: Count All Feedback from Satisfaction Survey Data
In the first step, we will count all feedback in our dataset to analyze satisfaction survey data. We will use different Excel formulas in this step. Let’s see the action that we will follow in STEP 1:
- To begin with, select cell C13. Type the following formula in that cell:
=COUNTBLANK(C5:C11)
- Press Enter.
- In addition, drag the Fill Handle tool horizontally to the range we want to use this formula.
- So, we get results like the following image.
Here, the COUNTBLANK function returns the number of blank cells in the selected range of cells.
- Then, select cell C14 and Insert the following formula:
=COUNTA(C5:C11)- Hit Enter.
- Afterward, drag the Fill Handle tool horizontally to the range we want to use this formula.
- So, we can see the result in the following image.
In the above formula, the COUNTA function counts the number of non-empty cells in the selected range of cells.
- Furthermore, type the following formula in cell C15:
=SUM(C13:C14)- Press Enter.
- Next, drag the Fill Handle tool to the range where we want to apply this formula.
- As a result, we get the number of total cells.
Here, we used the SUM function to sum the number of blank and non-blank cells.
- After that, create a new section to record the number of all feedback like the following image.
- Select cell C17. Enter the following formula in that cell:
=COUNTIF(C5:C11,$C$11)
- Hit Enter.
- Then, drag the Fill Handle tool from cell C17 to F17 to copy the formula.
- Similarly, count the number of feedback Agree with the following formula:
=COUNTIF(C5:C11,$C$5)
- In the same way, we can count the number of other two feedbacks.
- So, the final result will look like the following image.
- Moreover, we will use the SUM function to find the total number of feedback for a single question.
- So, select cell C21. Insert the following formula:
=SUM(C17:C20)
- Press Enter.
- Then drag the Fill Handle tool to the range that we want to copy the formula.
Read More: How to Tally Survey Results in Excel (Step by Step)
STEP 2: Evaluate Percentages of Feedback with Formula
In the second step, we will evaluate percentages of feedback to analyze satisfaction surveys in Excel. Just follow the below instructions to evaluate percentages:
- First, we will increase the visibility of our dataset. To do that select row 5.
- Next, go to the View tab.
- Click on the Freeze Panes drop-down and select Freeze Panes.
So, all the rows above row number 5 are frozen. We are now able to scroll through row 5 in our dataset.
- After that, select cell C24. Insert the following formula in that cell:
=C17/$C$21
- Hit Enter.
- Then, format the value of cell C24 in percentage. To do this right-click on cell C24 and select Format Cells > Percentage.
- Afterward, drag the Fill Handle tool from cell C24 to C27 to copy the formula.
- Also, format the value into a percentage.
- Then, type formulas in cells D24, E24, and F24 like the following image.
- Moreover, we can drag the Fill handle tool to get results like the following image.
- Additionally, in the step we will insert the following formula in cell C28:
=SUM(C24:C27)- Press Enter and drag the Fill Handle tool from cell C28 to F28.
- Finally, we get results like the following image after this step.
Read More: How to Tabulate Data in Excel (4 Effective Ways)
STEP 3: Use Evaluated Results to Analyze Satisfaction Survey Data
In the final step, we will use the percentage value of the previous step to analyze the satisfaction survey data in Excel. Using the data from the previous method, we will create the final report of our analysis with satisfaction survey data. Let’s see how we can do this:
- Firstly, select the header row of the dataset.
- Secondly, go to the Home tab. Click on the Copy icon to copy the selected data. We can also press Ctrl + C to copy.
- Thirdly, open a new worksheet.
- Then, select cell B4.
- After that, go to Home > Paste > Paste Special.
- A new dialogue box will appear. Check the option Values and Transpose from the dialogue box. Then, click on OK.
- So, we can see the results in the following image. We have edited the name in cell B4 to Questions from ID.
- In addition, go to the 1st sheet ‘Count Feedbacks’.
- Copy row number 5 that contains the Total number of feedback.
- Then, select cell C5 and go to Home > Paste > Paste Special.
- Again, check the options Values and Transpose from the new dialogue box.
- Click on OK.
- So, we get the number of total feedback in a new column.
- Afterward, select cell range (B24:F28).
- Next, go to the sheet ‘Use Calculated Result’.
- Then, select cell D5 and go to Home > Paste > Paste Special.
- Like the immediate step check the options Values and Transpose from the new dialogue box.
- Now click on OK.
- Lastly, we get results like the following image. This is the final report of our analysis of satisfaction survey data.
Read More: How to Analyze Survey Data in Excel (with Quick Steps)
Conclusion
In conclusion, this tutorial shows how to analyze satisfaction survey data in Excel with different steps. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will try our best to respond to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.
