How to Get the Average Number in Excel with a Formula

To get the average number in Excel, enter =AVERAGE(B2:B10) in an empty cell, replacing B2:B10 with the range that contains your numbers, then press Enter. Excel adds the numeric values in that range and divides by how many numbers it finds. The result updates when the values change, so this is a useful way to calculate a class average, typical sale, or other mean.

What You’ll Need

An Excel workbook with the numbers you want to average. This guide is for Microsoft Excel for Microsoft 365 on Windows.
A blank cell for the result. It is usually easiest to place it outside the range being averaged.
A clear idea of which values should count. In particular, decide whether zero values belong in the calculation.

You do not need to sort the numbers or calculate their sum by hand. Excel’s AVERAGE function handles both parts. You can use it in Excel for the web as well; the formula works the same way. On Mac, the formula and the steps for entering it are also the same, although some ribbon and keyboard details can differ.

Step-by-Step

Identify the number range. Look at the column or row containing the values. Note the first and last cells you want to include. For example, if monthly amounts are in cells B2 through B10, the range is B2:B10. Do not include a heading such as Sales in B1 unless you intentionally want it considered as data. AVERAGE ignores text in a referenced range, but setting the range accurately makes the calculation easier to check.
Choose where the answer should appear. Click an empty cell near the data, such as B11 if the values end at B10, or a summary cell elsewhere on the worksheet. Avoid placing the result inside the cells being averaged: including the answer cell in its own range can create a circular reference or distort the intended calculation.
Enter the AVERAGE formula. With the values in B2:B10, click the result cell, type =AVERAGE(B2:B10), and press Enter. Include the equals sign, the function name, and parentheses. A colon between the first and last cell means Excel should include every cell in that continuous range. Excel for Microsoft 365 on Windows uses commas to separate separate arguments; this one-range formula does not need a comma.
Check the displayed result. Excel calculates the mean and shows it in the cell. To verify the calculation independently, consider a small example: if B2:B6 contain 12, 15, 18, 20, and 25, their total is 90 and there are five values, so the average is 18. With those values in B2:B6, the formula is =AVERAGE(B2:B6), and it returns 18. If the answer does not match your expectation, check which cells are included and whether any values are stored as text.
Apply a useful number format if needed. The average may display with many decimal places or in a format that does not suit the data. Select the result cell, then use Home > Number to choose a format such as Number, Currency, or Percentage. Use Increase Decimal or Decrease Decimal to control how many decimal places appear. Formatting changes how the result is displayed, not the underlying average.
Average separate ranges when the values are not together. AVERAGE can take more than one range as an argument. For example, if the first group of measurements is in B2:B5 and another group is in D2:D5, enter =AVERAGE(B2:B5,D2:D5) in the result cell. Excel averages all numeric cells in both ranges together; it does not calculate two separate averages and then give each group equal weight. This distinction matters when the groups contain different numbers of entries.
Use AutoSum if you prefer the ribbon. Select the blank result cell beneath or beside the values, then choose Home > AutoSum > Average. Review the highlighted range before accepting it, because Excel guesses which nearby cells you mean. If the outline includes a heading, misses the last value, or includes unrelated cells, edit the proposed range and press Enter. You can also type the AVERAGE formula directly whenever AutoSum selects the wrong cells.
Confirm that the result responds to edits. Change one included value temporarily, or inspect the formula bar after selecting the answer cell. The formula should still refer to the intended cells, and Excel should recalculate when a value changes. Undo a temporary test with Ctrl+Z on Windows or Command+Z on Mac. Once you have confirmed the formula, restore any value you changed.

For an average that excludes values meeting a condition, use a conditional-average function rather than manually deleting data. For example, if scores are in B2:B10 and you want scores associated with the matching categories in A2:A10, AVERAGEIF can calculate a conditional mean. Make sure the criteria range and average range cover corresponding rows. If the goal is simply to average every number in a range, AVERAGE is the more direct choice.

Common Mistakes

Including the wrong cells. A range that starts one row too high or ends one row too soon changes the result. Check the colored outline Excel displays while you edit a formula, and compare the range endpoints with your first and last intended values.
Assuming blank cells count as zero. AVERAGE ignores truly empty cells, so blanks do not lower the result. A cell containing the number 0 does count and can lower it. If a zero means missing data in your worksheet, decide whether it should be excluded before choosing a formula.
Expecting text numbers to behave like numeric values. Values such as numbers imported with leading apostrophes or inconsistent separators may be stored as text. AVERAGE generally ignores text within a referenced range. Convert the entries to real numbers before relying on the result.
Rounding the data instead of the display. Deleting decimal places from the original values changes the calculation. If you only want a cleaner-looking answer, keep the full-precision values and adjust the number format of the result cell.
Averaging averages without considering group sizes. The average of two group averages is not necessarily the same as the average of all original observations. When groups have different numbers of values, average the underlying observations together if each observation should carry equal weight.

Tips

Use a clear label. Put Average beside the result so another person can tell what the number represents. If several measures appear together, label the unit too, such as Average time in minutes.
Keep the formula easy to maintain. When adding rows regularly, consider converting the data into an Excel table with Insert > Table. Table formulas can expand as you add records, reducing the chance that a fixed range leaves out new values. Check the formula after converting or rearranging data.
Show only as much precision as readers need. A result might be 17.6667 internally but display as 17.67. The underlying value remains available to other formulas, which is usually preferable to rounding the source data.
Be careful with filtered lists. AVERAGE includes values in rows hidden by a filter. If you need an average that responds to filtered-out rows, use SUBTOTAL instead. With the values in B2:B10, =SUBTOTAL(101,B2:B10) averages visible rows and ignores rows hidden by a filter or manually hidden. Function number 101 is the average option that also excludes manually hidden rows.
Use consistent units. An average of centimeters and meters, or minutes and seconds, is not meaningful unless the values are converted to the same unit first. Check that the cells represent comparable measurements, not just numbers.

Troubleshooting

The result is #DIV/0!. AVERAGE returns this error when the referenced cells contain no numeric values to average. Check that the range is correct and that the entries are numbers rather than text. If the data is expected to be empty sometimes, display a note or use an error-handling formula appropriate to your worksheet rather than assuming the average is zero.
The result seems too low or too high. Inspect the included cells for an accidental zero, an outlier, an extra row, or values with a different unit. Also check whether a filter is active: AVERAGE still includes filtered-out rows, while SUBTOTAL can average only visible rows.
Some entries are ignored. Select a suspicious cell and look at the formula bar. If the value is left-aligned or has an apostrophe, it may be text. Try converting the affected cells using Data > Text to Columns and finish the wizard without changing the default options, or use the warning indicator’s Convert to Number option when Excel offers it. Confirm the cells are numeric before recalculating.
The formula appears as text instead of producing an answer. The cell may be formatted as Text, or the formula may have a leading apostrophe or space before the equals sign. Change the cell format to General, edit the formula, and press Enter. If Show Formulas is on, toggle it from Formulas > Show Formulas or press Ctrl+` on Windows; on Mac, use Control+` where that shortcut is available, or switch it off on the Formulas tab.
The answer does not update after an edit. Check Formulas > Calculation Options and select Automatic. In Manual calculation mode, Excel may wait to recalculate; press F9 on Windows to recalculate. On Mac, use Formulas > Calculate Now or the available recalculation command. Also verify that the changed cell is actually inside the formula range.
Excel rejects the formula syntax. Make sure the name is AVERAGE, the parentheses are balanced, and the range uses a colon. If you are entering a formula with multiple arguments and your regional settings use semicolons as list separators, Excel may require semicolons instead of commas. The one-range formula =AVERAGE(B2:B10) avoids that separator issue.

Frequently Asked Questions

What is the formula for average in Excel?
Use AVERAGE followed by the cells to include. If the values are in B2:B10, enter =AVERAGE(B2:B10) in an empty cell. Replace the example range with the range in your worksheet.

Does Excel include zero when calculating an average?
Yes. A zero is a numeric value and counts in the calculation. Empty cells are ignored. If your zeros indicate missing measurements rather than real zero values, decide how to handle them before interpreting the result.

Does AVERAGE ignore blank cells and text?
When you reference a range, AVERAGE ignores empty cells and text entries, but includes numeric values and zeros. A number stored as text is not treated like a normal numeric entry in that range, so convert it if it should count.

How do I average only visible rows in a filtered list?
Use SUBTOTAL with function number 101. With the numbers in B2:B10, enter =SUBTOTAL(101,B2:B10). This calculates the average of visible rows and excludes manually hidden rows as well as rows removed by a filter.

Can I average numbers from more than one range?
Yes. Separate the ranges with commas in Microsoft 365 for Windows. If values are in B2:B5 and D2:D5, use =AVERAGE(B2:B5,D2:D5). This gives each numeric cell equal weight, regardless of which range it comes from.

How do I round the average to two decimal places?
Select the result cell and use Home > Number to set two decimal places; this changes the displayed precision without altering the source values. If another formula must use a rounded value, a ROUND formula can do that, but the rounding should match the purpose and accuracy requirements of the data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.