Power Query Problems

How to Create an Excel Pivot Chart from Microsoft Forms Ranking Responses

WPS Editor Oct 9, 2026 869 views

Question details

The user needs to normalize exported Microsoft Forms ranking responses in Excel so they can generate an accurate PivotChart.

How to Create an Excel Pivot Chart from Microsoft Forms Ranking Responses

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing exported survey data where Microsoft Forms groups multiple ranking answers into a single Excel cell.
Observed behavior: Multiple ranking options appear combined within one cell separated by semicolons, making it impossible to directly summarize or graph the data using a PivotTable or PivotChart.

Before you start

Ensure you have exported your Microsoft Forms responses to an Excel workbook and that your version of Excel supports the Power Query (Get & Transform Data) feature.

Solution 1Recommended

Normalize Data Using Power Query

Use Power Query to systematically split, unpivot, and clean the ranking data so it can be accurately analyzed using Pivot charts.

Microsoft Forms packs ranking responses into a single text string per respondent. Power Query Editor is the most efficient way to break these strings apart into distinct rows containing individual options and their respective rank values.

Import Data into Power Query

Open Excel, go to the 'Data' tab on the ribbon, click 'Get Data', and select your Microsoft Forms response workbook to open it in the Power Query Editor.

Split Ranking Responses

Select the column containing the packed ranking responses. Right-click the column header, choose 'Split Column', select 'By Delimiter', and choose 'Semicolon' to separate the rankings into new columns.

Unpivot the Columns

Select the newly created split columns, right-click any of the selected column headers, and click 'Unpivot Columns' to organize all the ranking options into unified rows.

Separate Option from Rank

Select the resulting values column, click 'Split Column' > 'By Delimiter', and use a period (.) to separate the text of the survey option from its numerical rank.

Clean and Load the Data

Filter out any blank or null answers from the columns. Ensure the rank column data type is set to 'Whole Number'. Finally, click 'Close & Load' on the Home tab to output the normalized data into a new Excel worksheet.

Insert the PivotChart

Select the new clean data table, navigate to the 'Insert' tab, click 'PivotChart', and build your chart by placing the survey options in the Axis (Categories) area and the numerical rank in the Values area, ensuring the calculation is set to 'Average'.

Calculation Tip: When summarizing ranking data in your PivotChart, always change the value field setting from 'Sum' or 'Count' to 'Average' to get a true representation of the ranking score.

Advanced Data Analysis

Analyze Survey Data Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in data handling tools, such as Text to Columns and versatile PivotCharts, allowing you to efficiently clean and visualize complex exported survey data.

1. Open the Data: Launch WPS Spreadsheet and open the exported Microsoft Forms workbook containing your survey results.
2. Split the Responses: Highlight the column with the ranking data, navigate to the 'Data' tab, click 'Text to Columns', and use the delimited option with a semicolon to split the text.
3. Organize the Layout: Format the separated columns into a structured table layout, ensuring each row represents a single option and its corresponding rank.
4. Insert PivotChart: Select your structured table, go to the 'Insert' tab, click 'PivotChart', and configure your fields to calculate the average rank for each category.

Completely free and lightweight office suiteSeamless compatibility with Microsoft Excel (.xlsx) filesRobust PivotTable and PivotChart features for data analysisIntuitive Text to Columns tool for quick data separation

microsoft office alternative - wps office

Frequently Asked Questions

Why does Microsoft Forms put all ranking answers in one Excel cell?

Microsoft Forms exports responses in a flat format to keep all answers from a single respondent on exactly one row. For ranking questions, it concatenates the choices into a single string separated by semicolons.

Can I separate this data without using Power Query?

Yes, you can use the 'Text to Columns' feature found on the Data tab to split the data by semicolons, though you will have to manually rearrange or transpose the resulting data to build a proper PivotTable.

How do I change the PivotChart to show average rank instead of sum?

In your PivotChart Fields pane, click on the field in the 'Values' section, select 'Value Field Settings', and change the summarize value option from 'Sum' to 'Average'.

Excel & Data Problems