Pivot Table Issues

How to Fix PivotTable Treating Numbers as Text and Not Summing

Muhammad Talha Sep 30, 2026 872 views

Question details

The user needs to make a PivotTable correctly identify and sum a numeric column that is currently being evaluated as text.

How to Fix PivotTable Treating Numbers as Text and Not Summing

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: Creating or updating a PivotTable where the source data contains numbers that have been imported or formatted incorrectly, causing the PivotTable to count instead of sum.
Observed behavior: The PivotTable classifies the numeric column as text and defaults to counting the values instead of summing them, even though a normal SUM formula calculates successfully on the exact same range.

Before you start

Before proceeding, check your source data column for empty cells, error values, or mixed data types (like numbers combined with letters), as these will immediately force the PivotTable to treat the entire column as text.

Solution 1Recommended

Convert Text to Numbers Using Text to Columns

This is the most effective method to quickly strip hidden characters like leading apostrophes and force the column into a true numeric format.

When numbers are imported from databases or other systems, they often contain hidden non-breaking spaces or leading apostrophes. The Text to Columns wizard clears these hidden formats in a few clicks.

Select the target column

Navigate to your source data sheet and highlight the entire column containing the problematic numbers.

Open Text to Columns

Go to the 'Data' tab on the top ribbon and click on the 'Text to Columns' icon.

Navigate the Wizard

Choose 'Delimited' in the first step and click 'Next' twice to reach the final step of the wizard.

Apply General formatting

Select 'General' as the Column data format and click 'Finish'. The text values will instantly convert to numbers.

Refresh your PivotTable

Right-click anywhere inside your PivotTable and select 'Refresh'. If the field still shows 'Count', remove it from the Values area and drag it back in to apply the 'Sum' aggregation.

Convert Text to Numbers Using Text to Columns

Check Field Selectors: Rebuilding the PivotTable cache by removing and re-adding the field is often necessary, as the PivotTable may retain the old data type in its memory.

Solution 2

Convert Text to Numbers Using Paste Special

Multiplying the text-formatted numbers by 1 forces the spreadsheet application to evaluate and convert them into actual numerical values.

Data Analysis Made Easy

Easily Manage and Clean Data with WPS Spreadsheet

WPS Office provides advanced data cleaning tools and robust PivotTable features to help you process large datasets and avoid formatting errors.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your PivotTable and source data.
2. Use Smart Tags: Highlight the column with text-formatted numbers and click the yellow error checking smart tag that appears next to the cells.
3. Convert to Number: Select 'Convert to Number' from the dropdown menu to instantly fix the data type.
4. Refresh PivotTable: Right-click your PivotTable and select 'Refresh' to accurately sum your data without rebuilding the table.

One-click smart tags to instantly convert text to numbersFully compatible with Microsoft Excel (.xlsx) PivotTables and data cachesIntuitive interface for refreshing data and managing complex calculationsFree, lightweight, and fast alternative to heavy office suites

Frequently Asked Questions

Why does my PivotTable default to 'Count' instead of 'Sum'?

PivotTables automatically default to 'Count' when the source column contains blank cells, text strings, errors, or numbers stored as text (such as those with leading apostrophes). Ensuring every cell in the column contains a true number will change the default behavior to 'Sum'.

I converted my data to numbers, but the PivotTable still counts. What should I do?

PivotTables cache source data to improve performance. Even after fixing the source, the cache might retain the old 'Text' classification. To fix this, untick the field from the PivotTable Fields pane, right-click the PivotTable to 'Refresh', and then check the field again to add it back to the Values area.

Why does a normal SUM formula work when the PivotTable fails?

Standard SUM formulas in the spreadsheet grid are designed to either ignore text strings entirely or coerce certain text-numbers into numeric values for the calculation. PivotTables, however, are strictly bound to data types; if they detect non-numeric data, they will classify the entire field as categorical (text) and count it instead.

Excel & Data Problems