Pivot Table Formula Errors in Excel: Causes and Fixes

Charts & pivot tables · Updated November 20, 2026

A PivotTable formula error usually comes from an error in the source data, a calculated field that does not work as intended, or a regular worksheet formula placed where the PivotTable can overwrite it. Check the source rows first, then refresh the PivotTable and confirm whether the calculation belongs in the source data, a PivotTable calculated field, or a separate worksheet cell.

What a PivotTable formula error means

A PivotTable summarizes records from a source range, Excel table, or data model; it is not normally a grid of independent formulas that you can edit cell by cell. When a result looks wrong or shows an error, the cause may be in the original records, the PivotTable’s calculation settings, or a formula outside the report that points to its cells. Those situations can look similar, but they need different fixes.

For example, a source row containing a division-by-zero error may cause an error to appear in the PivotTable. A PivotTable that shows a count instead of a sum may have a data-type problem or the wrong Value Field Settings. And a formula typed directly into a PivotTable body cell may disappear or shift when the report is refreshed or rearranged.

First identify where the error appears: in a source-data cell, in a PivotTable value, in a calculated field, or in a normal formula beside the PivotTable. That simple distinction often narrows the diagnosis more quickly than rebuilding the report.

How PivotTable calculations differ from worksheet formulas

A standard PivotTable groups source records by fields such as date, product, or region, then summarizes a numeric field using options such as Sum, Count, or Average. You change that summary through the PivotTable rather than editing its displayed result cells. Excel recreates those cells when the layout or source changes.

A calculated field is different from a formula typed into a cell. In Excel for Microsoft 365 on Windows, select a cell in the PivotTable and use PivotTable Analyze > Fields, Items & Sets > Calculated Field for a conventional PivotTable. A calculated field refers to source-field names, not worksheet addresses such as B2. It is evaluated using the source records and then summarized, so its result may not equal a calculation made from the final displayed totals.

That distinction matters for ratios. If each record has profit and sales, a calculated field that divides profit by sales and is then summed may add the individual record-level ratios. It does not automatically mean “total profit divided by total sales.” For a ratio of totals, use a suitable data-model measure or calculate from the PivotTable’s total values outside the report.

Calculated-field options depend on how the PivotTable was created. A PivotTable built using the Data Model has different calculation tools; use a measure in the model rather than expecting the standard Calculated Field command to work the same way. If the command is unavailable, check the PivotTable’s source and creation method.

Recognize the error before changing anything

Read the displayed message and inspect the source rows that contribute to the affected result. Common worksheet errors can pass through from the source, while a wrong-looking number may not be an error at all—it may simply be summarized or formatted in an unexpected way.

  • #DIV/0! usually points to a division by zero or a blank denominator in a source formula or a separate worksheet formula.
  • #VALUE! often indicates that a calculation received text or another value it cannot use as expected. Check for numbers stored as text and inconsistent source entries.
  • #REF! means a formula refers to an invalid cell or range. Look for deleted or moved references in the source or in formulas beside the PivotTable.
  • #NAME? often means Excel does not recognize text in a formula, such as a misspelled function or an unquoted text value. In a calculated field, also verify that the referenced source-field names are correct.
  • A number that appears as a Count instead of a Sum is usually a summary-setting or source-type issue, not a formula error. A text value in an otherwise numeric column can lead Excel to choose Count.

Do not assume every error displayed in a report was created by the PivotTable. Find the matching source records and inspect the underlying values before changing the report design.

Fix errors in the source data

If a source formula is producing an error, correct the data or formula there and refresh the PivotTable afterward. For instance, a unit-price calculation needs a clear policy for rows where quantity is zero. The following example returns a blank for a zero denominator rather than displaying a division error; decide whether a blank is appropriate for your reporting before using it.

Assuming revenue is in B2 and units are in C2, with the formula entered in D2 for a unit-price result: =IFERROR(B2/C2,""). If B2 is 120 and C2 is 4, the result is 30. If the calculation errors, the result is blank. IFERROR can also hide problems other than division by zero, so it should not be used to conceal data-quality issues that need investigation.

Check that each source column contains the type of information its heading implies. A date column should contain real Excel dates, and a sales column should contain numbers rather than a mixture of numbers and labels such as “pending.” Look for stray spaces, inconsistent spellings in category fields, and errors in calculated source columns. Correcting these at the source is often more dependable than trying to repair every affected summary.

When the source is an Excel table, new rows are generally included as the table expands. A fixed cell range may not include newly added records. Confirm the source reference before concluding that a refresh failed.

Repair a calculated field or summary setting

For a conventional PivotTable, open PivotTable Analyze > Fields, Items & Sets > Calculated Field, select the field, and check its formula and name. Verify that each referenced name matches a source field and that the arithmetic reflects the intended calculation. The formula uses field names rather than cell references. For example, with source columns named Sales and Cost, a calculated field named Gross Profit can use =Sales-Cost. This subtracts Cost from Sales for the source records used by the calculation.

If the problem is an incorrect summary, right-click a value in the PivotTable, choose Summarize Values By, and select the appropriate option. You can also open Value Field Settings to review the summary and number format. Sum is suitable for additive amounts; Average gives a mean of the underlying values; Count counts records or entries rather than adding them.

Be careful when summarizing percentages and ratios. The average of individual percentages is not necessarily the same as the percentage calculated from the totals. If a report needs a weighted rate, document the intended numerator and denominator, then calculate that rate from the corresponding totals or use a model measure designed for it.

Keep ordinary formulas outside the PivotTable

If you need a calculation based on displayed PivotTable results, put it in a normal worksheet cell outside the PivotTable’s expanding area. A formula inside the body can be overwritten when the PivotTable refreshes, expands, or changes shape. Leave room for the report to grow, and verify any adjacent formulas after changing PivotTable fields.

For example, with total profit in B5 and total sales in C5, and a separate formula cell in D5, enter =B5/C5. If B5 is 3,000 and C5 is 20,000, the result is 0.15, or 15% when formatted as a percentage. This calculates the ratio of those displayed totals, not a sum or average of row-level ratios. If C5 could be zero, decide how the report should handle that case rather than letting an unexpected error pass unnoticed.

GETPIVOTDATA can retrieve a value from a PivotTable by naming its data field and item fields, which can make a report formula less dependent on the position of a result cell. Excel may create this function automatically when you type a reference by selecting a PivotTable value. If a formula using it returns an error, check that the data-field caption and item names match the current PivotTable and that the requested item exists. A normal cell reference may be simpler when the report layout is fixed.

Refresh and check the PivotTable source

After fixing a source value or formula, refresh the report: select the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections as well. Refreshing updates the PivotTable from its source; it does not repair a broken source formula or automatically expand every fixed source range.

If a corrected record is still missing, select the PivotTable and inspect PivotTable Analyze > Change Data Source. Confirm that the range covers the intended records or that the correct table is selected. If the source is an external connection or Data Model, check that the connection or model data has updated too. A PivotTable can refresh successfully and still show old or incomplete results if it is connected to an unchanged source.

Also consider whether the report is showing cached items or filtered data. A filter, slicer, or grouped date field can hide records without producing a formula error. Clear or review filters and confirm that the displayed period and categories match the question the report is supposed to answer.

A practical troubleshooting order

Use this sequence to avoid changing several settings at once and making the real cause harder to find:

  1. Note the exact error or unexpected result and identify whether it appears in source data, a PivotTable value, a calculated field, or a separate formula.
  2. Inspect the contributing source records for worksheet errors, blanks, text in numeric columns, and invalid denominators.
  3. Check the PivotTable’s Value Field Settings and filters, then confirm the source range or table includes the required records.
  4. Correct the source or calculation, refresh the PivotTable, and compare the resulting total with a small hand-check of the relevant records.
  5. Change the calculation method only after confirming what the desired number means—for example, a sum, an average, or a ratio of totals.

For recurring reports, keep raw source data separate from the summary, use consistent column headings and data types, and document special rules such as how zero denominators should be treated. A small validation total or sample check can help reveal a broken source or changed calculation before the report is shared.

Frequently Asked Questions

Why does my PivotTable show #DIV/0!?
Check the source formulas for rows with a zero or blank denominator, and check any formula outside the PivotTable that divides by a displayed total. Correct the underlying issue, then refresh the PivotTable.

Why can’t I type a formula into a PivotTable cell?
PivotTable result cells are controlled by the report and may be replaced or moved when it refreshes or changes layout. Use a calculated field or model measure when appropriate, or put a regular formula in a separate cell outside the PivotTable.

Why is my PivotTable counting instead of summing?
Check whether the source column contains text, blanks, or mixed data types, and open Value Field Settings to see which summary is selected. Standardize the source values, refresh, and select Sum if that is the intended calculation.

Will refreshing fix a formula error?
Only if the source or connection has already been corrected or updated. Refreshing recalculates the report from its source; it does not repair a bad source formula, a wrong calculated field, or an incomplete source range.

Can a calculated field calculate a percentage of totals?
Not necessarily. A standard calculated field is evaluated using source records and then summarized, which can produce a different result from dividing the final totals. For a ratio of totals, calculate from those totals outside the PivotTable or use an appropriate Data Model measure.

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.