Average in Excel Not Working? Common Causes and Fixes

If AVERAGE in Excel is not working, first check that the referenced cells contain numbers and that none contains an error; AVERAGE ignores blank cells and text in a range, but includes zero. With numbers in B2:B6, the basic formula is =AVERAGE(B2:B6). If the result is an error or seems wrong, the checks below will help identify why.

How Excel’s AVERAGE function works

AVERAGE adds the numeric values in its arguments and divides their sum by the count of those values. It is intended for an ordinary arithmetic mean, not a weighted average. For a range reference, Excel counts numbers, including dates and times stored as numbers, and ignores blank cells, text, and logical values such as TRUE or FALSE.

For example, with 12, 15, a blank cell, 18, and the text value 20 in B2:B6, Excel averages only 12, 15, and 18. The result is 15. A zero is different from a blank: if B4 contains 0 instead, that zero is included in both the sum and the count. This distinction explains many results that look unexpectedly low.

Text typed directly as an argument can behave differently from text in a referenced cell. For example, AVERAGE can count a numeric text argument supplied directly in the formula, while it ignores a number stored as text inside a referenced range. When troubleshooting, focus first on what the cells actually contain, rather than how they look on the sheet.

Check the formula and the range

Confirm that the function name is spelled correctly, that the parentheses are balanced, and that the range covers the intended data. A formula can be syntactically valid yet point at the wrong cells, omit the newest row, or include a total that should not be averaged. Click the formula cell and inspect the colored range outlines Excel displays while editing the formula.

With the values to average in B2:B6, this is the basic formula:

=AVERAGE(B2:B6)

When the values are in several separate ranges, list each range as an argument. Make sure each argument is separated with the separator your Excel installation expects. In Microsoft 365 for Windows, commas are common in formulas, but regional settings can require semicolons instead. If Excel rejects a formula containing commas, check the separator used by your installation rather than changing the function name at random.

Also check for an accidental extra cell reference. For instance, averaging B2:B6 when B6 is a summary or subtotal may count that summary as another observation. If the range is large, select it and look at Excel’s status bar; its average and count can provide a quick comparison with the formula’s result.

Fix numbers stored as text

One of the most common causes of an incomplete average is that some values are stored as text. This can happen after importing a CSV file, copying data from a website, using a leading apostrophe, or pasting values with spaces or other characters. A cell may look like the number 25 while Excel treats it as text, so a range-based AVERAGE skips it.

Select a problem cell. If Excel shows a warning icon, open it and choose Convert to Number when that option is offered.
Check the formula bar for a leading apostrophe, spaces, currency symbols typed into the value, or other unexpected characters.
Try selecting the affected column and choosing Data > Text to Columns, then finish the wizard without changing the delimiter settings. This can convert many text-formatted numbers into numeric values.
For a small range, re-enter a value in a fresh cell and see whether Excel recognizes it as a number. Then use the same correction method consistently for the rest of the data.

Changing a cell’s number format to Number does not necessarily convert text into a number; formatting controls display, not the underlying value. After conversion, check the average again and verify that the count of numeric entries is what you expect. Avoid converting values blindly if the text contains meaningful codes, identifiers, or leading zeros that must remain text.

Understand errors and unexpected results

If the result is #DIV/0!, Excel did not find any numeric values to average. The range may be genuinely empty, contain only text, or contain formulas that return empty text such as "". Check whether a filter, import, or formula has left the selected range without usable numbers. If an empty result is expected in some situations, you can display a message instead of an error.

With the values in B2:B6, this formula shows a message if AVERAGE has no numeric values:

=IFERROR(AVERAGE(B2:B6),"No numeric values")

Use that approach only when hiding the error is appropriate. IFERROR can also mask a different problem, such as an error already present in the source data. If the result is #VALUE! or another error, inspect each referenced cell for its own error and correct the source when possible. AVERAGE generally propagates errors in its referenced range rather than silently skipping them.

If the result is a number but is displayed as a date, the calculation may be fine: Excel stores dates as serial numbers, and an average of dates is another serial number. Change the result cell’s format to Number or General to see the underlying value, or apply a date format if an average date is what you intended. Be cautious about averaging dates when the dates represent different kinds of events or when a calendar-date interpretation would be misleading.

Check calculation mode and formula display

A formula that appears unchanged after you edit its inputs may be in a workbook set to manual calculation. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. Then press F9 to recalculate. On Mac, use the Formulas tab and its Calculation Options control if available in your Excel version; the menu and shortcut details can vary by release.

If the cell shows the literal formula instead of a result, check whether the cell is formatted as Text or whether the formula begins with an apostrophe. Change the format to General, edit the cell, and press Enter so Excel parses the entry again. Also check whether formula display is on: on Windows, Ctrl+` toggles Show Formulas. The grave accent key is usually near the upper-left of the keyboard. If the whole sheet displays formulas, turn that view off; the formulas themselves may be valid.

Another simple check is to select the formula cell and look in the formula bar. If Excel has accepted the formula, the bar should show the formula while the cell normally displays its calculated result. A cell that remains blank may have a custom number format hiding its display, or the formula may intentionally return an empty string. Those are display or formula-logic issues, not necessarily failures of AVERAGE.

Know what filters and hidden rows do

AVERAGE normally includes numeric values in rows hidden by a filter and rows hidden manually. That means the visible figures on screen can appear to have one average while AVERAGE reports another. This is not a calculation error: the function is averaging the cells in its reference, not just the rows currently visible.

For a filtered list where you want the average of visible values in B2:B100, use SUBTOTAL with function number 101:

=SUBTOTAL(101,B2:B100)

SUBTOTAL with 101 calculates an average while excluding filtered-out rows and manually hidden rows. Function number 1 also calculates an average and excludes filtered-out rows, but it includes manually hidden rows. Choose based on what “visible” should mean in your worksheet. If the list is not filtered and hidden-row behavior is not relevant, ordinary AVERAGE is usually clearer.

Choose the right average for the question

AVERAGE is not the right tool for every question involving a mean. If you need to average only values that meet a condition, use AVERAGEIF for one condition or AVERAGEIFS for multiple conditions. With categories in A2:A6 and corresponding values in B2:B6, this formula averages values for rows labeled East:

=AVERAGEIF(A2:A6,"East",B2:B6)

For an average that gives observations different importance, use a weighted average instead. For example, with weights in A2:A4 of 2, 3, and 5 and corresponding values in B2:B4 of 10, 20, and 30, the weighted result is 23: (2×10 + 3×20 + 5×30) ÷ (2+3+5). With those weights and values in the stated cells, use:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)

AVERAGEA is another function, but it follows different rules for text and logical values. In a referenced range, it can treat text as zero and TRUE as one, whereas AVERAGE ignores those entries. Do not switch to AVERAGEA simply because AVERAGE skips text; first determine whether those text and logical values should count in the result at all.

A practical troubleshooting sequence

When the result still does not make sense, work through these checks in order. This helps separate a bad formula from a data-quality issue:

Read the formula and confirm that it points to the intended cells, excluding headings and totals where appropriate.
Check whether any supposed numbers are text, and convert only the values that should be numeric.
Look for blank cells, formulas returning empty text, zeros, and errors. Decide which should be included rather than assuming they are interchangeable.
Ask whether filtered or hidden rows should count. If only visible rows should be averaged, use SUBTOTAL instead of AVERAGE.
Check calculation mode, cell formatting, and whether Show Formulas is enabled if the displayed result does not update or looks like a formula.
Compare Excel’s result with a small hand calculation using a few known values. Confirm both the sum and the number of values included.

This sequence also helps avoid a common misconception: AVERAGE does not automatically ignore zero, errors, filtered rows, or every value that looks unusual. Its treatment depends on the value type and the function used. Correcting the data or choosing a more specific function is usually more reliable than altering the result after the fact.

Frequently Asked Questions

Why does Excel AVERAGE return #DIV/0!?
There are no numeric values in the referenced cells, so Excel has nothing to divide by. Check for numbers stored as text, an empty range, or formulas returning empty text.

Does AVERAGE include zero?
Yes. A numeric zero counts as a value and is included in the divisor. A blank cell is ignored, so replacing blanks with zeros changes the result.

Why is AVERAGE ignoring numbers in my cells?
The values may be stored as text, often after an import or paste. Convert them to numeric values and check that no apostrophe or extra character is present.

Does AVERAGE include filtered-out rows?
Yes. AVERAGE uses its referenced cells regardless of filters or hidden rows. Use SUBTOTAL with function number 101 when you want an average of visible rows that excludes manually hidden rows too.

Why does my average show as a date?
Excel dates are stored as numbers. The result cell may have a date format applied; change it to Number or General to display the serial value, or use a date format if the result is meant to represent an average date.

What is the difference between AVERAGE and AVERAGEIF?
AVERAGE includes numeric values in its arguments. AVERAGEIF includes only values associated with cells that meet a condition, such as a matching region or category.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.