Pivot Table Issues

Fix Excel PivotTable Treating Numeric Values as Text

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs to summarize numeric NAV values by asset class in a PivotTable, but the values are displaying as separate text items instead of being calculated as totals.

Product: Excel
Device & OS: not provided
Scenario: Creating a PivotTable to summarize numerical data totals grouped by specific categories.
Observed behavior: The PivotTable treats the numeric fields as text, placing them in rows or columns as separate items instead of calculating their sum.

Before you start

Ensure your source data range does not include blank cells or headers in the data rows, as these can cause the application to format the entire column as text.

Solution 1Recommended

Move the Data Field to the Values Area

Correctly positioning the field within the PivotTable Fields pane ensures the application calculates the sum rather than listing individual items.

Open PivotTable Fields

Click anywhere inside your existing PivotTable to make the PivotTable Fields pane appear on the right side of your screen.

Locate the Data Field

Find the 'NAV' field (or your specific numeric field) in the upper section of the fields list.

Move to Values Area

Drag the field from the 'Columns' or 'Rows' area and drop it into the 'Values' area at the bottom right.

Verify Summarization

Check that the field now says 'Sum of NAV'. If it says 'Count of NAV', right-click a value in the table, hover over 'Summarize Values By', and select 'Sum'.

Calculation Restored: Placing numeric fields in the Values area allows proper aggregation, generating the totals for each asset class.

Solution 2

Format Source Data as Numeric and Remove Hidden Characters

If placing the field in the Values area still results in text behavior, the source data may contain text-formatted numbers or hidden characters.

Analyze Data Easily

Create and Manage PivotTables Effectively in WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive interface for creating PivotTables, ensuring your numeric data is automatically summarized correctly without formatting conflicts.

1. Open Your Data: Launch WPS Spreadsheet and open the file containing your dataset.
2. Insert PivotTable: Highlight your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
3. Configure Fields: In the task pane, drag your category fields into 'Rows' and your numeric fields into 'Values'.
4. Automatic Summation: WPS Spreadsheet will automatically detect the numbers and summarize them as a 'Sum' rather than treating them as text.

Fully compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.Intelligent data recognition automatically places numeric fields in the Values area.Lightweight software that processes complex calculations and large datasets smoothly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable say 'Count of' instead of 'Sum of'?

This happens when your source data contains blank cells, text entries, or hidden characters within a numeric column. The application defaults to 'Count' to prevent calculation errors. Converting the entire column data to pure numbers will resolve this.

How do I force a PivotTable to sum instead of count?

Right-click any value in the PivotTable, select 'Summarize Values By' from the context menu, and choose 'Sum'. Note that this will only output a correct calculation if the underlying source data is actually numeric.

Does updating the source data formatting automatically fix the PivotTable?

No, PivotTables do not update their cache automatically. After you fix the formatting in your source data, you must right-click the PivotTable and select 'Refresh' for the structural changes to take effect.

Excel & Data Problems