How to Analyze Survey Data with Multiple Responses in Excel (2 Methods)

Often users get data from surveys. Those surveys usually have multiple responses. Therefore, users need to analyze survey data with multiple responses in Excel. Excel formulas and the Power Query method organize data in a way that its easier for users to analyze them.

Let’s say we have a dataset of compilated survey data as depicted below and we want to analyze them.

Dataset-How to Analyze Survey Data with Multiple Responses Excel

In this article, we use multiple formulas and the Power Query method to analyze survey data with multiple responses in Excel.


Download Excel Workbook


2 Easy Ways to Analyze Survey Data with Multiple Responses in Excel

Analyzing data means preparing a visual representation of each response to every question. Follow the below section to learn more.


Method 1: Using Multiple Formulas to Analyze Survey Data with Multiple Responses

From the survey data, we know that we have multiple responses to a single question from customers. So, 1st we need to count each response to a particular question. Then the percentage of each response against the total response number reflects the customer’s opinion. By doing so we are able to analyze them.

Dataset-How to Analyze Survey Data with Multiple Responses Excel


🔁 Creating a Survey Data Report

Step 1: Count a specific response (i.e., Strongly AgreeB20) from the Question 1 (i.e., C5:C15) array using the COUNTIF formula.

=COUNTIF($C$5:$C$15,B20)

Formula-Analyze Survey Data with Multiple Responses Excel

🔺 Repeat the formula for other responses as shown in the below image.

Repeat

Step 2: Find the total response number for each question. Type the below SUM formula in any blank cell.

=SUM(C20:C23)

Sum

🔺 Again, repeat the SUM formula to find the other question’s total responses.

Repeat

Step 3: To generate a report, use the division operator in any cell (i.e., C29). Execute the below formula as depicted below.

=C20/$C$24

Percentage

The percentage for each response for each question indicates the customer’s overall view about the asked topic in that question.


🔁 Presenting Achieved Report in Stacked Bar Chart

Users can present their data in Charts. Thus, it’s easy to understand other users’ outcomes of any survey. Follow the below steps to depict survey data in Charts.

Step 1: Highlight the range then go to the Insert tab > Click on All Chart Types.

Data Source

Step 2: In a moment, Excel fetched the Insert Chart window. From the window, choose any of the Recommended Charts (i.e., Stacked bar). Afterward, click on OK.

All Charts-Analyze Survey Data with Multiple Responses Excel

🔺 In a moment, Excel inserts a Stacked bar Chart as depicted in the below picture. Just furnish your Data Chart as you please.

Stacked Bar-Analyze Survey Data with Multiple Responses Excel


Method 2: Analyzing Survey Data with Multiple Responses Using Power Query in Excel

Excel Power Query is an effective tool for data analysis. Therefore, it’s obvious we use Excel Power Query to analyze survey data with multiple responses in Excel. For Power Query, we use different survey data for better representation (data in Table).

Power Query-Analyze Survey Data with Multiple Responses Excel

Step 1: Select the entire range then move to Data > Click on From Table/Range.

Power Query-Analyze Survey Data with Multiple Responses Excel

Step 2: Excel takes a while then loads the Power Query Editor window. Select columns (using CTRL+Click) except the Column ID column. Then select Transform tab > Click on Unpivot Columns.

Loaded Data

🔺 Excel transforms all the data as depicted in the below picture. Now, all the entries within the selected columns get unpivoted.

Unpivoted data-Analyze Survey Data with Multiple Responses Excel

Step 3: After unpivoting the columns, go to the Home tab > Select Close & Load then click on Close & Load.

Load & Close-Analyze Survey Data with Multiple Responses Excel

Step 4: After a while Excel loads all the columns. Place the cursor in any cell afterward the Table Design tab appears. Click on the Table Design tab and click on Summarize with Pivot Table (in the Tools section).

Loaded Data-Analyze Survey Data with Multiple Responses Excel

Step 5: The PivotTable from table or range window appears. Choose New Worksheet as Choose where you want the PivotTable to be placed. Then click on OK.

PivotTable Fields

Step 6: In the PivotTable Fields section, Tick all the fields and place the respective fields as shown in the screenshot below.

Pivot Table Fields

🔺 Placing the fields in the respective area creates a Pivot Table depiction as shown in the below image.

Pivot Table

Step 7: Right-click on the cell value then the Context Menu appears. Choose the Value Field Settings option.

Value Fields Settings-Analyze Survey Data with Multiple Responses Excel

Step 8: The Value Field Settings dialog box appears. Select Show Value As section >  Choose % of Row Total (as the rows represent an entire section response) under the Show values As command. Afterward, click OK.

Value Field Settings Window

🔺 In the end, each response results in the percentage against the total responses count.

Power Query Depiction- Analyze Survey Data with Multiple Responses Excel

Users can add more response items for each section. We use a tiny sample of survey data to analyze survey responses to provide a better one.


Conclusion

In this article, we use multiple functions and Power Query to analyze survey data and multiple responses in Excel. However, multiple functions need an extensive understanding of what the final analysis depiction may look like. On the other hand, it’s basic Power Query operations to go with. Hope this article helps Excel users to overcome their lagging. Comment, if any further inquiries arise or have anything to add.

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo