To get a quick average in Excel, select an empty cell and enter =AVERAGE(B2:B5), replacing B2:B5 with the cells you want to average; press Enter to see the result. You can also select the numbers and read Average on the status bar, or use AutoSum to insert an average formula. These methods work in Excel for Microsoft 365 on Windows and are useful for finding the mean of a list without calculating it by hand.
What You’ll Need
- An Excel workbook with the numbers you want to average. The values can be in a column, a row, or a selected group of cells.
- An empty cell for a formula result, unless you only want to view the average temporarily on the status bar.
- A clear idea of which values belong in the calculation. For example, decide whether a zero represents a real result that should be included or a placeholder that should be left out.
You do not need an add-in or a special template. Excel’s built-in AVERAGE function is available in Microsoft 365, and it is also supported in older Excel versions. The instructions below use the Windows desktop app; the basic formula works in Excel for Mac and Excel for the web as well.
Step-by-Step
- Check the values you want to include. Find the cells containing the measurements, scores, prices, or other numbers. Look for headings, totals, notes, and blank rows so you do not accidentally include them. AVERAGE can work with a continuous block of cells, such as one column of scores, or with separate ranges when your data is not together. Confirm that the numbers are actually stored as numeric values; a number entered as text may not behave like a number in calculations.
- Choose where the answer should appear. Click a blank cell outside the data. For a column of values, a cell just below the last value is often convenient, provided it is not part of a table or another calculation. You can also put the result in a summary area and add a label such as Average. Keeping the result separate from the source values makes it easier to check the data and prevents you from replacing an input by mistake.
- Enter the AVERAGE formula. With scores in B2:B5, where the values are 12, 15, 18, and 21, click the result cell and type
=AVERAGE(B2:B5), then press Enter. The arithmetic is (12 + 15 + 18 + 21) ÷ 4, so the result is 16.5. The colon means Excel should use every cell from B2 through B5. If your data is in another location, substitute your own first and last cell references, keeping the parentheses balanced. - Use the mouse to select the range, if easier. Click the blank result cell and type
=AVERAGE(, then drag over the cells you want to include. Excel inserts the selected range reference into the formula. Type the closing parenthesis and press Enter. This is handy when the range is long or you are unsure of the exact cell addresses. Before accepting the result, check that the highlighted selection starts and ends on the intended cells. - Use AutoSum to insert the function. Click the empty cell where you want the average. On the Home tab, in the Editing group, open the AutoSum dropdown and choose Average. Excel proposes a range of nearby cells. Check the outlined range or the formula in the cell; if it is right, press Enter. If the selection is wrong, select the correct cells before pressing Enter, or edit the cell reference in the formula bar. AutoSum is a quick way to create the same kind of AVERAGE formula without typing the function name.
- Read an average without adding a formula. Select the numeric cells you want to inspect. Look at the status bar along the bottom of the Excel window for Average. This shows a temporary summary of the current selection and does not put a result into a worksheet cell. If Average is not visible, right-click the status bar and enable Average in its list of summary options. The selected range still matters: check it before relying on the displayed value.
- Format and label the answer if needed. A result such as 16.5 may be suitable as-is, but you can display fewer or more decimal places using the number-format controls on the Home tab. Formatting changes how a value looks, not the underlying average. Add a nearby label if other people will use the workbook, and consider noting the range or date period so the result has context.
- Check that the result makes sense. For a simple list of positive values, the average should usually fall between the smallest and largest included values. Compare the result with the data and look for an unexpected zero, missing number, or text entry. If your values are not all equally relevant, an ordinary average may not answer the question you intend to ask; for example, averaging individual test scores is different from weighting scores by the number of questions in each test.
When you enter a formula in a worksheet cell, it updates if the included values change. The status-bar average is different: it reflects the cells selected at that moment and is not saved as a result in a cell. Use the formula method when you want a result you can refer to in another calculation, chart, or report.
Common Mistakes
- Selecting the wrong range: A formula that starts one row too low or ends one row too early can return a plausible but incorrect answer. Check both endpoints in the formula or selection outline. If a heading sits above the numbers, begin with the first numeric cell rather than the heading.
- Assuming blank cells and zeros are the same: AVERAGE ignores empty cells in a referenced range, but includes cells containing 0. If a zero means a genuine score or measurement, including it is usually appropriate. If zero is being used to mean not recorded, decide how to handle that missing information rather than treating it as an ordinary value by default.
- Including a total in the range: Adding a sum or subtotal to the same range as the individual values can inflate the average. Keep summary rows outside the input range, or adjust the formula so it covers only the observations you want to average.
- Expecting text to count as a number: A cell that looks like 15 may be stored as text, especially if it was imported or preceded by an apostrophe. Referenced text cells are not treated as numeric observations by AVERAGE. Check cell alignment and number formatting, or test a cell by editing it and confirming that its contents are a real number.
- Confusing average with median: The average is the sum divided by the number of numeric values. A very high or low outlier can pull it away from what feels typical. If the question is about the middle value rather than the arithmetic mean, consider whether a median is a better measure for your purpose.
- Overwriting source data: Typing a formula into a cell that already contains an input replaces that value. Put the answer in an empty cell and, when working in a shared or important workbook, save a copy before making structural changes.
Tips
- Use a whole-column reference carefully. If the values are in column B and the header is in B1, a formula can refer to B:B; text headings are ignored by AVERAGE. However, a whole-column reference can make the intended data less obvious and may include unrelated numbers elsewhere in that column. A specific range is easier to audit when the data has a known start and end.
- Average separate groups when appropriate. You do not have to move non-adjacent numbers into one block. For example, if the two intended groups are B2:B4 and D2:D4, use
=AVERAGE(B2:B4,D2:D4). With values 10, 20, 30 in the first range and 40, 50, 60 in the second, the result is 35. State clearly which groups belong in the calculation; the formula averages the six numbers together, not the two group averages equally. - Use a structured table for changing lists. If your records are formatted as an Excel table, a table-column reference can make formulas easier to read and can include new rows as the table grows. For example, if a table is named Sales and its numeric column is Amount, a formula in a cell outside the table can use
=AVERAGE(Sales[Amount]). The result is the mean of the numeric entries in that column. Confirm the actual table and column names in your workbook before using this form. - Keep the source numbers intact. Put the average in a separate summary cell rather than replacing a value in the list. That makes the formula easier to verify and means later edits to the observations can update the result automatically.
- Choose decimal places for readability. A mean can contain more decimal places than the source values. Use the number-format controls to show a sensible level of precision, but remember that the displayed rounding does not change the value used in later calculations. Avoid rounding the formula result itself unless your task specifically requires it.
- Use the status bar for a one-off check. When you only need to inspect a range and do not need to keep the answer, the status-bar Average can be faster than creating a formula. For reports or calculations that need to remain visible, use a worksheet formula instead.
Troubleshooting
The formula displays an error or does not calculate. Check that the function is spelled AVERAGE, that the parentheses are paired, and that the cell references use the correct row and column. If the formula appears as text in the cell instead of returning a value, the cell may be formatted as Text, or the entry may have a leading apostrophe. Change the cell format to General or Number, then edit and re-enter the formula. Also check that Show Formulas has not been turned on; when formulas are being displayed, use the Formulas tab and select Show Formulas to toggle that view.
The result is lower or higher than expected. Inspect every cell in the range, especially zeros, outliers, hidden-looking entries, and values near the range edges. Hidden rows are generally still included in a normal AVERAGE range, so hiding a row is not the same as excluding its value. If you intend to omit particular observations, remove them from the selected range or use a method designed for that rule rather than assuming hidden cells are ignored.
The status bar does not show Average. First make sure the selection contains numeric cells. Then right-click the status bar and select Average from the available summary options. If the workbook is open in Excel for the web or the interface differs, use a cell formula instead; the status bar controls can vary by platform and version.
Excel rejects separators or displays a formula warning. The examples use commas between separate arguments, as in the standard Microsoft 365 Windows setup. Some regional settings use different formula separators. If you are entering separate ranges and Excel flags the comma, use the separator expected by your Excel regional settings. A simple one-range formula does not need an argument separator.
The average ignores a value that looks numeric. The entry may be stored as text or may contain extra characters such as spaces or a currency symbol imported as text. Convert the source entries to numbers using Excel’s available conversion options, or re-enter them as numeric values, then check the result again. Do not convert a value blindly if the extra characters indicate a unit or other information that needs to be preserved.
Frequently Asked Questions
What is the quickest way to average cells in Excel?
Select an empty cell, enter an AVERAGE formula for the range, and press Enter. If you only need to check the result once, select the numeric cells and read Average on the status bar instead.
Does Excel include zero in an average?
Yes. A zero in a referenced cell is a numeric value and counts in both the total and the number of values. A genuinely empty cell is ignored. Decide whether a zero is a real observation before using the result.
Does AVERAGE include blank cells?
No. Empty cells within a referenced range do not count as zero and are not included in the count. Cells containing zero do count, so a list with blanks can produce a different average from a list where those blanks are filled with zeros.
Can I average numbers that are not next to each other?
Yes. Include each range as a separate argument in the AVERAGE function, using the separator accepted by your Excel settings. Check that the groups you include are the observations you actually intend to combine.
Why is the average not the same as the middle value?
The average, or mean, adds the numeric values and divides by their count. The middle value after sorting is the median. They can differ, particularly when the data includes unusually large or small values.
Will the average update when I change a number?
A formula in a worksheet cell recalculates when a referenced value changes, as long as Excel calculation is enabled. A status-bar average is only a summary of the current selection; it is not a stored formula result.
Related
- How to Delete an Excel File from OneDrive Safely
- How to Delete an Excel File from SharePoint
- How to Delete an Excel Workbook Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Gridlines: How to Make Them Print on Every PageTo make worksheet gridlines print in Excel for Microsoft 365 on Windows, select the worksheet, open Page Layout,…
- How to Set an Out of Office Reply in OutlookTo set an out-of-office reply in Outlook, open Automatic Replies, turn them on, write your message, and—if available—choose…
- How to Double Space in WordTo double-space text in Microsoft Word, select the text, then choose Home > Line and Paragraph Spacing >…
- Create Labels in Word With Mail Merge: Step-by-StepTo create a sheet of address labels in Word, open the Mailings tab, choose Start Mail Merge >…
- Calculated Field in a PivotTable: How It WorksA calculated field in an Excel PivotTable is a custom calculation made from fields in the source data,…
- Automatically Merge Duplicate Cells in Excel? What WorksExcel has no built-in command that automatically merges cells just because their values match. For repeated values in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.