To find the mean in Excel, use the AVERAGE function on the cells containing your numbers. With values in B2:B6, enter =AVERAGE(B2:B6) in an empty cell and press Enter. Excel returns the arithmetic average. You will need a workbook with the data you want to analyze and a clear idea of which cells belong in the calculation.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The AVERAGE function works in all three.
- A worksheet containing the numbers you want to average. A single column or row is easiest, but the function can also use selected cells or ranges.
- An empty cell for the result. If you plan to compare the result with the original data, choose a cell outside the data range.
You do not need to install an add-in or turn on a special setting. The examples below use a small set of test scores in cells B2:B6: 12, 15, 18, 20, and 25. Their total is 90, and there are five scores, so their mean is 18. Using a small sample first is a useful way to check that you understand which values should be included before applying the calculation to a larger sheet.
Step-by-Step
- Check the data you want to average. Find the first and last cells containing the relevant numbers. For the example, the scores are in B2:B6, with a label such as Score in B1. Do not include the label in your selection. If the numbers are spread across different columns, decide whether all of those values belong in the same average. Also note whether any zeros represent real results or placeholders for missing information; that distinction changes the answer.
- Select an empty result cell. Click a blank cell where you want the mean to appear, such as B7 beneath the five scores. Keeping the result outside the source range avoids accidentally including the result in its own calculation later. You can place the result elsewhere on the worksheet if that is more convenient, but make a note of the source range so you can check it before confirming the formula.
- Enter the AVERAGE formula. With the five scores in B2:B6, type
=AVERAGE(B2:B6)into the selected cell. The colon means Excel should use every cell from B2 through B6, including both endpoints. The function name is not case-sensitive, so typing it in lowercase also works. Start with the equals sign so Excel treats the entry as a formula rather than ordinary text. - Calculate and inspect the answer. Press Enter on Windows or Return on Mac. With the example values in B2:B6, the result is 18. Excel may display 18, 18.0, or 18.00 depending on the cell’s number format. That display choice does not change the stored answer. Check that the displayed result is in a plausible range: 18 is between the lowest score, 12, and the highest, 25.
- Use the ribbon if you prefer not to type the function. Select the empty cell below or beside the numbers. In Excel for Microsoft 365 on Windows, choose Home > Editing > AutoSum arrow > Average. Excel proposes a range, usually the adjacent run of numbers. Review the outline around the proposed cells before pressing Enter. If Excel selected the wrong range, drag over the correct cells or type the range into the formula before confirming. On Mac, the same AutoSum option is available from the Home tab, though the ribbon layout can differ slightly. Excel for the web also supports AVERAGE; using the formula directly works consistently if the ribbon placement looks different.
- Average separate cells or ranges when needed. You are not limited to one continuous column. For example, if the only values to include are in B2, B4, and B6, enter
=AVERAGE(B2,B4,B6). The layout assumed here is that those three cells contain 12, 18, and 25, respectively; the result is about 18.33. Use commas between separate references. Be deliberate about this approach: cells between the references are excluded, even if they contain numbers you meant to include. - Verify the result with the total and count. A useful independent check is to divide the sum of the values by how many numeric entries there are. With the five scores in B2:B6, enter
=SUM(B2:B6)/COUNT(B2:B6)in a separate blank cell. The sum is 90 and COUNT returns 5, so the result is 18. If this check differs from AVERAGE, review the range and look for numbers stored as text, unintentional zeros, or missing values. - Choose a readable number format. If the mean is correct but displays too many or too few decimal places, select the result cell and use Home > Number Format to choose a suitable format, such as Number. Use the increase or decrease decimal buttons in the Number group to adjust what is shown. Rounding the display does not necessarily round the underlying value; other calculations will still use the full stored result unless you explicitly change the formula.
For the basic example, the calculation is the sum of 12, 15, 18, 20, and 25 divided by five observations. That gives 18. The AVERAGE function performs those steps automatically and updates the result if you change a number in the range. If you add another score below the original list, check whether the formula’s range includes the new cell; a formula using B2:B6 will not automatically include a value entered in B7 if B7 already contains the result.
Common Mistakes
- Including a heading in the selection. AVERAGE generally ignores text in a referenced range, so a header may not cause an error, but selecting the right cells makes the formula easier to check and less likely to include unrelated values.
- Confusing blanks with zeros. Empty cells are ignored by AVERAGE, while a cell containing 0 is counted. If zero means a real score or measurement, it belongs in the mean. If it is being used to mark an unrecorded result, decide how to handle it before calculating.
- Using the wrong range. A formula can calculate successfully and still include extra rows or omit the last observation. Check the cell references in the formula bar and compare the highlighted range with the data you intended to use.
- Assuming text-formatted numbers count. A cell that looks like a number may actually contain text, especially after importing data. AVERAGE ignores text within a referenced cell range, which can make the result higher or lower than expected because that entry is left out.
- Expecting the mean to represent every data set well. A single unusually high or low value can pull the mean toward it. The mean is still calculated correctly, but it may not describe a typical observation. For skewed data or outliers, consider checking the median as well.
- Thinking filtered or hidden rows are automatically excluded. AVERAGE uses numeric values in its referenced range even when some rows are hidden or filtered out. If you need a result based only on visible rows, choose an approach designed for that situation rather than assuming AVERAGE changes its range automatically.
Tips
- Use a consistent data layout. Keep one observation per cell and put labels in a separate header row. A tidy list makes it easier to select the full range and reduces the risk of including totals or notes.
- Turn a list into an Excel table for growing data. Select the data and choose Insert > Table, confirming that the table has headers if it does. A formula that refers to a table column can expand with that column as you add rows. This can be more reliable than remembering to extend a fixed range.
- Keep an eye on units. Do not combine values measured in different units, such as minutes and seconds, or different categories that should have separate averages. The function averages numbers; it cannot tell whether the values are comparable.
- Use the same inclusion rule throughout a report. Decide whether blanks, zeros, incomplete records, or outliers should be included, then apply that decision consistently. Write a short note beside the result if someone else may need to understand what the mean represents.
- Show enough decimal places for the context. A whole-number average may be adequate for a count of people, while measurements or test results may need one or more decimal places. Format the output for readability without confusing display rounding with the exact calculated value.
- Copy a formula carefully. If you fill the formula down a column for several groups, Excel adjusts relative references as it copies. After filling, inspect a few formulas to confirm each one points to the intended group rather than the same data range or a neighboring section.
Troubleshooting
The cell shows #DIV/0!. This commonly occurs when the referenced range contains no numeric values for AVERAGE to use. Check that the range points to the intended cells, that the entries are actual numbers rather than text, and that the range is not empty. If the data was pasted or imported, try selecting a suspicious entry and checking whether it behaves like a number in Excel.
The formula appears as text instead of returning a result. Confirm that the entry starts with an equals sign and that the cell is not formatted as Text. Change the cell format to General or Number, then edit the formula and press Enter again. If formulas are displayed across the worksheet instead of results, check whether Show Formulas is turned on in the Formulas tab and switch it off.
The answer looks wrong but there is no error. Click the result cell and inspect the references in the formula bar. Then review every cell in the range for text-formatted numbers, zeros standing in for missing data, duplicate records, or values that should not be included. For the example in this guide, the five cells should sum to 90 and the mean should be 18. Comparing the formula result with that simple sum-and-count check can reveal a range mistake quickly.
Excel rejects a formula with a separator error. The examples use standard comma separators. Some regional settings use a different list separator, often a semicolon, in formulas with multiple arguments. Follow the separator Excel expects in your installation. The single-range example uses only one argument, so it does not require a list separator between arguments.
The displayed result has unexpected decimals. This is usually a number-format issue rather than a calculation problem. Select the result cell and adjust the decimal places from the Home tab. If you need to round the actual result for a specific purpose, decide how many decimal places are appropriate first; changing only the cell format leaves the underlying value unchanged.
Frequently Asked Questions
Is mean the same as average in Excel?
Usually, yes. Mean commonly refers to the arithmetic average: add the included numbers and divide by their count. In Excel, AVERAGE calculates that value.
Does AVERAGE include blank cells and zeros?
It ignores genuinely blank cells in a referenced range, but it includes cells containing zero. A zero is a numeric observation, so remove or replace it only if it does not represent a real value for your analysis.
How do I find the mean if my numbers are not next to each other?
List the individual cell references, separated by commas, inside AVERAGE. Check each reference carefully because any numeric cells you leave out will not affect the result. If your Excel region uses semicolons as argument separators, use the separator Excel expects.
Why does my mean change when I add a new number?
If the new number is inside the formula’s referenced range, AVERAGE recalculates automatically. If you typed it just outside a fixed range, edit the formula to include it or use an Excel table so the data range can expand with new rows.
Can I calculate a mean while ignoring zeros?
AVERAGE does not ignore zeros: it counts them as values. First confirm that zero represents missing or invalid data rather than a genuine measurement. If zeros should be excluded, use a conditional-average method appropriate to your data and verify that its criteria match your rule.
Should I use mean or median?
Use the mean when you want the total of the observations divided evenly across them and extreme values are not distorting the summary. The median is the middle value after sorting and is often more representative when the data has strong outliers or a skewed distribution. Looking at both can help explain the shape of a data set.
Related
- Export Tab Names in Excel: Two Practical Ways to Save Them
- How to Export an Excel Table to Word Without Losing Format
- MT5 History to Excel: Export Trades and Account Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Copy an Image in Excel: Clear Steps and TipsTo copy an image in Excel for Microsoft 365 on Windows, click the image’s edge to select it,…
- Excel Shortcut to Go to the Bottom of a Data ColumnIn Excel for Microsoft 365 on Windows, press Ctrl+Down Arrow to jump to the bottom edge of the…
- Microsoft Excel: How to Remove Duplicate Rows SafelyIn Microsoft Excel for Microsoft 365 on Windows, select your data, then choose Data > Remove Duplicates, select…
- Detect Duplicates in Google Sheets: A Practical GuideTo detect duplicates in Google Sheets, select the cells you want to check, choose Format > Conditional formatting,…
- DAX Measure Switch Based on a Column Value in Power BIYes. In a DAX measure, use SWITCH to return a different measure for each value, and use SELECTEDVALUE…
- Excel Attendance File to Print: Set Up and Print ItTo print an Excel attendance file clearly, open it in Excel for Microsoft 365 on Windows, select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.