Excel Pivot Table Values Not Summing? Fix the Cause

Charts & pivot tables · Updated November 18, 2026

If Excel PivotTable values are not summing, first check that the source column contains real numbers rather than numbers stored as text, then set the value field to Sum and refresh the PivotTable. A field that contains text, mixed data types, or no usable numeric values may default to Count or show zero instead.

Why a PivotTable counts instead of sums

Excel chooses a default calculation based on the data it detects in the source field. For a consistently numeric field placed in the Values area, the usual default is Sum. If Excel detects text in that field, it commonly defaults to Count, which counts nonblank entries rather than adding their values. That is why a column that looks like amounts can produce a count of transactions.

Numbers imported from a website, a text file, or another system are often stored as text. They may look exactly like numbers in the worksheet, but Excel does not treat them as numeric values. A leading apostrophe, an unusual space, or inconsistent decimal and thousands separators can also prevent recognition. A column with a mixture of numbers and text is especially likely to cause a surprising default.

Check the field name in the PivotTable Fields pane as well. A field must be in the Values area to be calculated; if it is in Rows or Columns, Excel uses it to label and arrange the report instead. These are separate issues: moving a field into Values does not by itself fix text-formatted numbers.

Set the value field to Sum

If the source values are genuinely numeric, you can change the summary calculation without rebuilding the PivotTable. In Excel for Microsoft 365 on Windows:

  1. Click a number in the PivotTable’s value column.
  2. Right-click and choose Summarize Values By > Sum. Alternatively, choose Value Field Settings, select Sum under Summarize Values By, and click OK.

The field may then be labelled “Sum of Sales” rather than “Count of Sales.” You can edit the custom name in the Value Field Settings dialog, but the new name should be different from the exact source field name. If Sum is unavailable, or selecting it still produces zero, fix the source data first; the summary setting cannot turn text into numbers.

On Excel for Mac, click a value and use the contextual PivotTable options or Control-click the value to open the summary settings. The precise ribbon arrangement can vary by release, but the relevant dialog is Value Field Settings, where you choose Sum. In Excel for the web, select a value and look for the PivotTable field or value settings; some desktop options may not be available in every web release.

Check whether the source values are real numbers

Go to the source worksheet and inspect several cells from the field, including the first, middle, and last records. Text values are often left-aligned by default while numbers are right-aligned, although cell alignment can be customized, so alignment is only a clue. A small green triangle may indicate a number stored as text. Also look for apostrophes, spaces before or after digits, currency symbols typed directly into cells, and values with unexpected separators.

For a direct test, assume the source values are in B2:B100 and test B2 in an unused cell. Enter =ISNUMBER(B2). If B2 contains a real number, the result is TRUE; if it contains a numeric-looking text string such as “125”, the result is FALSE. Repeat the check on cells that behave differently, because a column can contain a mixture.

Check the source column’s number format too, but do not confuse formatting with conversion. Choosing Home > Number Format > Number changes how a numeric value is displayed; it does not reliably convert text into a number. A cell can be formatted as Number and still contain text.

Convert text-formatted numbers

Choose a conversion method that fits the source and preserve the original column until the results are checked. For a straightforward text value that Excel can recognize, add a helper column. With the original text in B2, enter =VALUE(B2) in C2 and fill it down. If B2 contains the text 125, the result in C2 is the numeric value 125. Review the helper results, then use that numeric column in the PivotTable source.

For a whole column of values marked with green error indicators, select the cells, click the warning icon, and choose Convert to Number if that option appears. Another option is Data > Text to Columns: select the source cells, open the wizard, leave the data as delimited if no splitting is needed, and finish the conversion. Test on a copy first if the column contains mixed formats or identifiers that must remain text, such as product codes with leading zeros.

Imported separators need particular care. A value such as 1.234 may mean one and 234 thousandths in one regional format, but it may be interpreted differently in another. Use the correct decimal and thousands separators when importing or converting, and verify a few known totals. Do not remove punctuation blindly: a comma or period can change the value substantially.

After conversion, update the PivotTable’s source to use the numeric field if you created a helper column. Then refresh. If the source is an Excel table, adding the helper column to the table usually makes it available to the PivotTable after a refresh; for a plain cell range, check that the source range includes the new column.

Refresh the PivotTable after fixing the data

A PivotTable is a summary of its source data, not a live display of every source-cell edit. After correcting values, click inside the PivotTable and choose PivotTable Analyze > Refresh on Windows, or right-click the PivotTable and choose Refresh. To update all PivotTables and connections in a workbook, use Data > Refresh All.

Refresh does not always solve a data-type problem by itself. If Excel has already built the PivotTable cache with a field treated as text, converting some cells and refreshing may not be enough in every workbook. Confirm that the source range includes the corrected column. If the field still behaves as text, create a new PivotTable from the corrected source or change the source and rebuild it, then set the field to Sum.

When the source data is a range that grows over time, new rows may fall outside the PivotTable’s source range. Converting the source to an Excel table with Insert > Table can make the source easier to expand as records are added. Refresh after adding records; the table’s expansion does not mean an existing PivotTable has automatically recalculated its summary.

Understand zero totals, blanks, and filters

A PivotTable showing zero is not always the same problem as one showing Count. First confirm that the field is summarized by Sum. Then inspect the source values and the report’s filters. Text entries cannot contribute to a numeric sum, while blank cells contribute nothing. A filter or slicer may also exclude the records you expect, and a row or column label can hide categories that are collapsed.

Compare a small, visible group of source records with the corresponding PivotTable result. Check whether the records are included, whether the source amounts are numeric, and whether the PivotTable is filtered. If some cells contain errors, correct those errors in the source and refresh; errors can affect a PivotTable’s ability to summarize a field as expected.

Also distinguish an actual zero from a blank display. A PivotTable may be configured to show empty cells differently, and a field with no matching records can appear empty. The number format can hide small values by rounding them to zero, too. Increase the displayed decimal places temporarily to see whether a tiny value is being rounded rather than omitted.

Sum is different from Show Values As

Value Field Settings includes two concepts that are easy to mix up. Summarize Values By determines the basic calculation, such as Sum, Count, or Average. Show Values As changes how that result is displayed, for example as a percentage of a total or as a running total. If a PivotTable displays percentages, it may still be summing the underlying values before showing them as percentages.

For a straightforward total, choose Sum under Summarize Values By and leave Show Values As at its default setting, typically No Calculation. To show each category’s share of a grand total, keep the appropriate underlying summary and deliberately choose a percentage option under Show Values As. That produces a different display from a raw sum, so the right choice depends on whether the question is “How much?” or “What share?”

Other causes and useful checks

If Sum is selected but the result remains unexpected, work through the structure of the report as well as the source:

  • Wrong field: Make sure the Values area contains the amount or quantity field, not an ID, date, or category that happens to contain numbers.
  • Duplicate records: A correct sum can be larger than expected if the source contains duplicate rows. A PivotTable summarizes all included records; it does not know which ones are accidental duplicates.
  • Hidden records: Source worksheet filters do not always mean the PivotTable summarizes only the rows currently visible. Check the PivotTable’s own filters and the source range.
  • Data Model: A PivotTable built from the Data Model may use measures or different calculation options. Check the measure definition and whether the field is being aggregated as intended.
  • Wrong unit or scale: Imported values may represent cents rather than dollars, minutes rather than hours, or thousands rather than single units. Confirm what the source system exports.

For a quick diagnosis, make a small test PivotTable from a few known records and place only the suspected numeric field in Values. If it offers Count by default, inspect the source type. If it offers Sum and the test total is right, the issue is more likely a filter, source range, or layout setting in the original PivotTable.

Frequently Asked Questions

Why does my Excel PivotTable say Count of instead of Sum of?
Excel likely detected text or mixed data types in the source field, so it defaulted to counting entries. Convert the source values to numbers, refresh the PivotTable, and choose Sum in Value Field Settings.

Can I change Count to Sum without rebuilding the PivotTable?
Usually, yes. Right-click a value, choose Summarize Values By > Sum, or open Value Field Settings and select Sum. If the data is stored as text, convert it first; changing the setting alone may not produce a valid total.

Why does my PivotTable still show zero after I convert the numbers?
Check that the PivotTable uses the corrected source column and includes the relevant records, then refresh it. Filters, an outdated source range, or values that remain text can all leave the total at zero.

Does changing a cell format to Number convert text to numbers?
No. Number formatting changes display, not the underlying data type. Use a conversion method such as Convert to Number, Text to Columns, or a helper column with VALUE, then refresh the PivotTable.

Why are some values summed but other records seem missing?
Look for blank or nonnumeric source cells, PivotTable filters, and records outside the source range. Check the source rows against the report and confirm the PivotTable has been refreshed.

Will a PivotTable refresh automatically when I edit its source?
Not necessarily. Refresh the PivotTable after changing source data. You can also use Data > Refresh All to update multiple PivotTables and connections in the workbook.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.