Tally is a data representation technique that helps to understand complex data collection. The survey results often consist of lots of questions on a certain topic. After finishing a survey, it’s quite important to organize data for analysis. Thus, organizing survey results in tally can be really useful. In this article, I will show you to tally survey results in Excel step by step.
How to Tally Survey Results in Excel: with Easy Steps
In the following Patient Satisfaction Survey, you can see some survey questions and their corresponding feedback. All the feedback is recorded against the User Ids. The feedback is categorized as Great, Fair, and Poor. The questions that didn’t get any feedback are left blank.
Now I will use this survey result to show you to tally them all.
Step-1: Count All Feedback to Tally Survey Results
At the beginning of creating a tally of the survey results, you need to count all the feedback.
1.1. Counting Empty Response
First of all, I will count the total number of empty responses in the No Response row.
To count the empty response, I have used the COUNTBLANK function.
- Now type the following formula in cell I6.
- Then press ENTER.
=COUNTBLANK(C$5:C$16)Formula Breakdown
Here, the range C$5:C$16 is the range of the 1st Question column. In the range, only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.
Now drag the bottom-right corner of cell I6 until cell L6 to copy the formula.
After that, you will see the total number of empty responses against all the questions.
1.2. Counting Non-Empty Response
Now it’s time to count all the non-empty responses. For this purpose, I’ve used the COUNTA function.
- Insert the following formula in cell I7.
- Then press ENTER.
=COUNTA(C$5:C$16)Formula Breakdown
Here, the range C$5:C$16 is the range of the 1st Question column. In the range, only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.
Now drag the bottom-right corner of cell I7 until cell L7 copies the formula.
After that, you will see the total number of non-empty responses against all the questions.
1.3. Count Total Number of Response
After counting the number of empty and non-empty responses, you need to count the total number of responses. To do this, you can use the SUM function.
- Type the following formula in cell I8.
- Then press the ENTER key.
=SUM(I$6:I$7)Formula Breakdown
Here, the range I$6:I$7 includes the count value of empty and non-empty responses. In the range, only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.
Now drag the bottom-right corner of cell I8 until cell L8 copies the formula.
After that, you will see the total number of all kinds of responses to all the questions.
Step-2: Calculate Feedback Percentages to Tally Survey Results
After counting all the feedback, it’s time to calculate the percentages of feedback to tally the survey result. For this purpose, I’ve used the COUNTIF function.
- Insert the following formula in cell I10. This formula will calculate the percentages of Great, Fair, Poor
=COUNTIF(C$5:C$16,$H10)/I$7Formula Breakdown
- C$5:C$16: Here, the range C$5:C$16 is the range of the 1st Question In the range only the row numbers are locked. Thus, when you drag the formula to the right, only the column number changes but the rows.
- $H10: It refers to feedback, “Great”. Thus, the formula only counts the feedback that is “Great”. Dollar Sign ($) before the column number locks the column to H. I locked only the column because feedback is available only in the range H10:H12.
- I$7: It refers to the total non-empty response. Here, I locked only the row number because the number of total non-empty responses is only available in the range I7:L7.
- After applying the formula in cell I10, drag the bottom-right corner of cell I10 up to cell L10.
- Then select the range I10:L10 and drag the bottom-right corner of the selection area up to cell L12. This copies the formula in cell I10 all over the range I10:L12.
- With the range, I10:L12 selected, go to Home ➤ Number ➤ Percentage.
- Then click on the Increase Decimal command, two times.
This will calculate the percentages of all the feedback up to two decimal places.
Step-3: Create a Survey Report
So, we’ve calculated all the survey feedback in the two steps above. Now I will show you to create a survey report with all the results.
- For that, select the range H4: L12.
- Then press CTRL + C to copy them all.
- Now go to a blank worksheet.
- Select a cell and right-click on it.
- From the context menu, click on Transpose in the Paste Options.
So, your copied rows will be converted into columns and vice versa like in the following screenshot.
Now I will generate a Response vs Questions chart.
- For that, select the Parameter, No Response, Response, & Total Response
- Go to Insert ➤ Charts ➤ Insert Column or Bar Chart ➤ 2-D Column ➤ Clustered Column.
This will generate a Question vs Response chart. In the chart, Response, No Response, & Total Response data will be represented with 3 different color columns.
Now I will generate a Feedback vs Question chart.
- For that, select Parameter, Great, Fair, & Poor
- Then go to Insert ➤ Charts ➤ Insert Column or Bar Chart ➤ 2-D Column ➤ Clustered Column.
This will generate a Feedback vs Question chart. In the chart, 3 types of feedback i.e. Great, Fair, & Poor will be represented with 3 different color columns.
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, I have discussed how to tally survey results in Excel step by step. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
Related Articles
- How to Display Survey Results in Excel
- How to Create a Questionnaire in Excel
- How to Tabulate Data in Excel
- How to Encode Survey Data in Excel
- How to Analyze Survey Data in Excel
- How to Analyze Satisfaction Survey Data in Excel
- How to Analyze Survey Data with Multiple Responses in Excel
<< Go Back to Survey in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Very informative post.