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.
In this article, we use multiple formulas and the Power Query method to analyze survey data with multiple responses in Excel.
How to Analyze Survey Data with Multiple Responses in Excel: 2 Easy Ways
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.
🔁 Creating a Survey Data Report
Step 1: Count a specific response (i.e., Strongly Agree–B20) from the Question 1 (i.e., C5:C15) array using the COUNTIF formula.
=COUNTIF($C$5:$C$15,B20)
🔺 Repeat the formula for other responses as shown in the below image.
Step 2: Find the total response number for each question. Type the below SUM formula in any blank cell.
=SUM(C20:C23)
🔺 Again, repeat the SUM formula to find the other question’s total responses.
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
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.
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.
🔺 In a moment, Excel inserts a Stacked bar Chart as depicted in the below picture. Just furnish your Data Chart as you please.
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).
Step 1: Select the entire range then move to Data > Click on From Table/Range.
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.
🔺 Excel transforms all the data as depicted in the below picture. Now, all the entries within the selected columns get unpivoted.
Step 3: After unpivoting the columns, go to the Home tab > Select Close & Load then click on Close & Load.
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).
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.
Step 6: In the PivotTable Fields section, Tick all the fields and place the respective fields as shown in the screenshot below.
🔺 Placing the fields in the respective area creates a Pivot Table depiction as shown in the below image.
Step 7: Right-click on the cell value then the Context Menu appears. Choose the Value Field Settings option.
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.
🔺 In the end, each response results in the percentage against the total responses count.
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.
Read More: How to Analyze Satisfaction Survey Data in Excel
Download Excel Workbook
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.
Related Articles
- How to Tally Survey Results in Excel
- How to Tabulate Data in Excel
- Display Survey Results in Excel
- How to Create a Questionnaire in Excel
- How to Analyze Survey Data in Excel
- How to Encode Survey Data in Excel
<< Go Back to Survey in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
