Excel Cell Range Average: Formula and Examples

To average a range in Excel, use AVERAGE: if A2:A5 contain 10, 20, a blank cell, and 0, =AVERAGE(A2:A5) returns 10, because (10 + 20 + 0) ÷ 3 = 10. The formula calculates the arithmetic mean of the numeric values in the referenced cells.

What an Excel cell-range average means

A cell range is a group of cells identified by its first and last cell addresses. In A2:A5, the colon means “from A2 through A5,” including both endpoints. AVERAGE adds the numeric values in the range and divides the total by the count of numeric values it includes. It does not divide by the number of cells in the range if some cells are blank or contain text.

This makes the function useful for questions such as average monthly sales, average test scores, or average response time. The result is a single number, but its usefulness depends on the data and the range you select. For example, including a total row alongside the individual transactions can make an average misleading, even though Excel correctly calculates the range you supplied.

Excel worksheets in Microsoft 365 for Windows can contain up to 1,048,576 rows and 16,384 columns, ending at column XFD. A range can be a small block or a large portion of a worksheet; for a typical average, it is usually best to reference only the cells that contain the observations you intend to compare.

The basic AVERAGE formula

With the five observations 12, 18, a blank, 0, and 30 in B2:B6, =AVERAGE(B2:B6) returns 15: Excel adds 12 + 18 + 0 + 30 to get 60, then divides by the four numeric cells. Enter a formula in an empty cell, such as B7, and press Enter. The formula cell can be outside or inside the data range, but including it in its own range would create a circular reference.

You can also select the result cell, type the formula, and select the range with the mouse before closing the parenthesis. On Windows, pressing Enter confirms the formula. Excel for Mac uses the same formula syntax; keyboard shortcuts can differ, but typing the formula directly works in both desktop versions. Excel for the web also supports AVERAGE.

The result cell’s number format controls how the average appears, not how it is calculated. If the result is a long decimal, use Home > Number to choose a suitable number format or adjust the displayed decimal places. Formatting a result to show two decimal places changes its appearance; it does not necessarily round the underlying value used in later calculations.

How blanks, zeros, text, and errors affect the result

For a referenced range, AVERAGE ignores empty cells and text entries. It does count a numeric zero. That distinction matters when an empty cell means “not recorded” but zero is a real measurement. In the opening example, the blank does not enter the calculation, while the zero does. If the zero represented missing data rather than a genuine value, the average would need a deliberate data-cleaning decision rather than a different interpretation by Excel.

A formula that displays an empty string, such as a cell whose formula returns "", is treated as text by AVERAGE and is not counted as a numeric observation. A number stored as text is also generally ignored when it is in a referenced range. If a value that looks like a number is being skipped, check whether it is stored as text and convert it to a number before relying on the average.

An error value in a referenced range, such as #N/A or #DIV/0!, normally causes AVERAGE to return an error rather than silently skip that cell. Find and resolve the source error, or use an approach designed to handle errors if excluding them is appropriate for the analysis. Do not hide errors merely to produce a plausible-looking average: an error may indicate a problem with the underlying records.

Choose the right range and avoid common mistakes

The most important step is defining which observations belong in the calculation. Check the top and bottom of the range, and look for headers, subtotals, notes, and unrelated numbers within it. If a column contains a heading in row 1 and data in rows 2 through 20, selecting the data range rather than the whole surrounding block makes the intention clearer.

Check the endpoints: A range such as C2:C20 includes both C2 and C20. Accidentally stopping at C19 omits a record.
Keep headers out: Text headers are ignored in many average calculations, but selecting only the data still reduces ambiguity and helps prevent other formula mistakes.
Look for totals: A total or subtotal is a numeric value. Including it as though it were another observation can distort the result.
Confirm the data meaning: Decide whether a zero is a real observation, and whether blank cells mean missing information.
Check for hidden records: AVERAGE includes values in hidden rows and rows hidden by a filter. If the question is specifically about visible records, use a different method.

For data that grows over time, a fixed range such as C2:C20 will not automatically expand to include a new record entered in C21. An Excel Table is often a more convenient way to manage growing lists because table references can expand as rows are added. Alternatively, update the range when the data changes. Avoid selecting an entire column without considering workbook size and intent: Excel will ignore empty cells, but broad references can make a complex workbook harder to understand and may add calculation work.

Average several ranges or only matching records

AVERAGE can accept more than one range. With 10, 20, and 30 in A2:A4 and 40, 50, and 60 in C2:C4, =AVERAGE(A2:A4,C2:C4) returns 35: the six values total 210, and 210 ÷ 6 = 35. This can be useful when observations are in separate sections. Take care not to include the same cell twice, because a repeated reference contributes its value more than once.

To average values that meet one condition, use AVERAGEIF. Suppose B2:B5 contain East, West, East, East, while C2:C5 contain 10, 20, 30, 20. With those ranges, =AVERAGEIF(B2:B5,"East",C2:C5) returns 20: the three East values total 60, and 60 ÷ 3 = 20. The first range is checked for the criterion; the average range supplies the values to average.

For multiple conditions, use AVERAGEIFS. Suppose B2:B6 contain East, East, West, East, East; C2:C6 contain 10, 20, 30, 40, 50; and D2:D6 contain 8, 12, 15, 20, 6. With that layout, =AVERAGEIFS(C2:C6,B2:B6,"East",D2:D6,">=10") returns 30. Only the second and fourth rows meet both conditions, so Excel averages 20 and 40. In AVERAGEIFS, the average range comes first, followed by pairs of criteria ranges and criteria.

Filtered rows, dates, and other kinds of data

A common surprise is that filtering a list does not make AVERAGE ignore the filtered-out rows. AVERAGE uses the values in its range whether the rows are visible or hidden. If the intended result is the average of visible records only, consider SUBTOTAL or AGGREGATE and check their options for filtered and manually hidden rows. The appropriate choice depends on whether manually hidden rows should also be excluded; verify the result against a small sample before applying it to an important report.

Excel stores dates as serial numbers, so AVERAGE can calculate an average date from a range of valid dates. The result may initially appear as a serial number; apply a date number format to display it as a calendar date. For example, an average of dates represents a midpoint in Excel’s date-number system, not necessarily a date that appears in the original records. Similar care applies to percentages and currency: Excel averages the stored numeric values, while the cell format determines whether the result displays as a percentage, date, or monetary amount.

If observations have different importance or represent different group sizes, a simple arithmetic mean may not be the right statistic. A weighted average gives some values more influence than others, often according to a weight or quantity column. For instance, averaging the average scores of two classes equally is not the same as combining every student’s score when the classes have different enrollment. Choose the calculation based on the question, not just on which cells are easiest to select.

Troubleshooting an unexpected average

If the result looks wrong, first check the formula’s range and inspect every numeric value it includes. Then check whether numbers are stored as text, whether zeros are legitimate, and whether a total row has slipped into the selection. A visible blank may also be a formula returning an empty string, and an error anywhere in the range may pass an error through to the result.

It is also worth checking the result cell’s format. A value displayed as 15% may be stored as 0.15, while a date-formatted result may display as a date instead of a decimal. Formatting can make a correct calculation look unfamiliar, but it cannot fix a wrongly selected range or inappropriate inputs. If results are unexpectedly different after a filter is applied, remember that AVERAGE still includes filtered-out records.

Frequently Asked Questions

What is the Excel formula to average a range?
Use AVERAGE with the range in parentheses. For example, if the values to average are in D2:D12, enter =AVERAGE(D2:D12) in another cell. The function returns the arithmetic mean of the numeric entries in that range.

Does Excel count blank cells when averaging?
No. AVERAGE ignores blank cells in a referenced range, so it divides by the number of numeric entries rather than by the total number of cells. A numeric zero is not blank and is included.

How do I average only visible cells in Excel?
AVERAGE includes filtered-out and hidden rows. For a visible-record calculation, use a suitable SUBTOTAL or AGGREGATE option instead, taking care to distinguish filtered rows from rows hidden manually. The option you need depends on which hidden rows should count.

Can Excel average cells that are not next to each other?
Yes. AVERAGE accepts multiple ranges separated by commas in standard English-language Excel formulas. Check that the ranges do not overlap unless you intentionally want overlapping values counted more than once.

Why is AVERAGE returning an error or an unexpected value?
Check for an error in the referenced cells, numbers stored as text, zeros that should be treated as missing, and an incorrect range endpoint. Also confirm whether hidden or filtered rows should be included and whether the result cell’s number format is changing how the answer appears.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.