How to Calculate Mean in Excel: Step-by-Step

This guide shows you how to find the arithmetic mean (average) of a set of numbers in Microsoft Excel using the AVERAGE function, plus manual alternatives. You’ll learn the exact steps, common errors, and how to fix them when results look wrong. No prior Excel experience is needed beyond opening a workbook.

What You’ll Need

• A computer with Microsoft Excel installed (2016, 2019, 2021, or Microsoft 365 all work; the steps are nearly identical on Mac and Windows).
• A workbook with the numbers you want to average, either typed in or pasted into a column or row.
• Basic familiarity with clicking cells and typing in the formula bar (top of the sheet).

Step-by-Step

1. Open your Excel file and locate the worksheet containing the data. If you’re starting fresh, click an empty cell and type your numbers down a single column (for example, A1 through A10) or across a row (A1 through J1).
2. Choose an empty cell for the result — usually below or to the right of your data. Click that cell so it’s selected. This is where the mean will appear.
3. Type the formula =AVERAGE( in the selected cell. Do not press Enter yet. Excel will show a tooltip listing the function’s arguments.
4. Select the range of numbers. Click the first number in your data, hold the mouse button, drag to the last number, then release. Excel inserts the range reference (like A1:A10) into the formula automatically. Alternatively, type the range manually, e.g., =AVERAGE(A1:A10).
5. Close the parenthesis by typing ) so the formula reads =AVERAGE(A1:A10).
6. Press Enter. Excel calculates and displays the mean in the selected cell. For example, if your numbers are 10, 20, 30, the result shows 20.
7. Check the result by clicking the cell — the formula appears in the formula bar above the grid. If you see a #DIV/0! error, your range contains no numeric values (see Troubleshooting below).
8. Optional: Use AutoSum for speed. Click the empty cell where you want the mean, go to the Home tab, find the Σ AutoSum button (it looks like a Greek sigma), click the small arrow next to it, and choose Average. Excel guesses the range; adjust it if needed, then press Enter.
9. Optional: Average non-adjacent cells. If your numbers are scattered, type each cell reference separated by commas, e.g., =AVERAGE(A1, C3, E5). You can also hold Ctrl (Cmd on Mac) while clicking each cell, and Excel fills them in.
10. Optional: Ignore zeros. If you want the mean of only positive values, use =AVERAGEIF(A1:A10, ">0"). This calculates the average of cells greater than zero, skipping blanks and negative numbers.

Common Mistakes

• Including text or blank cells in the range — AVERAGE ignores text and empty cells, but if you accidentally include a cell with an error like #N/A, the whole result becomes #N/A.
• Typing numbers as text — If a cell has an apostrophe before a number (like '10) or is formatted as Text, AVERAGE skips it. Check the cell’s number format under Home → Number.
• Selecting the wrong range — Dragging one cell too far or missing the last row changes the result. Double-check the range in the formula bar.
• Using AVERAGEA instead of AVERAGE — AVERAGEA counts text and FALSE as 0, which lowers the mean. Use plain AVERAGE for numeric data.
• Forgetting the closing parenthesis — Excel usually auto-corrects, but if you press Enter without it, you may get a syntax error.
• Dividing manually instead of using the function — Some users type =SUM(A1:A10)/10 and hard-code the count. If you add more numbers later, the denominator stays wrong. AVERAGE updates automatically.

Tips

• Use a dynamic range — Instead of =AVERAGE(A1:A10), try =AVERAGE(A:A) to average the entire column. This automatically includes new numbers you add, but beware it also averages any stray numbers elsewhere in that column.
• Format the result — Right-click the result cell, choose Format Cells, and pick Number with 2 decimal places if you want fewer digits. The mean itself is unaffected by formatting.
• Name your range — Select the numbers, type a name like “Scores� in the Name Box (left of the formula bar), then use =AVERAGE(Scores). This makes formulas readable and less error-prone.
• Combine with other functions — Use =ROUND(AVERAGE(A1:A10), 2) to round the mean to two decimals in one step.
• Check for hidden rows — If you filtered data, AVERAGE only sees visible rows. To average all rows regardless of filtering, use =SUBTOTAL(1, A1:A10) instead.

Troubleshooting

• Result shows #DIV/0! — The range has no numeric values. Select a different range or check that cells contain numbers, not text. If you want to treat text as zero, use =AVERAGEA(A1:A10).
• Result shows #VALUE! — The formula has a syntax error, usually a missing comma or parenthesis. Click the cell, look at the formula bar, and fix the range references.
• Result shows #NAME? — You typed the function name wrong, e.g., =AVERGE. Re-type it as =AVERAGE.
• Result is a date or time — The cell is formatted as Date. Right-click the cell, choose Format Cells, and select Number or General.
• Result seems too high or low — Check for outliers or accidentally included totals. For example, if row 11 contains a sum, your range A1:A11 includes it. Narrow the range to A1:A10.
• Excel won’t let you type the formula — The cell may be protected. Go to Review → Unprotect Sheet (if you know the password) or ask the workbook owner.

Frequently Asked Questions

Q: Does AVERAGE include blank cells?
No. Blank cells are ignored entirely. Only numeric values are summed and counted.

Q: What’s the difference between AVERAGE and MEDIAN?
AVERAGE sums all values and divides by the count. MEDIAN returns the middle value when sorted. For skewed data (like incomes), median is often more representative.

Q: How do I average only the top 10 numbers?
Use =AVERAGE(LARGE(A1:A100, ROW(1:10))) as an array formula (press Ctrl+Shift+Enter on older Excel). Or sort descending and average the first 10 cells manually.

Q: Can I average cells from different worksheets?
Yes. Use a 3D reference like =AVERAGE(Sheet1:Sheet3!A1:A10) to average the same range across multiple sheets. Ensure the sheets are adjacent in the tab bar.

Q: Why does my mean change when I filter data?
By default, AVERAGE ignores hidden rows. If you filter, the formula recalculates using only visible cells. To keep the original mean, use =SUBTOTAL(1, range) or unfilter the data.

Q: How do I calculate a weighted mean?
Use =SUMPRODUCT(values, weights)/SUM(weights). For example, if values are in A1:A5 and weights in B1:B5, type =SUMPRODUCT(A1:A5, B1:B5)/SUM(B1:B5).

Related

• Zune vs iPod: Key Differences Explained
• Fix Encrypt iPhone Backup Option Greyed Out
• Check If Windows Firewall Is Blocking an App