Excel Not Showing Average? Fix the Formula and Display

Errors & fixes · Updated November 18, 2026

If Excel is not showing an average, first check whether you want the result in a worksheet cell or on the status bar. For a cell result, enter =AVERAGE(B2:B10) in an empty cell and press Enter; if you expect a quick status-bar result, select the numeric cells and make sure Average is enabled on the status bar.

Start with the kind of average you expect

Excel can show an average in two different places. A formula puts a result in a cell that can be used elsewhere in the workbook. The status bar shows a temporary summary of the currently selected cells. An average missing from one place does not necessarily mean the other method is broken.

For a worksheet formula, with the numbers 10, 20, 30, and 40 in cells B2:B5, enter =AVERAGE(B2:B5) in another cell. The result is 25: Excel adds the four values to get 100 and divides by 4. If you want the result to update as the source values change, use the formula rather than copying a status-bar figure.

For a quick check, select the cells containing the values. Excel can display Average, Count, and Sum on its status bar at the bottom of the window. This summary is not inserted into the sheet, and it changes when you make a different selection.

Make Average appear on the status bar

If selecting numbers shows a count or sum but no average, the Average status-bar option may be switched off. In Excel for Microsoft 365 on Windows, right-click the status bar at the bottom of the Excel window and select Average in the list of available calculations. Then select the numeric cells again.

The status bar summarizes eligible numeric values in the current selection. Selecting a heading or a range with no numeric values will not give the average you expect. If your selection contains a mixture of numbers and text, Excel calculates the summary from the numeric cells, rather than treating every visible item as a number. The status-bar calculation is also temporary: it is not a formula and will not be included if you copy a nearby cell.

On a Mac, Control-click or right-click the status bar to open its available calculation options, then enable Average. Excel for the web has a more limited interface than the desktop app, so if the status-bar option is unavailable in your browser, calculate the result in a cell with AVERAGE instead.

Check the formula and its range

A basic average formula has the function name followed by the range in parentheses. Check that the formula begins with an equals sign, that the range covers the intended data, and that the formula was entered in a cell outside that range. For example, if sales figures are in C2:C8, use =AVERAGE(C2:C8) in a separate result cell.

AVERAGE ignores blank cells and text in a referenced range, but includes zero as a number. That distinction can change the answer. With 10, 20, and a blank cell in B2:B4, a formula using =AVERAGE(B2:B4) returns 15. If B4 contains 0 instead, the same formula returns 10 because Excel divides the total of 30 by three numeric values.

Check for an accidental range that includes labels, unrelated figures, or the result cell itself. A circular reference can occur if the formula includes its own cell; Excel may warn about it or fail to calculate as expected. Also check whether the data extend beyond the last row in the formula. Clicking the formula cell and reviewing the colored range outline can help you see which cells Excel is using.

Look for numbers stored as text

Values that look like numbers are sometimes stored as text, especially after copying data from a website, a text file, or another system. AVERAGE ignores text entries in a referenced range, so a range made entirely of text-formatted numbers can produce #DIV/0! because Excel finds no numeric values to average.

One diagnostic is to count the numeric cells. With the suspected values in B2:B10, enter =COUNT(B2:B10) in an empty cell. COUNT returns the number of cells Excel recognizes as numeric; if the result is 0 even though the cells look filled, investigate their data type. COUNT does not count text or blank cells.

To convert text-formatted numbers, select the affected cells and look for Excel’s warning icon and Convert to Number option. Another desktop approach is to select the range and choose Data > Text to Columns, then finish the wizard using the default settings when appropriate. Check the result before relying on it, especially if the data include identifiers, leading zeros, or unusual separators that should not be converted.

Changing the cell format from Text to General does not reliably convert values that were already entered as text. After changing the format, you may need to convert the data or edit and re-enter the values. If a single value needs conversion, with text that represents a number in B2, =VALUE(B2) returns its numeric equivalent when Excel can interpret the text as a number.

Confirm that Excel is calculating

A valid formula can appear not to update when the workbook is set to manual calculation. On Windows, open Formulas > Calculation Options and choose Automatic. Then recalculate the workbook with F9 or use Formulas > Calculate Now. In Excel for Mac, use Formulas > Calculation Options if available in your version; the calculation commands are also on the Formulas tab, while the function-key shortcut can depend on your Mac keyboard settings.

Manual calculation is useful in large workbooks when recalculating after every change would be slow, but it can leave displayed results out of date. If other formulas in the workbook are stale too, calculation mode is a stronger possibility than a problem with AVERAGE itself. If only one result is wrong, inspect that formula’s range and source values first.

Also check whether Excel is displaying formulas instead of their results. On Windows, Ctrl+` toggles Show Formulas; the grave accent key is usually near the top-left of the keyboard. You can also check Formulas > Show Formulas. If the worksheet shows formula text throughout, turn that view off. If just one cell displays the formula literally, it may be formatted as Text or may have a leading apostrophe. Set the cell to General, then edit and confirm the formula again.

Understand errors, filters, and hidden rows

If the result is an error rather than blank, its meaning can narrow down the cause. #DIV/0! usually means the range has no numeric values for AVERAGE to divide by. A text-only range, a range of blanks, or a range that has been emptied can lead to this result. If a source cell contains an error such as #N/A, the average may also return an error; find and resolve the source error if it should not be part of the calculation.

Filtering rows does not automatically make AVERAGE ignore the filtered-out values. A formula such as =AVERAGE(B2:B10) generally averages the values in the full range, including rows hidden by a filter. This is a common reason a calculated result differs from the visible rows.

For an average that excludes filtered-out rows, with values in B2:B10, use =SUBTOTAL(1,B2:B10). The result is the average of the visible rows after filtering, but this function number still includes rows hidden manually. To exclude both filtered-out rows and manually hidden rows, use =SUBTOTAL(101,B2:B10). These formulas are useful when the intended question is specifically about the rows currently visible, not the entire dataset.

Fix results that look missing or wrong

Sometimes Excel calculates an average but the cell makes it hard to see. If a numeric result appears as hash marks, widen the column by dragging its right boundary or double-clicking that boundary to AutoFit. Hash marks commonly mean the column is too narrow to display the formatted value, not that the formula failed.

A number format can also make a nonzero average look like zero. For example, a result such as 0.4 displayed with zero decimal places may appear as 0 because of rounding in the display. The underlying result may still be 0.4. Select the result cell and choose Home > Increase Decimal to show more places, or open the Number Format options to choose a suitable format.

If a formula returns a plausible but unexpected value, check whether zeros should count, whether blank-looking cells contain spaces or formulas returning empty text, and whether hidden rows are part of the intended calculation. AVERAGE ignores genuinely blank cells and text in referenced cells, but it includes numeric zero. A cell containing a formula that returns an empty string is not the same as a numeric zero.

Choose a conditional average when needed

Sometimes the problem is not that Excel cannot show an average, but that AVERAGE includes values that do not meet the question you are asking. AVERAGEIF calculates an average for values meeting one condition, while AVERAGEIFS handles multiple conditions. For instance, you might want the average sales for one department rather than the average across every department.

With department names in A2:A10 and sales figures in B2:B10, =AVERAGEIF(A2:A10,E2,B2:B10) averages the sales in B2:B10 for rows whose department matches the value in E2. If E2 contains Sales, the formula averages only the sales figures on rows labeled Sales. Confirm that the condition range and average range cover corresponding rows; mismatched ranges can produce an error or a misleading result.

Conditional averages do not solve text or data-quality problems in the values being averaged. If none of the rows meet the condition, or none of the matching values are numeric, the formula can return #DIV/0!. Check the spelling of the criterion and confirm that matching numeric values are stored as numbers.

Frequently Asked Questions

Why does Excel show #DIV/0! for an average?
AVERAGE returns this error when it has no numeric values to average. Check for a range containing only blanks or text, and look for numbers stored as text. COUNT can help identify whether Excel recognizes any values in the range as numeric.

Does Excel include zero in an average?
Yes. A numeric zero counts as a value, while a blank cell is ignored. If zero means missing data in your source, decide how to handle it deliberately rather than assuming AVERAGE treats it as blank.

Why is the average on the status bar missing?
Right-click the status bar and enable Average, then select cells containing numbers. The status bar is only a temporary summary; use an AVERAGE formula if you need a result saved in the worksheet.

Does AVERAGE ignore filtered rows?
No. AVERAGE generally includes values in filtered-out rows. Use SUBTOTAL with function number 1 to average visible filtered rows, or function number 101 if manually hidden rows should also be excluded.

Why does my formula appear in the cell instead of its result?
Show Formulas may be turned on, or the individual cell may be formatted as Text or begin with an apostrophe. Turn off formula display if the whole sheet is affected. For a single cell, set its format to General, then edit and confirm the formula again.

Related

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.