Chart & Visualization Issues

How to Create a Chart from an Excel PivotTable with Categories

Maira Mehtab Sep 21, 2026 869 views

Question details

The user wants to create a chart from an existing PivotTable, specifically placing categorical data on the category axis and numeric data on the value axis.

Product: Excel
Device & OS: not provided
Scenario: Creating a PivotChart from a PivotTable to visually analyze performance categories against numerical time values.
Observed behavior: The categorical field cannot be placed on the value axis, as the value axis requires numeric data to plot the chart correctly.

Before you start

Ensure your source data is organized in a strict tabular format with clear column headers (e.g., Squad, Month, Lead Time) and contains no completely blank rows or columns.

Solution 1Recommended

Build the PivotTable and Insert a PivotChart

Properly structure your PivotTable fields to automatically generate an accurate PivotChart with the correct categorical and numeric axes.

To successfully chart categorical data alongside numeric values, you must arrange your PivotTable correctly before generating the chart. Categorical axes and value axes serve different structural purposes in data visualization.

Select your source data

Highlight your dataset containing the columns Squad, Month, and LEAD TIME. Navigate to the Insert tab on the ribbon and click PivotTable.

Configure Rows and Columns

In the PivotTable Fields pane on the right side of the screen, drag the 'Month' field into the Columns area and the 'Squad' field into the Rows area.

Assign the Numeric Value

Drag the 'LEAD TIME' field into the Values area. Verify that it displays as 'Sum of LEAD TIME' so it acts as the quantitative measurement.

Insert the PivotChart

Select any cell inside your newly configured PivotTable. Go to the Insert tab, click PivotChart, and choose your preferred chart type (such as a Column or Bar chart).

Verify Chart Axes

Check the generated chart to ensure that 'Squad' is plotted along the category (horizontal) axis, while 'LEAD TIME' correctly scales along the numeric value (vertical) axis.

Value Axis Constraints: The value axis of a chart must exclusively contain numeric data. Text-based categorical fields like 'Squad' cannot be mathematically aggregated and will not plot correctly if dragged into the Values field.

Solution 2

Provide a Sanitized Sample File for Advanced Troubleshooting

If your PivotChart still does not display the categories correctly, preparing a sanitized sample file can help diagnose underlying data formatting issues.

Visualize Data Easily

Create PivotTables and Charts Seamlessly with WPS Office

WPS Spreadsheet provides a highly intuitive interface for building PivotTables and PivotCharts. You can effortlessly manage categorical axes and numeric values without confusing errors.

1. Open data in WPS Spreadsheet: Launch WPS Office and open your dataset containing the categorical and numeric data.
2. Insert PivotTable: Navigate to the Insert tab and click 'PivotTable' to generate your data summary framework.
3. Organize Data Fields: Drag your categorical fields (like Squad) into the 'Rows' box and numeric fields (like Lead Time) into the 'Values' box.
4. Generate PivotChart: Click 'PivotChart' under the PivotTable Analyze tab to instantly visualize your data with the correct axes.

Free to use with a lightweight installationFully compatible with Microsoft Excel (.xlsx) formats and featuresIntuitive drag-and-drop PivotTable fields interfaceRich variety of built-in PivotChart templates

microsoft office alternative - wps office

Frequently Asked Questions

Why does my numeric field show up as 'Count' instead of 'Sum' in the PivotTable?

If your numeric column contains any blank cells, hidden spaces, or text values, the software defaults the aggregation to 'Count'. Ensure all data in your Lead Time column is strictly numeric, then click the field in the Values area, select 'Value Field Settings', and change it to 'Sum'.

Why can't I put my text categories on the value axis?

The value axis of a PivotChart is strictly designed for quantitative data that can be mathematically calculated (like sums, averages, or variances). Text categories cannot be aggregated in this manner, so they must be placed on the category (horizontal) axis.

How do I refresh the PivotChart when my source data changes?

PivotTables and PivotCharts do not update automatically when source data is altered. You must right-click anywhere inside the PivotTable or PivotChart and select 'Refresh' from the context menu to pull in the newest data.

Excel & Data Problems