To calculate an average in Excel, enter =AVERAGE(B2:B11) in an empty cell, replacing B2:B11 with the cells you want to average, then press Enter. Excel adds the numeric values in that range and divides by how many numbers it contains. Before you start, check that the data is arranged in a range and that you know whether blanks, zeros, or filtered rows should count.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows.
- A range of cells containing the numbers you want to average. Labels may be in a neighboring column, but keep them outside the numeric range.
- An empty cell for the result, ideally labeled “Average” so it is easy to identify later.
You can use the same formula in Excel for Mac and Excel for the web. Ribbon controls may be arranged differently in those versions, but the formula itself works the same way.
Step-by-Step
- Check and organize your data. Put the values you want to average in one column or row, with one value per cell. For example, you might list ten test scores in B2:B11 and put a heading such as “Score” in B1. A heading is fine because it is outside the range. Remove accidental spaces or notes from the numeric cells, and decide whether a zero means a real result or should be treated differently. This quick check helps prevent a technically correct formula from answering the wrong question.
- Choose an output cell. Click a blank cell where the result should appear, such as B12 beneath the scores. Keeping the answer outside the data range avoids including the average itself in the calculation. If you have a table, you can put the result beside it and label it in the neighboring cell. Avoid placing the formula inside the values you intend to average, especially if you plan to extend the data later.
- Enter the AVERAGE formula. Type the function name, an opening parenthesis, the range, and a closing parenthesis. With scores in B2:B11, enter
=AVERAGE(B2:B11). The colon means “through,” so B2:B11 includes every cell from B2 to B11. You can type a different range or select the cells with the mouse while entering the formula. Be sure the reference includes all intended numbers and does not include a heading or the result cell. - Confirm the calculation and result. Press Enter to calculate. If B2:B11 contains 72, 85, 91, 68, 84, 100, 75, 80, 90, and 85, the sum is 830 and there are ten numbers, so the average is 83. Click the result cell to see its formula in the formula bar. If the displayed answer has too few decimal places, select the cell and use Home > Increase Decimal in the Number group. Changing the displayed decimals changes how the answer looks, not the underlying calculation.
- Use AutoSum if you prefer the ribbon. Select the blank cell directly below or beside the numbers. On Windows, go to Home > Editing > AutoSum arrow > Average. Excel inserts an average formula and highlights the range it thinks you want. Check that the outlined cells are correct before accepting the suggestion; adjust the reference if Excel selected the wrong cells, then press Enter. AutoSum is a shortcut for inserting a formula, not a separate averaging method. On Mac or the web, look for the AutoSum control on the Home ribbon; its location can vary by layout.
- Average only values that meet a condition, if needed. For instance, suppose department names are in A2:A11 and their scores are in B2:B11. To average scores only for rows labeled North, enter
=AVERAGEIF(A2:A11,"North",B2:B11)in an empty cell. AVERAGEIF checks the first range for the criterion and averages the corresponding values in the second range. The text criterion must match the entries in column A. This is useful when one list contains several groups and you need a separate average for one group rather than for every number. - Check what the result represents. A standard average gives each included numeric value equal weight. It does not account for different importance, such as a final exam counting more than a quiz. If your data has weights or represents rates with different denominators, the simple average may not be the statistic you need. Confirm that the calculation matches the question you are trying to answer before sharing the result.
Common Mistakes
Including the result cell in the range: If the formula is in B12, averaging B2:B12 includes the formula cell itself. This can cause a circular reference warning or an incorrect result. Select only the source data, such as B2:B11, and keep the answer outside it.
Assuming a blank and a zero mean the same thing: AVERAGE ignores genuinely empty cells but counts numeric zeros. If a zero is a valid score, counting it is usually appropriate. If zero means “not recorded,” the formula will still include it, so correct the data or use a formula that deliberately excludes zeros.
Including a label or unrelated numbers: Text labels in a referenced cell are ignored, but selecting extra numeric cells can silently change the answer. Check the highlighted range or formula reference, particularly if another table or a total is nearby.
Expecting filtered-out rows to disappear from an ordinary average: AVERAGE includes values in rows hidden by a filter. If you want an average of only visible, filtered results, use SUBTOTAL instead. For example, with the values in B2:B11, enter =SUBTOTAL(1,B2:B11) in an empty cell. The function number 1 requests an average, and filtered-out rows are excluded. Manually hidden rows are still included with 1; use 101 if manually hidden rows should also be excluded.
Rounding the data too soon: Formatting a result to show fewer decimal places does not round the value used in later calculations. Conversely, replacing source values with rounded numbers can change the average. Keep the original precision unless your task specifically calls for rounding.
Tips
Use a clear heading for the result, such as “Average score,” and keep the formula in a consistent summary area. This makes the workbook easier to understand when someone opens it later. If the data is likely to grow, consider converting it into an Excel table using Insert > Table. A formula based on a table column can expand as rows are added, whereas a fixed reference such as B2:B11 does not automatically include values added in B12.
For a quick check without adding a formula, select the numeric cells. Excel may show Average, Count, and Sum on the status bar at the bottom of the window. If Average is not visible, right-click the status bar and select Average in its menu. This is a convenient glance at the selected cells, but it does not put a reusable result in the worksheet. Check that you have selected exactly the intended values.
Keep the number format appropriate to the data. Scores might display as whole numbers or decimals; measurements may need a unit shown in a neighboring heading. Percentages should be stored as numeric percentages and formatted accordingly. The number format affects display, while AVERAGE works with the underlying numeric values.
If you need separate averages for multiple categories, organize the category labels in one column and the measurements in another. AVERAGEIF handles one condition; AVERAGEIFS can handle multiple conditions. Confirm that each condition refers to the matching rows and that the ranges are the same size. For a small one-off calculation, selecting the relevant rows manually can be simpler, but a criteria formula is easier to update in a recurring report.
Troubleshooting
The result shows #DIV/0!: Excel has no numeric values to average in the referenced cells. Check that the range is correct and that the entries are numbers, not numbers stored as text. If the range is intentionally empty, wait until data is entered or use a controlled error-handling approach so the workbook displays an appropriate message instead of an error.
The answer is unexpectedly low or high: Inspect the reference in the formula bar and compare it with the intended data. Look for zeros, duplicated entries, a total included among the individual values, or numbers outside the range that should be included. You can also calculate the sum and count separately to check the arithmetic: the average is the sum divided by the number of included numeric cells.
A number is being ignored: It may be stored as text. Look for a warning indicator, a leading apostrophe, or inconsistent alignment. Select the affected cells and use the warning icon’s Convert to Number option when it appears. You can also remove unwanted spaces or re-enter the value as a number. Do not convert labels or identifiers that are intentionally text.
The formula appears as text instead of calculating: Check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell format to General, then edit and re-enter the formula so Excel evaluates it. Also confirm that the formula begins with an equals sign and that calculation is not being delayed by workbook settings.
The formula gives an unexpected answer after filtering: Remember that AVERAGE counts filtered-out rows. For visible rows only, use SUBTOTAL as described above and verify the function number matches how you want manually hidden rows handled. If the result is based on an average of a filtered list, changing the filter can change the SUBTOTAL result.
Excel rejects a formula with a condition: Check that the criteria range and average range contain the same number of rows, and that the criterion matches the category text. Excel installations using regional settings that require semicolons may need semicolons instead of commas in formulas with multiple arguments. The single-range AVERAGE example has no argument separator to change.
Frequently Asked Questions
What is the basic formula for an average in Excel?
Use AVERAGE with the cells you want to include. With values in B2:B11, enter =AVERAGE(B2:B11) in an empty cell. Replace that range with your own cell reference.
Does Excel include zero in an average?
Yes. A numeric zero counts as a value, while a truly blank cell is ignored. If zero represents missing information rather than a real result, decide how to handle those records before calculating.
Does AVERAGE include numbers stored as text?
No. Text in referenced cells is not counted as a numeric value. Convert numeric-looking text to numbers if it should be part of the calculation.
How do I average only visible rows?
For filtered data, use SUBTOTAL with function number 1 for an average; filtered-out rows are excluded. Use 101 instead if manually hidden rows should also be excluded. An ordinary AVERAGE includes filtered-out values.
Can I average cells that are not next to each other?
Yes. With values in B2:B5 and D2:D5, enter =AVERAGE(B2:B5,D2:D5) in an empty cell. This averages the numeric entries in both ranges and skips the cells between them.
Why does my average show many decimal places?
The underlying result may be more precise than the display. Select the result and adjust the decimal places from Home > Increase Decimal or Decrease Decimal. This changes the display format, not the stored result.
Related
- How to Build a Forecast Model in Excel
- How to Build a Debt Schedule in Excel Step by Step
- Dynamic Forecast in Excel: Build One That Updates
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 Delete a Page Range in Word Without Losing TextTo delete a page range in Word, select the content from the beginning of the first page through…
- Excel Next Tab Shortcut: Move to the Next WorksheetIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next worksheet tab; press…
- Excel Data Model: How to Combine Tables and Analyze ThemYes. In Excel for Microsoft 365 on Windows, you can combine tables in the Data Model by adding…
- Lock Track Changes in Word Online: What You Can DoYou can’t password-lock Track Changes directly in Word for the web. To make sure collaborators can’t turn tracking…
- Change Email List View in Outlook: A Step-by-Step GuideTo change your email list view in Outlook, open the view or layout controls and choose how messages…
- Excel Consolidate Button: Where It Is and How to Use ItIn Excel for Microsoft 365 on Windows, find the Consolidate button on the Data tab, in the Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.