Chart & Visualization Issues

How to Fix Cannot Create a Chart from Excel Survey Data

WPS Editor Sep 30, 2026 868 views

Question details

The user is unable to generate accurate charts from Excel survey data because the PivotTables are formatting incorrectly or displaying inconsistent results across different data tables.

How to Fix Cannot Create a Chart from Excel Survey Data

Product: Excel
Device & OS: not provided
Scenario: Visualizing survey data by creating charts from PivotTables based on response datasets.
Observed behavior: PivotTables and corresponding charts are created incorrectly when survey fields are placed in the wrong areas, or results vary because the source tables contain different data structures or insufficient data.

Before you start

Before creating your PivotTable and chart, review your survey data to ensure there are no blank rows or columns and that every column has a distinct header.

Solution 1Recommended

Adjust PivotTable Field Placement

Correctly configuring your PivotTable fields ensures that survey data, such as age groups and survey choices, is categorized properly for charting.

Often, survey data charts fail to render correctly because categorical fields are placed in the wrong PivotTable areas. By reorganizing the Rows, Columns, and Values, the data becomes structured appropriately for charting.

Insert the PivotTable

Select your entire survey dataset, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'. Choose to place it on a New Worksheet.

Assign the Row Field

In the PivotTable Fields pane, click and drag your demographic category (e.g., 'Age Group') into the 'Rows' area. Make sure it is not accidentally placed in the 'Columns' area.

Assign the Column and Value Fields

Drag the survey response field (e.g., '1st Choice') into the 'Columns' area. Next, drag the exact same '1st Choice' field into the 'Values' area to count the occurrences of each response.

Insert the Chart

Select a cell inside your configured PivotTable, go to the 'Insert' tab, click on 'PivotChart' (or 'Chart'), and select a Stacked Column or Bar chart to visualize the responses.

Field Verification: Double-check that your Value field is set to 'Count of 1st Choice' rather than 'Sum', as text-based survey choices must be counted to display properly.

Solution 2

Standardize Data Structures Across Tables

If different tables produce varying PivotTables, standardizing your source data ensures consistent chart outputs.

Smart Data Visualization

Create Professional Charts from Survey Data with WPS Office

WPS Spreadsheet provides intuitive tools for building PivotTables and dynamic charts, allowing you to seamlessly analyze complex survey data with high accuracy and efficiency.

1. Open Survey Data: Launch WPS Spreadsheet and open your .xlsx file containing the survey responses.
2. Create a PivotTable: Highlight the data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Configure Fields: Use the right-side task pane to drag your demographic data into 'Rows' and your response data into both 'Columns' and 'Values'.
4. Insert Chart: With the PivotTable selected, click on 'Chart' under the Insert tab and choose your preferred visualization style.

Fully compatible with Microsoft Excel (.xlsx, .xls) file formats and PivotTable structures.Intuitive drag-and-drop interface for fast PivotTable configuration.Rich library of highly customizable chart templates, including specialized stacked bars and columns for survey visualization.Lightweight architecture ensuring smooth performance even with large survey datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Excel chart not picking up all survey responses?

This usually happens if your data range contains blank rows or if the PivotTable data source was not updated after adding new responses. Go to the PivotTable Analyze tab and click 'Change Data Source' to ensure all rows are included.

Can I combine multiple survey questions into one chart?

Yes, but you must first unpivot your data so that the questions are listed in a single categorical column. This allows the PivotTable to aggregate and chart the responses correctly.

What chart type is best for survey data with multiple choices?

A 100% Stacked Bar or Stacked Column Chart is highly recommended for survey data. It clearly visualizes the proportion of different choices across categories like age groups, making comparisons easier.

Why does my PivotTable show 'Sum' instead of 'Count' for text responses?

Excel defaults to 'Sum' if it detects numerical values. If your survey choices are numbers (e.g., a scale from 1-5), right-click the value in the PivotTable, select 'Value Field Settings', and change the calculation from 'Sum' to 'Count'.

Excel & Data Problems