The AVERAGE function is the fastest way to find a mean

Excel's AVERAGE function adds up a range of numbers and divides by how many numbers are in that range. Type =AVERAGE(A1:A10) into any cell, and Excel returns the mean of the values in cells A1 through A10. This works for any contiguous range — rows, columns, or a rectangular block of cells.

The function ignores empty cells and text. If you have 10 cells selected but 2 are blank, AVERAGE divides the sum by 8, not 10. This is usually what you want. If a cell contains zero, AVERAGE counts it as a number and includes it in the division.

You can also type multiple separate ranges: =AVERAGE(A1:A5, C1:C5) averages all 10 cells together, even though they are not next to each other. Excel treats this as one calculation.

Key Takeaways

  • Type =AVERAGE(A1:A10) into any cell to find the mean of that range; Excel adds the numbers and divides by the count.
  • The AVERAGE function skips empty cells and text but counts zeros, so the divisor changes based on what is actually in the range.
  • Use a colon (A1:A10) for a continuous range or commas (A1:A5, C1:C5) to average multiple separate blocks at once.
  • Copy the formula down a column to calculate a running average for each row, or use it in a single cell to find one overall mean.

Entering the formula in a cell

Click the cell where you want the average to appear. Type the equals sign first — this tells Excel you are entering a formula, not text. Then type AVERAGE, an opening parenthesis, the range, and a closing parenthesis.

For example, if your numbers are in column B from row 2 to row 20, type =AVERAGE(B2:B20). Press Enter. Excel calculates and displays the result in that cell. The formula bar at the top shows what you typed; the cell shows only the number.

You can also click and drag to select the range instead of typing it. Click the cell, type =AVERAGE(, then click the first cell of your range, hold Shift, and click the last cell. Type the closing parenthesis and press Enter. Excel fills in the cell references for you.

Copying the formula to other cells

Once you have entered the formula in one cell, you can copy it down or across to calculate averages for other ranges. Click the cell with the formula. Copy it (Ctrl+C on Windows, Cmd+C on Mac). Select the cells where you want the formula to go and paste (Ctrl+V or Cmd+V).

Excel automatically adjusts the cell references. If you copy =AVERAGE(B2:B20) to the cell to the right, it becomes =AVERAGE(C2:C20). If you copy it down one row, it becomes =AVERAGE(B3:B21). This is called a relative reference and is usually what you want.

If you want the range to stay the same when you copy, use dollar signs: =AVERAGE($B$2:$B$20). This is called an absolute reference. When you copy this formula, the range stays B2:B20 no matter where you paste it.

Averaging only cells that meet a condition

The AVERAGEIF function finds the mean of only the cells that match a rule you set. For example, =AVERAGEIF(A1:A10, ">50") averages only the numbers in A1:A10 that are greater than 50. The syntax is the function name, the range to check, and the condition in quotes.

You can also average cells in one column based on a condition in another. Type =AVERAGEIF(A1:A10, "yes", B1:B10). This checks column A for the word "yes" and averages the corresponding values in column B. The first range is what Excel checks; the second range is what it averages.

Common conditions include ">50" (greater than 50), "<100" (less than 100), ">=25" (greater than or equal to 25), and "apple" (exact match). Text conditions do not need the greater-than or less-than symbols — just the word in quotes.

Handling errors and blank cells

If your range includes a cell with an error (like #DIV/0! or #VALUE!), the AVERAGE function returns an error instead of a number. To ignore error cells, use AVERAGEIF with a condition that excludes them, or use AGGREGATE, which has a built-in option to skip errors.

Type =AGGREGATE(1, 6, B2:B20). The 1 tells Excel to calculate the average; the 6 tells it to ignore error values. This function is more complex but handles messy data better than AVERAGE alone.

Blank cells are ignored by AVERAGE, so they do not affect the result. If you have a range with some empty cells, Excel counts only the cells with numbers. This is different from cells that contain zero — those are counted as numbers.

Using AVERAGE with named ranges

If you use the same range often, give it a name so you do not have to type the cell references every time. Select the range, then go to the Name Box (the field to the left of the formula bar that shows the cell reference). Type a name like sales_data or temperatures and press Enter.

Now you can type =AVERAGE(sales_data) instead of =AVERAGE(A1:A50). Named ranges make formulas easier to read and update. If the data moves to a different location, you change the name definition once, and all formulas using that name update automatically.

To see all named ranges in your workbook, go to the Formulas tab (or Sheet menu on Mac) and click Define Name or Manage Names. You can edit or delete names from there.

Frequently Asked Questions

Does AVERAGE count zero as a number?

Yes. If your range is A1:A5 and contains 10, 20, 0, 30, and 40, AVERAGE returns 20 (the sum 100 divided by 5). The zero is counted. Empty cells are not counted — if one of those cells were blank instead of zero, the average would be 25 (100 divided by 4).

Can I average cells from different sheets?

Yes. Type =AVERAGE(Sheet1!A1:A10, Sheet2!A1:A10) to average ranges from two different sheets. Use the sheet name, an exclamation point, and the cell range. If the sheet name has a space, put it in single quotes: ='Sales Data'!A1:A10.

What is the difference between AVERAGE and MEDIAN?

AVERAGE adds all numbers and divides by the count. MEDIAN finds the middle number when all values are sorted. If you have 1, 2, 100, the average is 34.3 but the median is 2. Use MEDIAN when one very large or very small number would skew the result.

How do I average only the visible cells in a filtered list?

Use SUBTOTAL(1, A1:A10) instead of AVERAGE. The 1 tells Excel to calculate the average of only the visible (unfiltered) cells. When you filter the list, SUBTOTAL updates automatically to show only the average of what you can see.

Can I average text values?

No. AVERAGE works only with numbers. If your range contains text, those cells are ignored. If every cell is text, AVERAGE returns 0 or an error. To count how many text entries you have, use the COUNTA function instead.