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.

**Table of Contents**Expand

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

### 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**

Very informative post.