The AVERAGE function does the math for you

Excel's AVERAGE function adds up a group of numbers and divides by how many numbers there are — that's all an average is. Instead of doing this by hand, you type a formula that tells Excel which cells to use, and it gives you the result in seconds.

The basic formula looks like this: =AVERAGE(A1:A10). That tells Excel to average the numbers in cells A1 through A10. You can change the cell references to match wherever your numbers actually are.

Key Takeaways

  • The AVERAGE function adds numbers and divides by the count in one formula: =AVERAGE(A1:A10) averages cells A1 through A10.
  • You can average a range (A1:A10), separate cells (A1,A3,A5), or mix both in the same formula.
  • AVERAGE ignores empty cells and text, so it only counts the actual numbers present.
  • If you need to exclude certain numbers — like zeros or negative values — use AVERAGEIF instead to set a condition.

How to type the AVERAGE formula

Click the cell where you want the average to appear. Type an equals sign to start a formula, then type AVERAGE, then open parentheses, then the range of cells you want to average, then close parentheses. Press Enter.

For example, if your sales numbers are in cells B2 through B15, click an empty cell below them and type =AVERAGE(B2:B15), then press Enter. Excel calculates the average and shows it in that cell.

The colon between two cell references (like A1:A10) means "from A1 to A10, including everything in between". If your numbers are not in a continuous block, you can list them separately with commas instead: =AVERAGE(A1,A3,A7) averages only those three cells.

Averaging non-consecutive cells and mixed ranges

When your numbers are scattered across the spreadsheet, use commas to separate them. Type =AVERAGE(A2,A5,C3,D1) to average just those four cells, skipping everything else.

You can also mix ranges and individual cells in one formula: =AVERAGE(A1:A5,C2,D3:D8) averages cells A1 through A5, plus C2, plus D3 through D8. Excel treats this as one calculation.

What AVERAGE ignores and why that matters

The AVERAGE function counts only cells that contain numbers. It skips empty cells, text, and cells with errors. This is usually what you want — if someone didn't fill in a sales number for Tuesday, you don't want that to count as zero.

But this behavior can hide problems. If you think you're averaging 12 months of data but three cells are empty, AVERAGE divides by 9 instead of 12. Check your data first to make sure you're not accidentally excluding numbers you meant to include.

Using AVERAGEIF when you need conditions

Sometimes you want to average only numbers that meet a certain rule — for example, only sales above $100, or only entries from a specific region. The AVERAGEIF function lets you set that condition.

The formula structure is =AVERAGEIF(range, criteria, average_range). The first part is the range you're checking, the second part is the condition, and the third part is the range to average. For example, =AVERAGEIF(B2:B20,">100",C2:C20) averages the numbers in C2:C20, but only for rows where the number in column B is greater than 100.

If you're checking the same column you're averaging, you can leave out the third part: =AVERAGEIF(A1:A10,">50") averages only the numbers in A1:A10 that are greater than 50.

Fixing common mistakes

The most common error is forgetting the equals sign at the start. Excel won't recognize it as a formula without it — it will just show the text you typed. Always start with =.

Another mistake is using the wrong cell references. If you copy a formula to a different cell, the references change automatically (A1 becomes A2, A3, and so on). This is usually helpful, but sometimes you want a reference to stay the same. To lock a reference, put a dollar sign before the column and row: =AVERAGE($A$1:$A$10) will not change if you copy it.

If your result shows as an error like #DIV/0! or #VALUE!, check that all your cells contain actual numbers, not text that looks like numbers. Numbers are right-aligned in cells; text is left-aligned. If they're left-aligned, Excel sees them as text and cannot average them.

When to use other functions instead

AVERAGE gives equal weight to every number. If you want to weight some numbers more heavily than others — for example, if test scores should count more than homework — use SUMPRODUCT or AVERAGEWEIGHTED instead.

If you want the middle number in a list (the median) rather than the average, use the MEDIAN function. If you want the number that appears most often, use MODE. These are different calculations that sometimes give very different results, especially if you have one very large or very small number pulling the average in one direction.

Frequently Asked Questions

Can I average cells from different sheets?

Yes. Use the sheet name followed by an exclamation point: =AVERAGE(Sheet2!A1:A10) averages cells A1 through A10 on Sheet2. If the sheet name has a space, put it in single quotes: =AVERAGE('Sales Data'!A1:A10).

What if I want to average only cells with numbers, not blanks or text?

AVERAGE already does this — it ignores blanks and text automatically. If you want to count how many cells it actually used, wrap it with COUNTA: =AVERAGE(A1:A10) divided by =COUNTA(A1:A10) shows you the average and the count.

Can I average cells based on a date or text condition?

Yes, use AVERAGEIF. For dates: =AVERAGEIF(A1:A10,">="&DATE(2024,1,1),B1:B10) averages column B only for rows where column A is January 1, 2024 or later. For text: =AVERAGEIF(A1:A10,"North",B1:B10) averages column B only where column A says "North".

Why is my average different from what I calculated by hand?

Check that you're averaging the right cells — it's easy to include or exclude one by mistake. Also check that cells you think are numbers are not actually text. Click a cell and look at the formula bar at the top; if it shows an apostrophe before the number, it's text, not a number, and AVERAGE will skip it.