To average the same cell across several worksheets, enter a formula such as =AVERAGE(Jan:Mar!B2) when the sheets are arranged together in tab order. For sheets that are not next to one another, list each cell separately, as in =AVERAGE(Jan!B2,Feb!B2,Mar!B2). Both approaches use Excel’s AVERAGE function; choose based on how your workbook is organized.
What You’ll Need
- An Excel workbook with the worksheets you want to include, and permission to edit it.
- A consistent layout: for example, the value to average is in cell B2 on each sheet. For a multi-cell average, use the same range on each sheet.
- The names of the worksheets to include. For a 3-D reference, the relevant sheets must be between the first and last sheet named in the formula.
The instructions below are for Excel for Microsoft 365 on Windows. The formulas also work in current Excel for Mac and Excel for the web. Button locations and the way you navigate between tabs can differ slightly in those versions.
Step-by-Step
- Check the sheet layout and values. Open each worksheet and confirm that the corresponding values occupy the same cell or range. For example, if you are combining monthly totals, check that each month’s total is in B2, rather than in B2 on some sheets and B3 on others. Make a note of any sheets to exclude, such as a template or a yearly summary, before building the formula.
- Choose where the result will go. Select a blank cell for the average. You can place the result on a summary worksheet, or in an unused cell on one of the source sheets. Avoid putting the formula in a cell that is itself part of the range being averaged; that can create a circular reference. If you are making a summary sheet, give the result a clear label such as Average monthly total.
- Average cells on sheets that are not next to each other. If the worksheets are separated by other tabs, or you want to pick only particular sheets, list each sheet and cell in the AVERAGE formula. With values of 72 in Jan Sales!B2, 84 in Feb Sales!B2, and 90 in Mar Sales!B2, enter this in the result cell:
=AVERAGE('Jan Sales'!B2,'Feb Sales'!B2,'Mar Sales'!B2)
Excel returns 82, because (72 + 84 + 90) / 3 = 82. Sheet names containing spaces need single quotation marks around the sheet name. The exclamation mark separates the sheet name from the cell reference, and commas separate the arguments. Sheet names without spaces can be used without single quotation marks. - Build the formula by selecting cells if you prefer. Type
=AVERAGE(in the result cell, click the first worksheet tab, then click the cell you want to include. Type a comma, select the next worksheet and its corresponding cell, and repeat for each sheet. Type a closing parenthesis and press Enter. Excel inserts the sheet references as you select them. In Excel for Mac or the web, you can use the same approach; select the relevant sheet tabs and cells, then finish the formula in the formula bar or cell. - Use a 3-D reference for a continuous block of sheets. A 3-D reference is convenient when the sheets to include sit together in tab order and contain the same layout. Suppose Jan, Feb, and Mar are consecutive tabs, with the monthly totals in B2 on each sheet. Enter this formula in the summary cell:
=AVERAGE(Jan:Mar!B2)
If those cells contain 72, 84, and 90, the result is 82. The reference covers B2 on Jan, every worksheet between Jan and Mar, and B2 on Mar. It does not mean every sheet in the workbook unless the first and last named tabs span the entire workbook. - Quote sheet names with spaces in a 3-D reference. If the first and last sheets have spaces in their names, put single quotation marks around the sheet-range reference. For example, if the consecutive tabs are North Region, Central Region, and South Region, and the value is in D5 on each, use:
=AVERAGE('North Region:South Region'!D5)
Keep the colon between the first and last sheet names inside the quotation marks. If the name contains an apostrophe, Excel doubles that character when it creates the reference; selecting the cell while building the formula is often the easiest way to avoid typing a sheet name incorrectly. - Average ranges across several sheets. When each sheet has a group of values in the same range, you can provide each range as a separate AVERAGE argument. Assume Jan!B2:B4 contains 10, 20, and 30, and Feb!B2:B4 contains 40, 50, and 60. In the result cell, enter:
=AVERAGE(Jan!B2:B4,Feb!B2:B4)
The result is 35: the six values total 210, and 210 / 6 = 35. This averages the underlying numbers together. It does not calculate each month’s average first and then average those monthly results, which can produce a different answer when the sheets contain different numbers of entries. - Check the result and save the workbook. Compare the formula’s included sheets and cells with your list. If the values are known, work out a quick check using their sum divided by the count of numeric values. Press Enter to commit a formula you have edited, and save the workbook. If someone changes a referenced number, Excel normally recalculates the average automatically.
Common Mistakes
Using a 3-D reference when the sheets are not a continuous group. A reference such as =AVERAGE(Jan:Mar!B2) includes every worksheet between Jan and Mar in tab order. If an unrelated sheet sits between them, its B2 value is included too. Move the relevant tabs together, or list only the desired sheets individually.
Mixing up sheet names, cell references, or punctuation. Each reference needs an exclamation mark between its worksheet name and cell address. A sheet name with spaces needs single quotation marks. One missing comma or parenthesis can make Excel show an error or interpret the formula differently. When in doubt, build the references by clicking the cells rather than typing each name.
Including the wrong cells or the result cell itself. A formula can be valid but still average the wrong data if a sheet uses a different layout. A formula that includes its own result cell may produce a circular-reference warning. Verify both the referenced cell addresses and the location of the formula.
Assuming blanks, text, and zero are treated alike. AVERAGE ignores blank cells and text in referenced cells, but it includes numeric zero. A cell containing a number stored as text may not be counted as a number. This means a missing entry and a recorded zero can affect the average differently; decide which one your data represents before interpreting the result.
Tips
- Use a 3-D reference when the same cell or range is needed from a fixed, consecutive block of worksheets. Use individual references when choosing nonadjacent tabs or when the included sheets need to be explicit.
- Keep repeated worksheets consistent. A template with the same labels and data positions on every tab makes formulas easier to create, check, and maintain.
- Give sheet tabs distinctive names, such as Jan or Feb, and avoid ambiguous names like Sheet1. Clear names make formula references easier to read.
- Think about how the workbook may change. A new sheet inserted between the endpoints of a 3-D reference becomes part of that reference. A sheet added after the last endpoint does not automatically become part of it.
- For a larger number of worksheets, consider keeping the source data in one table with a column for month or category. A single structured dataset can be easier to filter and summarize than a growing collection of sheet-by-sheet formulas.
Troubleshooting
The formula displays an error. Check that it begins with an equals sign, has balanced parentheses, and separates arguments with commas. In Excel settings that use a different regional list separator, Excel may require semicolons instead of commas; use the separator Excel inserts when selecting references. Also check spelling and spaces in sheet names. If you rename a sheet, Excel usually updates references, but a manually typed name may not match an existing tab.
The result is higher or lower than expected. Inspect each source cell, particularly on sheets between the endpoints of a 3-D reference. A hidden or overlooked worksheet can still be part of the reference. Check for zeros, text-formatted numbers, and blanks as well: AVERAGE counts numeric zeros, ignores blank cells, and does not treat text that looks like a number as a numeric value in a referenced range. Convert text-formatted numbers to real numbers if they should count.
Excel shows a circular-reference warning. Find out whether the cell containing the average is included among the cells or ranges being averaged. Move the formula outside the source range or adjust the references so that they exclude the result cell. Do not dismiss the warning unless you intentionally designed a calculation that uses iterative calculation.
The formula does not update the way you expect. Press Enter after editing the formula and verify that the referenced cells are correct. If calculations appear not to refresh, open the Formulas tab and check Calculation Options; set calculation to Automatic if the workbook is meant to recalculate automatically. Also confirm that the source cell really changed and that the formula points to that worksheet.
A sheet name includes an apostrophe or unusual punctuation. Rather than guessing how to type the reference, start the formula, click the sheet tab and target cell, then continue the formula. Excel will create the correctly escaped reference. This is especially helpful for names with spaces, punctuation, or apostrophes.
Frequently Asked Questions
Can I average cells from sheets that are not next to each other?
Yes. Use an individual sheet-and-cell reference for each one, separated by commas inside AVERAGE. This gives you control over exactly which tabs count. A 3-D reference is for a continuous range of worksheets, including any tabs between its endpoints.
Does AVERAGE include blank cells or zeros?
Blank cells are ignored, while numeric zeros are included in the calculation. As a result, an average may be higher if missing entries are blank than if those entries are recorded as zero. Check what the empty cells mean in your data before deciding whether that is appropriate.
Can a formula average a range from every sheet?
A 3-D reference averages the same cell or range across a specified sheet interval. To cover all worksheets, use the first and last tabs as the endpoints, and remember that any sheets between them are included. If you need to exclude a tab in the middle, list the wanted sheets separately.
What happens when I add a worksheet to a 3-D reference?
A new worksheet inserted between the reference’s first and last tabs is included. A worksheet added outside that interval is not included automatically. Review the formula after adding tabs, especially if the new sheet is a template, summary, or other non-data worksheet.
Can I average different ranges on different sheets?
Yes. Add each sheet’s required range as a separate AVERAGE argument, even if the ranges are different sizes. The function averages all numeric values supplied, so make sure the ranges represent comparable data and that the resulting calculation matches your intended method.
Related
- How to Delete an Excel File Safely on a Windows Computer
- How to Change Text to Dates in Excel
- How to Copy an Excel Spreadsheet Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation: How to Circle Invalid Data CellsYes. In Excel for Microsoft 365 on Windows, open Data > Data Tools > Data Validation using its…
- Freeze a Specific Row in Excel: Steps, Limits, and TipsTo keep a row visible while you scroll in Excel for Microsoft 365 on Windows, select the cell…
- Excel Date Shortcut: Insert Today’s Date with Ctrl+;In Excel for Microsoft 365 on Windows, press Ctrl+; (Ctrl plus the semicolon key) to insert today’s date…
- Excel Add Values to a Dropdown List: Easy MethodsTo add a value to an Excel dropdown, edit the list named in its Data Validation settings or…
- Birthday List Excel Template: Setup and TipsA birthday list Excel template is a worksheet for keeping names, birth dates, and useful reminder details together.…
- Can You Put Text With a Checkbox in Google Sheets?Yes—but a Google Sheets checkbox does not display ordinary text inside its box. Put the label in a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.