To find an average in Excel, use the AVERAGE function. With the numbers 12, 15, 18, 20, and 25 in cells B2:B6, enter =AVERAGE(B2:B6) in an empty cell; Excel returns 18. The function adds the numeric values and divides by how many numbers there are. You can also choose Average from Excel’s AutoSum menu if you prefer not to type a formula.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows. The same AVERAGE formula works in Excel for Mac and Excel for the web, though the ribbon layout can differ slightly.
- A range of cells containing the values you want to average. For the examples below, use a small sample list or substitute the range and cell references from your own worksheet.
- An empty cell in which to show the result. Make sure it is not inside the data you are averaging, unless you specifically want the result included in a later calculation.
Step-by-Step
- Check the data you want included. Find the column or row containing the values. Confirm that the range starts and ends where you intend: a heading, subtotal, or unrelated number included by mistake can change the result. For example, if five scores are in B2:B6, the range should be B2:B6 rather than the entire column B. AVERAGE can work with a longer range too, but a precise range is easier to review.
- Choose where the result will appear. Click an unused cell near the data, such as B7 if the five sample values are in B2:B6. The result cell should not be part of the range being averaged. If you put a formula within its own range, Excel may report a circular reference because the calculation depends on itself.
- Enter the AVERAGE formula. With the sample values 12, 15, 18, 20, and 25 in B2:B6, type
=AVERAGE(B2:B6)into B7 and press Enter. Excel calculates (12 + 15 + 18 + 20 + 25) ÷ 5, which equals 18. The equals sign tells Excel to calculate; AVERAGE is the function name; and the cell range in parentheses tells it which values to use. If your numbers are in another range, replace B2:B6 with your actual range. - Use AutoSum if you would rather select a command. Click an empty cell where the result should go. On the Windows ribbon, open Home > AutoSum using the arrow beside the AutoSum button, then choose Average. Excel inserts a suggested range. Inspect the highlighted cells before pressing Enter; adjust the reference in the formula bar if the suggestion misses a value or includes the wrong cells. The same command may also be available from the Formulas tab. On Mac or Excel for the web, ribbon placement can vary, but you can always enter the AVERAGE formula directly.
- Check the displayed result and formatting. Click the result cell and look at the formula bar to verify the range. Excel may display a decimal, depending on the values. To change how many decimal places appear, select the result and use the decimal-place controls in Home > Number. Formatting changes only how the value looks; they do not change the underlying calculation. For example, showing 18.00 instead of 18 does not make the average more precise.
- Average only values that meet a condition, if needed. Suppose A2:A6 contains the labels North, South, North, North, South, and B2:B6 contains 12, 15, 18, 20, 25 in the same row order. To average only the North values, enter
=AVERAGEIF(A2:A6,"North",B2:B6)in an empty cell. The matching values are 12, 18, and 20, so the result is 16.67 (50 divided by 3, rounded for display). AVERAGEIF takes the cells to check, the condition, and the cells to average. Text conditions go in straight double quotation marks. If the values to check and average are in the same range, the final range argument can be omitted. - Decide whether zero values belong in the calculation. AVERAGE includes zero as a numeric value, which is usually right when zero means a genuine result. If B2:B5 contains 0, 10, 20, and 30 and you intentionally want to ignore zeros, enter
=AVERAGEIF(B2:B5,"<>0"). The result is 20, because Excel averages 10, 20, and 30. Do not exclude zeros just to make the average higher; first establish whether zero represents a real observation or a placeholder in your data.
For a conventional mean, use AVERAGE rather than calculating a total by hand and guessing how many entries to divide by. If you add more values later, update the range so the new entries are included. If the data is regularly extended, you can make it an Excel Table with Insert > Table; table formulas can use named columns, which are often easier to maintain than fixed cell references.
Common Mistakes
- Including the wrong cells: An extra figure, heading, or subtotal can lead to an unexpected result. Review the range shown in the formula bar, or select the intended data before creating the formula.
- Assuming blanks and zeros are treated alike: AVERAGE ignores empty cells in a referenced range, but it counts cells containing 0. An empty cell does not affect the divisor; a zero does.
- Using numbers stored as text: A cell may look like a number but be stored as text, especially after pasting data from a website or a text file. Text entries in a referenced range are not included as numeric values. Check for warning indicators, inconsistent alignment, or a number format issue.
- Expecting AVERAGE to skip errors: An error value such as #N/A or #DIV/0! within the range can make the average formula return an error. Find and correct the source error, or decide deliberately how error rows should be handled before calculating.
- Rounding the source values unnecessarily: If you round every input before averaging, you may lose precision. Keep the source values intact and adjust the result’s displayed decimal places instead, unless your task requires rounding each observation.
Tips
- Use a clear label. Put a label such as Average score beside the result so another reader can tell what it represents. Include the relevant period or group if the worksheet contains several averages.
- Keep the range consistent. When comparing groups, use the same rules for blanks, zeros, and invalid entries in every group. Otherwise, two averages may not be comparable even if the formulas look similar.
- Check the count as well as the average. An average based on two entries can tell a different story from the same average based on two hundred. If sample size matters, count the numeric observations separately and report it beside the average.
- Remember that an average can be a value not present in the data. That is normal. In the example, 18 happens to be one of the values, but a different list could produce an average between two observations.
- Use a weighted average when entries have different importance. A simple average gives every value equal weight. If scores are in B2:B4 (80, 90, 70) and their weights are in C2:C4 (1, 2, 1), enter
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)in an empty cell. The result is 82.5: (80×1 + 90×2 + 70×1) divided by (1 + 2 + 1). Use this only when the weights genuinely represent how much each value counts. - Consider what dates mean in your sheet. Excel stores dates as serial numbers, so averaging date cells can produce a midpoint date. That can be useful for finding a typical date, but check the result’s date format and confirm that a midpoint is meaningful for your question.
Troubleshooting
- The result is #DIV/0!: The selected range may contain no numeric values, or an AVERAGEIF condition may match no numeric cells. Check that the references are correct, that the values are numbers rather than text, and that the condition matches the labels exactly. If no matching values exist, revise the condition or handle that case separately.
- The result is #VALUE! or another error: Look through the referenced cells for an error value, then fix its source if possible. AVERAGE does not automatically discard errors. Also check that the formula has balanced parentheses and that the range reference uses a colon between its first and last cell, such as B2:B6.
- The average seems too high or too low: Click the formula cell and inspect which cells are highlighted. Look for an accidental heading or subtotal, omitted values, zeros that should or should not count, and numbers stored as text. Verify the calculation with a small hand-check: add the included values and divide by their count.
- Excel displays the formula instead of a result: Check whether the cell is formatted as Text. Change it to General from Home > Number, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. Also make sure the formula begins with an equals sign and that Formulas > Show Formulas is not turned on.
- AutoSum chose the wrong range: Before pressing Enter, replace the suggested reference with the range you want. AutoSum makes a guess based on nearby data; it cannot know whether a neighboring number is a subtotal or belongs to a different group.
Frequently Asked Questions
What is the formula for average in Excel?
Use AVERAGE followed by the cell range in parentheses. For example, if the values are in C3:C12, enter =AVERAGE(C3:C12) in a different cell. Replace that range with the cells in your worksheet.
Does Excel include blank cells when finding an average?
No. AVERAGE ignores blank cells within a referenced range. It does include numeric zeros, so a zero changes both the total and the number of values being averaged.
How do I average a column without including its heading?
Start the range at the first row containing a number rather than the heading row. For example, if the heading is in B1 and data runs from B2 through B20, average B2:B20.
Can I average only values that meet a condition?
Yes. Use AVERAGEIF for one condition, such as averaging sales for one region. Specify the cells containing the labels, the condition to match, and the corresponding cells containing the numbers. Check that the label and number ranges cover the same rows.
Why is my Excel average different from the one I calculated by hand?
Excel may be averaging a different set of cells, counting zeros you left out by hand, or ignoring values stored as text. Compare the range and included numeric cells, then divide their total by their count. Also distinguish displayed rounding from the full stored result.
Can I find an average in Excel for Mac or the web?
Yes. The AVERAGE function works in Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon positions can vary, so typing the formula into a cell is a reliable option in all three.
Related
- GST Number to PAN in Excel: Extract the 10-Character PAN
- How to Extract a Filtered List in Excel
- How to Extract Data From a Picture in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel XLOOKUP Between Two Worksheets: ExamplesIf your lookup value is on one worksheet and the data to search is on another, XLOOKUP can…
- Why Is HLOOKUP Used in Excel? Uses and Examples ExplainedHLOOKUP is used in Excel to find a value in the top row of a table and return…
- Excel Due Date Formula: Examples and Workday OptionsFor a basic due date in Excel, enter a start date in A2 and the number of calendar…
- INDIRECT Excel Function: Examples, Uses, and LimitsIn Excel, INDIRECT converts text into a live cell or range reference; if A2 contains B5, =INDIRECT(A2) returns…
- How to Copy and Concatenate Text in Excel with FormulasTo combine text from two cells, put the values in A2 and B2, then enter =A2&" "&B2 in…
- Divide Symbol on Keyboard in Excel: Use / for FormulasIn Excel, the divide key for calculations is the forward slash (/): enter a formula such as =A1/B1…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.