Pivot Table Average Not Working? Fix the Source Data

If a PivotTable average is missing, showing Count, or giving an unexpected result, check the value field’s summary setting first: right-click a value in the PivotTable, choose Summarize Values By, and select Average. If Average is unavailable or the result is still wrong, inspect the source column for text, blanks, or an incomplete range, then refresh the PivotTable.

Set the value field to Average

Excel chooses a default calculation when you add a field to the Values area. Numeric fields usually start as Sum, while a field containing text or mixed data may start as Count. That default does not tell you which summary is appropriate; it only reflects how Excel interpreted the source field.

Click a cell in the PivotTable’s Values area that belongs to the field you want to change.
In Excel for Microsoft 365 on Windows, right-click that cell and choose Summarize Values By > Average.
If you need to confirm which field is being changed, right-click the value and choose Value Field Settings. On the Summarize Values By tab, select Average and choose OK.

You can also open the settings from the PivotTable Fields pane: use the menu beside the field in the Values area and choose Value Field Settings. Check that the field is in Values, not Rows or Columns. A field placed in Rows labels categories; it does not calculate an average. If the field is already set to Average, continue troubleshooting rather than repeatedly changing the same option.

On a Mac, the equivalent settings are available from the value field’s context menu or Value Field Settings dialog, though ribbon labels and menu placement can differ slightly. Excel for the web has PivotTable controls, but some settings can be easier to locate in the desktop app. The instructions above refer to Excel for Microsoft 365 on Windows.

How a PivotTable average is calculated

For an ordinary PivotTable, Average means the arithmetic mean of the numeric source values represented by that group: Excel adds those numbers and divides by the number of numeric entries. Blank cells and text entries are not numeric observations in that calculation. A numeric zero, however, is a real value and is included. This difference explains many results that look too high or too low.

For example, suppose a category has the numeric values 10, 20, and 30, plus one blank. Its average is 20, because the three numbers total 60 and the blank is not an observation. If the blank is actually meant to represent zero, the average should instead be 15. The source data must contain an actual 0 for Excel to include it that way.

A PivotTable’s average can also differ from an average calculated over displayed subtotals. Excel generally recalculates a grand total from the underlying records; it does not simply average the visible category averages. That is usually the statistically meaningful result when groups contain different numbers of records.

Check whether the source values are really numbers

A cell can look like a number while being stored as text. Common causes include importing a CSV file, leading apostrophes, spaces before or after values, or a source system that exports numbers as text. Text-formatted values can lead Excel to default to Count, be excluded from an average, or make a field behave inconsistently.

Check a few source cells rather than relying on appearance alone. Select a suspicious cell and look for a warning indicator or a number stored as text notice. In a blank cell, you can test a source value with ISNUMBER; if it returns FALSE for an entry that should be numeric, investigate the data type. Also inspect the formula bar for leading apostrophes or unexpected spaces.

For values flagged as numbers stored as text, select the affected cells and use the warning menu’s Convert to Number option, if it appears.
If the values came from a delimited file, review the import settings and assign the column a numeric data type when importing.
Remove unwanted spaces or characters from the source, then convert the cleaned entries to numbers before refreshing the PivotTable.
Check that decimal and thousands separators match the regional settings used to interpret the imported data.

Do not convert every text entry automatically if the column intentionally contains labels such as N/A or Pending. Decide what those entries mean first. If they represent missing measurements, leaving them blank may be appropriate; if they mean zero, replace them with numeric zero only when that interpretation is correct for the data.

Look for blanks, errors, and mixed entries

Blanks are not the same as zero, and text placeholders are not the same as either. A source column containing 12, 15, a blank, and the text N/A has two numeric observations for an average, not four. If many records are blank or marked with text, the PivotTable average can be valid but may describe a smaller group than expected.

Filter the source column and inspect its distinct-looking entries. Watch for values such as a dash, a space, N/A, or an empty-string result from a formula. They may look blank in the worksheet but are not necessarily stored as genuinely empty cells. Decide whether each kind of entry should be excluded, corrected, or represented by a numeric value. Keep the original data intact until the intended treatment is clear.

Also look for error values such as #VALUE! or #DIV/0! in the source. Correct the underlying cause where possible. A PivotTable may show an error or fail to produce the intended summary when its source contains errors, depending on the operation and data model. Do not hide errors merely to make the average appear; determine whether they represent invalid records or a calculation problem that needs fixing.

Confirm the source range and refresh the PivotTable

A PivotTable does not necessarily include rows added after it was created, and it does not always update its displayed results immediately when source data changes. If new numeric records are missing, first confirm that the PivotTable’s data source covers them. Select the PivotTable and use PivotTable Analyze > Change Data Source to inspect or adjust the range in the Windows desktop app.

For data that will grow over time, format the source range as an Excel Table with Insert > Table, or Home > Format as Table. A PivotTable based on an Excel Table can include rows added to that table after you refresh it. After editing or expanding the source, right-click the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To refresh every PivotTable in the workbook, use Data > Refresh All.

Refreshing updates the PivotTable from its source; it does not repair text values or change an incorrect summary setting. If a refreshed result is still wrong, check both the field’s aggregation and the source data. If you changed the source range, verify that the new range includes the intended header row and all relevant records, without accidentally including unrelated totals or notes.

Interpret Count, zero, and unexpected results

If the field shows Count instead of Average, select Average in Value Field Settings and then inspect the data type if the result remains unavailable or surprising. Count counts nonblank entries, including text; Count Numbers counts numeric entries. A field defaulting to Count is a useful clue that the column may contain text, but it is not proof that every entry is text.

If the average is lower than expected, check for zeros and for records included in the group that should not be there. If it is higher than expected, check whether low values are stored as text or missing, whether the source range excludes records, and whether blanks were intended to represent zero. Also check report filters, slicers, and row or column filters: they can restrict which records contribute to the visible average.

Number formatting can create another false alarm. A value field may be displayed with no decimal places even though its average has decimals. Right-click a value, choose Number Format, and select a suitable number format and decimal-place count. Changing display formatting affects how the result looks, not the underlying average.

Why the grand total may not average the displayed averages

Consider a report with two groups. Group A has one measurement, 10, so its average is 10. Group B has three measurements, 20, 20, and 20, so its average is 20. The grand total average is 17.5: the four underlying measurements total 70, and 70 divided by 4 is 17.5. It is not 15, the simple average of the two displayed group averages.

This is a weighting issue, not a PivotTable error. Each source observation contributes once to the overall average, so a group with more observations contributes more values. If your goal is instead to give every group equal weight, averaging group averages may be appropriate, but that is a different calculation. State which question the report is meant to answer before comparing its grand total with a manual calculation.

When checking the PivotTable manually, use the same filtered records and the same treatment of blanks and text. Comparing an average of all records with an average of subgroup averages will not produce a fair check unless the groups have equal sizes or the subgroup averages are weighted appropriately.

Data Model and special PivotTable cases

Some PivotTables use the Excel Data Model or an external OLAP source rather than a simple worksheet range. In those cases, the available summarization options can differ, and the source may define measures or calculations centrally. If Average is missing from the usual value-field options, check whether the PivotTable is based on the Data Model or an external connection before assuming the source column is malformed.

For a Data Model report, a measure can define how a value is aggregated. A measure named Average Sales, for example, may follow a different calculation from simply changing a regular worksheet field to Average. Review the measure definition or ask the workbook’s owner how it is intended to work. Do not replace a measure with a basic average unless the two calculations answer the same question.

Calculated fields and formulas beside a PivotTable can also complicate comparisons. A formula outside the PivotTable may not expand or recalculate in the same way as the PivotTable itself. When possible, verify the underlying source records and the value-field settings first, then check any separate formulas or model measures used in the report.

Frequently Asked Questions

Why does my PivotTable show Count instead of Average?
A PivotTable may default to Count when Excel interprets the field as containing text or mixed entries. Right-click a value and choose Summarize Values By > Average. If the option is unavailable or the result seems wrong, check whether the source entries are stored as numbers and whether the PivotTable uses the Data Model.

Does a PivotTable average include zeros?
Yes. Numeric zeros are included in the average. Truly blank cells and text entries are not numeric observations, so they do not contribute to the average in the same way. Use zero only when zero is the correct meaning of the source record.

Why is my PivotTable grand total different from the average of the row averages?
The grand total is generally calculated from the underlying records, so groups with more records contribute more observations. The simple average of visible group averages gives each group equal weight instead. Those results match only in particular cases, such as groups with equal sizes.

How do I make a PivotTable include new rows?
Check PivotTable Analyze > Change Data Source to confirm the range includes the new records, then refresh. For a growing dataset, use an Excel Table as the source and refresh the PivotTable after adding rows.

Why does the average look rounded or show too few decimals?
The displayed number format may hide decimal places without changing the underlying result. Right-click a PivotTable value, choose Number Format, and select an appropriate number of decimal places to inspect the average more closely.

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.