Chart & Visualization Issues

How to Group Excel Survey Responses and Create Charts

Huma Ashraf Ch Oct 1, 2026 868 views

Question details

The user needs to group survey and focus-group responses in Excel by respondent type and summarize them into charts displaying the response counts.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Summarizing raw survey data to visualize response distributions based on specific respondent demographics or types.
Observed behavior: Data needs to be reshaped and aggregated to generate meaningful, categorized charts based on respondent types and their answers.

Before you start

Ensure your survey data is organized in a clean tabular format with distinct column headers for respondent types and individual survey questions before attempting to unpivot or summarize.

Solution 1Recommended

Use Power Query and PivotTables to Summarize Survey Data

This method reshapes survey data using Power Query's Unpivot feature, making it perfectly structured for PivotTable aggregation and PivotChart visualization.

Survey data is often exported in a wide format where each question is its own column. To properly summarize this data by respondent type, it must first be 'unpivoted' into a flat format.

Import Data into Power Query

Select your data range in Excel, go to the 'Data' tab on the ribbon, and click 'Get Data > From Table/Range' to open the Power Query Editor.

Unpivot the Answer Columns

In the Power Query Editor, hold the Ctrl key to select all columns containing the survey answers. Right-click one of the selected column headers and choose 'Unpivot Columns'. This will collapse them into 'Attribute' (Question) and 'Value' (Answer) columns.

Load the Data Back to Excel

Click the 'Close & Load' button in the Home tab. This will bring your newly structured, analysis-ready data back into a new Excel worksheet.

Create a PivotTable

Click anywhere inside the new data table, navigate to the 'Insert' tab, and select 'PivotTable'. In the PivotTable Fields pane, drag 'Respondent Type' and 'Attribute' into the Rows or Columns area, and drag 'Value' into the Values area to count the responses.

Insert a PivotChart

With your PivotTable selected, navigate to the 'PivotTable Analyze' (or 'Insert') tab and click 'PivotChart'. Choose a preferred chart type, such as a Clustered Column or Bar chart, to visualize your summarized data.

Use Power Query and PivotTables to Summarize Survey Data

Dynamic Analysis: You can now use PivotTable Filters or Slicers to dynamically update your PivotChart based on specific respondent types or questions.

Summarize Survey Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful PivotTable and charting capabilities, allowing you to seamlessly group, analyze, and visualize your survey data without complex add-ins.

1. Open Data: Launch WPS Spreadsheet and open the file containing your prepared survey dataset.
2. Insert PivotTable: Select your entire data range, navigate to the 'Insert' tab, and click on 'PivotTable'.
3. Configure Fields: Drag the 'Respondent Type' and 'Answer' fields into the Rows area, and drag 'Answer' again into the Values area to set it to 'Count'.
4. Create Chart: Highlight the summarized data in your PivotTable, go to the 'Insert' tab, and select 'Chart' to generate a visual summary.

Fully compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.Intuitive drag-and-drop PivotTable interface for quick data summarization.Rich library of customizable charts to present your survey findings beautifully.Lightweight software that processes large datasets quickly and smoothly.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to unpivot my survey data before creating a chart?

Survey tools often export data with each question occupying a separate column. Unpivoting condenses these into dedicated 'Question' and 'Answer' columns. This flat structure is required by PivotTables to accurately group, count, and filter responses across multiple categories.

How can I filter the PivotChart to show only specific respondent types?

You can easily add a Slicer to your PivotTable by going to the 'PivotTable Analyze' tab and clicking 'Insert Slicer', then selecting 'Respondent Type'. Alternatively, drag the 'Respondent Type' field into the Filters area of the PivotTable Field List to create a dropdown filter above your chart.

Can I show percentages instead of response counts in my PivotChart?

Yes. In the PivotTable Field List, click on the field in the 'Values' area and select 'Value Field Settings'. Go to the 'Show Values As' tab and select '% of Grand Total', '% of Column Total', or '% of Row Total' depending on how you want to represent the distribution.

Excel & Data Problems