Find the Average in Excel with the AVERAGE Function

Formulas & functions · Updated November 20, 2026

To find the average in Excel, enter =AVERAGE(B2:B6) in an empty cell and press Enter, replacing B2:B6 with the cells containing your numbers. Excel adds the numeric values and divides their total by how many numbers are included. Before you start, check that the selected range contains the data you want to include; blank cells and text are normally ignored, but zero values count.

What You’ll Need

  • An Excel workbook containing the numbers you want to average. The steps below use Microsoft Excel for Microsoft 365 on Windows.
  • An empty cell where the result can appear. If you place the formula inside the range being averaged, you can create a circular reference or include the result cell by mistake.
  • Optionally, a clear idea of which values should count. For example, decide whether zero means a real result or a missing entry before calculating.

Step-by-Step

  1. Check the data you want to average. Find the column or row containing the values, and note the first and last cells. In the example, the five values are in B2:B6: 12, 15, 18, 20, and 25. Decide whether all of them belong in the calculation. If a heading is above the numbers, leave it out of the range; if there are unrelated values below them, do not include those either.
  2. Select an empty result cell. Click a blank cell near the data, such as B7 or D2. Choosing a cell outside the range makes it easier to see the answer and reduces the chance of accidentally including the formula cell in its own calculation. You can also put the result on another worksheet if that is more convenient.
  3. Enter the AVERAGE formula. With the sample values in B2:B6, type =AVERAGE(B2:B6) into the selected empty cell. A colon between cell references means “through,” so B2:B6 includes B2, B3, B4, B5, and B6. For your own sheet, replace the reference with the actual range, such as C3:C40 or A2:A12.
  4. Press Enter and check the result. Excel displays 18 for the sample data. The arithmetic is (12 + 15 + 18 + 20 + 25) ÷ 5 = 90 ÷ 5 = 18. The cell shows the calculated value, while the formula bar shows the formula when you select it. If the result looks unexpected, verify the range and the values before changing the formula.
  5. Use the mouse to select a range if you prefer. Click the empty result cell, type =AVERAGE(, then drag across the cells you want to include. Type the closing parenthesis and press Enter. Excel inserts the selected reference into the formula. This can help avoid typing a long range, but inspect the highlighted cells before confirming that the correct rows and columns are selected.
  6. Average separate ranges when needed. You can provide more than one range in the same AVERAGE formula. With the first group of numbers in B2:B4 and a second group in D2:D4, enter =AVERAGE(B2:B4,D2:D4) in an empty cell. Excel combines the numeric values from both ranges for one average; it does not calculate an average of the two range averages. In a regional settings setup that uses semicolons as list separators, Excel may require semicolons between separate arguments.
  7. Format the answer for readability. If Excel displays too many decimal places, select the result cell and use Home > Number to choose a number format or adjust the decimal places. Formatting changes how the result is displayed, not the underlying calculation. For example, rounding the display to two decimal places does not turn the stored result into a different number.
  8. Calculate an average for matching rows, if appropriate. If column A contains region names and B contains amounts, you can use AVERAGEIF for one condition. With A2:A7 holding East, East, West, East, West, West and B2:B7 holding 10, 20, 15, 30, 25, 35, enter =AVERAGEIF(A2:A7,"East",B2:B7) in an empty cell. The result is 20: the three East values (10, 20, and 30) add to 60, then divide by 3. This is useful when you need an average for a category rather than for every row.

Common Mistakes

Including the wrong cells: A range that is too short leaves out data, while one that is too long may include unrelated numbers. Click the result and inspect the formula’s highlighted range, or double-click the formula cell to review its references. Check both the first and final row, especially when the data has recently been extended.

Assuming blanks and zeros mean the same thing: AVERAGE ignores empty cells in a referenced range, but includes numeric zeros. If the range contains 10, 20, and 0, the average is 10, not 15. If a zero is a real measurement, it should usually remain in the calculation. If it represents missing data, decide how to handle it rather than silently treating it as a valid value.

Including numbers stored as text: A cell can look like a number but be stored as text, often after data is imported or copied from another system. Text values in referenced cells are ignored by AVERAGE. This can make the result higher or lower than expected because fewer values are used. Check the cells’ alignment and warning indicators, and convert genuine numeric text to numbers when needed.

Using an ordinary average when a weighted average is needed: AVERAGE gives every numeric entry equal weight. That is suitable for, say, the average of individual test scores when each score counts equally. It is not automatically suitable for combining group averages of different sizes, or for averaging unit prices when quantities differ. Confirm what each row represents and whether each value should contribute equally.

Confusing a displayed value with its stored value: A cell formatted to show no decimal places may contain a value such as 18.4. AVERAGE uses the underlying numeric value, not just the rounded appearance. Increase the displayed decimal places if you need to inspect the precision behind the answer.

Tips

Use a table or a consistently maintained range if new records are added regularly. In a formatted Excel table, formulas can use column names instead of fixed cell addresses, which can make them easier to understand and maintain. For a small one-time calculation, a direct range such as B2:B20 is usually clear and sufficient.

To get a quick check without adding a formula, select the numeric cells. In Excel for Microsoft 365 on Windows, the status bar at the bottom of the window can show Average, Count, and Sum for the selection. If Average is not visible, right-click the status bar and select Average in the list. The status-bar value is a quick readout, but it is not placed in a worksheet cell; use a formula when you need a saved, reusable result. On Excel for Mac, select the cells and check the status bar; available summary items can also be adjusted from its context menu.

Keep the data and calculation separate where practical. A label such as “Average score” beside the formula makes the result easier to interpret later, particularly when a workbook contains several summaries. If you copy a formula to another location, check whether its cell references changed as intended. Excel normally adjusts relative references when formulas are copied.

When the data represents a filtered list, decide whether the average should include hidden or filtered-out rows. AVERAGE on a regular range includes numeric values in rows hidden by a filter. If you specifically need a calculation that responds to filtering, use a suitable function such as SUBTOTAL and verify its behavior for your workbook and filter setup; do not assume an ordinary AVERAGE excludes hidden records.

Troubleshooting

The formula appears as text instead of calculating: Confirm that it starts with an equals sign and that the cell is not formatted as Text. Change the cell format to General using Home > Number, then edit the formula (press F2 on Windows) and press Enter. If an apostrophe appears before the equals sign in the formula bar, remove it.

The result is an error: Inspect the referenced cells for error values such as #VALUE! or #N/A; an error in a referenced numeric range can cause the average formula to return an error. Also check for a mistyped range or an accidental reference to a deleted cell. Correct the source error if possible rather than masking it without understanding why it occurred.

Excel says there are too few arguments or highlights part of the formula: Check that the opening and closing parentheses are present and that the range uses a colon, as in B2:B6. When using several separate arguments, your Excel list separator depends on regional settings. If commas are rejected, check whether your installation expects semicolons between arguments; do not replace the colon used to define a continuous range.

The answer is too high or too low: Check for omitted numbers, accidental zeros, numeric text, and unwanted values in the range. Recalculate the example manually for a small sample to see how many values Excel should include. Remember that blank cells do not enter the divisor, whereas zero does. If the formula references separate ranges, confirm that all intended ranges are included exactly once.

The answer does not update after changing data: Press F9 to recalculate in Excel for Windows, or check Formulas > Calculation Options and select Automatic. If calculation is set to Manual, formula results may remain unchanged until you recalculate. Also confirm that the edited source cells are inside the formula’s referenced range.

Frequently Asked Questions

What is the formula for average in Excel?
Use AVERAGE with the cells or ranges you want to include. For numbers in C2:C10, enter =AVERAGE(C2:C10) in an empty cell. Replace the example reference with your own range.

Does Excel include zero when calculating an average?
Yes. A zero is a numeric value, so it counts in both the total and the number of entries. A genuinely empty cell is ignored. Check how zeros are used in your data before deciding whether they belong in the result.

Does AVERAGE ignore text?
Text in cells within a referenced range is ignored, as are empty cells. However, a number stored as text is also ignored, even if it looks numeric. Convert it to a real number if it should count.

Can I average only values that meet a condition?
Yes. AVERAGEIF calculates an average for values whose corresponding cells match one condition, such as a particular department or region. For multiple conditions, Excel also provides AVERAGEIFS; make sure the criteria ranges and average range cover corresponding rows.

How do I show fewer decimal places?
Select the result cell and use the number-format controls on the Home tab to reduce displayed decimal places. This changes the display, not the precision used in later calculations. Use a rounding formula only if you intentionally need to change the calculated value.

Can I see the average without writing a formula?
Yes. Select the numbers and look for Average on the status bar at the bottom of Excel. That summary is convenient for a quick check, while an AVERAGE formula is better when the result needs to stay in the worksheet and update with the data.

Related

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.