How to Tally Survey Results in Excel (Step by Step)

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.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Steps to Tally Survey Results in Excel

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.

Dataset to Tally Survey Results in Excel


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.

Counting Empty Response to Tally Survey Results in Excel

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.

Counting Empty Response to Tally Survey Results in Excel


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.

Counting Non-Empty Response to Tally Survey Results in Excel

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.

Counting Non-Empty Response to Tally Survey Results in Excel


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
=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.

Counting Total Response to Tally Survey Results in Excel

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.

Counting Total Response to Tally Survey Results in Excel


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$7

Formula 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.

Calculate Feedback Percentages to Tally Survey Results in Excel


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.

Create a Survey Report

  • 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 Chart2-D ColumnClustered Column.

Create a Survey Report

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.

Response Chart: Tally Survey Results in Excel

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 ColumnClustered 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.

Feedback Chart: Tally Survey Results in Excel


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.


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. And please visit our website Exceldemy to explore more.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo