If your numbers are in B2:B10, select an empty cell and enter =AVERAGE(B2:B10), then press Enter. Excel returns the arithmetic mean: the sum of the numeric values divided by how many numeric values there are. Before starting, check that the column contains the figures you intend to average and that the result cell is outside the range being averaged.
What You’ll Need
- An Excel workbook with the values you want to average in one column. The steps below are for Microsoft Excel for Microsoft 365 on Windows; the same AVERAGE formula works in Excel for Mac and Excel for the web.
- A blank cell for the result. It can be above or below the data, or elsewhere on the worksheet, as long as it is not part of the range being averaged.
- A clear idea of what counts as a value. Decide whether zeroes are meaningful, and whether any text, blank cells, or error messages in the column need attention.
Step-by-Step
- Find the values you want to include. Look at the column and identify the first and last cells containing the intended numbers. For example, if monthly sales figures run from B2 through B10, the range is B2:B10. Do not include a heading such as “Sales” or a total cell unless you specifically want it included. Including a total alongside the original figures will affect the result.
- Choose a result cell. Click a blank cell where the average should appear, such as B11 when your data is in B2:B10. Keeping the answer directly beneath the data makes it easy to review, but you can use a cell on another part of the sheet. Make sure that cell is not inside the range you plan to average; otherwise, the formula may include itself and create a circular reference.
- Enter the AVERAGE formula. With the values in B2:B10 and the result cell outside that range, type
=AVERAGE(B2:B10). The equals sign tells Excel to calculate a formula, AVERAGE is the function name, and the parentheses contain the cells to use. You can type the formula directly or begin typing=AVERAGE(and drag over the cells to select the range. Check that the highlighted selection starts and ends on the right rows. - Press Enter and read the result. With the example values 12, 15, 18, 20, and 25 in B2:B6, the formula
=AVERAGE(B2:B6)returns 18. The arithmetic is (12 + 15 + 18 + 20 + 25) ÷ 5 = 90 ÷ 5 = 18. Excel may display decimal places depending on the cell’s number format, but that does not change the underlying calculated average. - Use the ribbon option if you prefer not to type the function. Click the blank result cell, then choose Home > Editing > AutoSum > Average. Excel proposes a range and displays a formula in the cell. Inspect that proposed range before accepting it: if it has selected a heading, omitted values, or included an existing total, edit the range. Press Enter to confirm. AutoSum may be shown as a compact button or dropdown depending on the ribbon layout.
- Format the answer for readability, if needed. Select the result cell and use the number-format controls on the Home tab to show an appropriate number of decimal places. For a whole-number display, reduce decimal places; for measurements or rates, retain enough decimals to be useful. Formatting changes what is shown, not the calculated average. If you need the result rounded for later calculations, use an appropriate rounding formula rather than relying on display formatting alone.
- Check the range and the data. Compare the displayed result with a quick reasonableness check. The average should generally fall between the smallest and largest included values. If it does not, inspect the selected cells, check for values stored as text or unexpected zeroes, and confirm there are no error values in the range. If the source data changes, Excel normally recalculates the formula automatically.
For a non-adjacent set of cells, AVERAGE can take multiple ranges or individual cell references separated by commas. With values in B2:B5 and D2:D5, for example, use =AVERAGE(B2:B5,D2:D5). This includes only the two specified groups; it does not include column C. The arithmetic mean is appropriate when each included observation should count equally. If the data calls for different weights, such as grades where assignments have different percentages, a simple average may not represent the intended result.
Common Mistakes
- Selecting the wrong range: A formula can be syntactically correct and still calculate the wrong cells. Check both endpoints in the formula bar. A range ending at B9 will leave out B10, while a range extending to B11 may include a total or the result cell.
- Including a total with the underlying values: If a column contains several monthly amounts followed by a total, averaging the entire block counts that total as if it were another month. Select only the individual observations. This is one reason to keep totals and averages clearly labeled and outside the source range.
- Assuming blanks and zeroes mean the same thing: AVERAGE ignores genuinely empty cells and text in referenced cells, but it includes numeric zeroes. A zero may be a valid result, such as no sales on a particular day. Removing it changes the question the average answers.
- Expecting a visible rounded value to be the exact value: A cell formatted to show two decimal places can display 18.33 while storing additional decimal digits. If another formula uses that cell, Excel generally uses the stored value, not merely the shortened display.
- Entering numbers as text: Values imported from a file or typed with a leading apostrophe may look numeric but be stored as text. AVERAGE typically ignores text entries in a referenced range, which can make the result seem too high or leave no numeric values to average.
- Including a cell with an error: An error such as #N/A or #DIV/0! in the referenced range can cause the average formula to return an error. Find and resolve the source error if the intended calculation requires that value.
Tips
- Use a table for data that grows regularly. If you convert your list to an Excel table with Insert > Table, new rows can be included in table-based calculations as the data expands. AVERAGE can use a table column reference; for a table named SalesData with a column named Amount, the formula is
=AVERAGE(SalesData[Amount]). This assumes the table and column use those exact names. It averages the numeric entries in that table column, so check the table’s header and make sure it contains only the data you want. - Average only nonzero entries when zero means “not recorded.” With the candidate figures in B2:B10 and zeroes representing missing readings rather than real measurements, use
=AVERAGEIF(B2:B10,"<>0",B2:B10). The result excludes zero values from the average. Do not use this approach when zero is a genuine observation, because excluding real zeroes will raise or otherwise change the average. Empty cells are not treated as numeric observations by AVERAGE in the first place. - Keep a label beside the result. Put a label such as “Average score” in the neighboring cell so another reader can tell what the number represents. Include the period, unit, or group where useful, such as “Average response time (seconds).” A value without context is easy to misread when a worksheet contains several calculations.
- Use filters carefully. AVERAGE includes values in rows hidden by a filter. If you specifically need the average of visible filtered rows, use SUBTOTAL instead. With the figures in B2:B10,
=SUBTOTAL(1,B2:B10)averages values in visible rows while ignoring rows excluded by a filter; function number 1 does not exclude rows hidden manually. Use=SUBTOTAL(101,B2:B10)if manually hidden rows should also be excluded. These formulas assume the same B2:B10 data range, and the calculated result changes as the visible rows change. - Consider whether a mean is the right summary. A few unusually large or small values can pull the arithmetic average away from what is typical. For example, a single very high transaction can make average transaction size look larger than most transactions. If your goal is the middle observation rather than the arithmetic mean, consider the median instead, and explain which measure you are reporting.
Troubleshooting
- The result is #DIV/0!: Excel has no numeric values to average in the selected range. Check that the range is correct and that at least one entry is a real number, not text that only looks like a number. If the data is meant to arrive later, you can leave the formula in place and show a blank until numbers are available. With data in B2:B10, use
=IF(COUNT(B2:B10)=0,"",AVERAGE(B2:B10)). This displays an empty string when there are no numeric values and otherwise returns the average. - The result is much higher or lower than expected: Edit the formula and inspect the referenced range. Look for an included total, a zero that should or should not count, duplicated rows, and numbers stored as text. Also check whether the values use consistent units; averaging values in minutes and seconds together will not produce a meaningful result.
- The formula appears as text in the cell: The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General on the Home tab, edit the cell, and press Enter. Confirm that the formula starts with an equals sign and that Show Formulas is not enabled. On Windows, Ctrl+` toggles Show Formulas; on many keyboards the backtick key is near the top-left. On Mac, use Control+` where that shortcut is available, or turn off Show Formulas from the Formulas tab.
- The answer does not update after editing a value: Confirm that the edited cell lies inside the formula’s range. Then check the calculation setting: on Windows, go to Formulas > Calculation Options and select Automatic if appropriate. Excel for Mac has the calculation options on the Formulas tab as well. If calculation is set to Manual, recalculate the workbook; on Windows, F9 calculates open workbooks. Excel for the web generally recalculates formulas automatically.
- The formula returns an error from one cell: Find error values inside the selected range and inspect the formulas or source data that created them. AVERAGE does not silently skip errors. Correct the underlying error or deliberately revise the calculation to exclude invalid records; avoid hiding errors without deciding how they should affect the result.
Frequently Asked Questions
Does AVERAGE count blank cells?
Empty cells in a referenced range are ignored; they do not count as zero or as an observation. A cell containing the numeric value 0 does count. Text in referenced cells is also generally ignored by AVERAGE.
How do I average an entire Excel column?
You can reference a full column, such as =AVERAGE(B:B), when the result is placed outside column B. This includes every numeric entry in that column, so it can also include unrelated numbers elsewhere in it. For a cleaner calculation, use a defined range or an Excel table column.
Can I average a column while ignoring zeroes?
Yes. If your data is in B2:B10 and zero means missing rather than a real result, use =AVERAGEIF(B2:B10,"<>0",B2:B10). If zero is a valid measurement, keep it in the calculation because excluding it would change the meaning of the average.
How do I average only filtered, visible rows?
Use SUBTOTAL with function number 1 for visible rows after filtering. For B2:B10, =SUBTOTAL(1,B2:B10) ignores rows filtered out, but still includes manually hidden rows. Use function number 101 in place of 1 if you also want to ignore manually hidden rows.
Why does my average have decimals when I expected a whole number?
The arithmetic mean can be fractional even when every input is a whole number. You can change the result cell’s displayed decimal places on the Home tab. If you need a rounded value for another calculation, use a rounding formula rather than changing only the display format.
Does the formula work in Excel for Mac or Excel for the web?
Yes. The AVERAGE function and standard range references work in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The ribbon may look different between versions, but entering the formula in a cell works the same way.
Related
- Remove Duplicates in Excel by Entire Row: A Safe Guide
- Remove Duplicates in Excel: A Clear Step-by-Step Guide
- Clean Excel Rows: Remove Blanks, Duplicates, and Errors
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 Schedule an Email in Outlook on Any DeviceTo schedule an email in Outlook, write your message, open the menu beside Send, choose Schedule send or…
- Make a Flowchart in Word From Excel: Steps and OptionsYes. Use the steps or decisions in your Excel sheet as a plan, then build the diagram in…
- Excel Not Responding: How to Save Your Work SafelyIf Excel says Not Responding, first stop clicking and give it time to finish; if the window still…
- Best Way to Make a Booklet in Word: Layout and PrintingFor a printed, folded booklet, the best way is Word’s built-in Book fold layout: it arranges pages in…
- How to Manually Archive Emails in Microsoft OutlookTo manually archive an email in Outlook, select the message and choose Archive; Outlook moves it to the…
- Outlook Signature: How to Create and Set It Up for EmailTo create a signature message in Outlook, open the signature settings for your version, add your name and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.