How to Average Values from Different Sheets and Cells in Excel
Question details
The user needs to calculate the average of data points spread across multiple worksheets when the values do not reside in the exact same cell reference on each sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating an average from yearly data stored on different worksheet tabs, where at least one target value is located in a different cell address than the rest.
- Observed behavior
- Standard 3D reference formulas fail to compute the average because they require the target data to be in identical cell locations across all sheets.
Ensure you have noted the exact worksheet names and specific cell references for all the individual data points you intend to include in your calculation.
Use Explicit Cell Referencing and Manual Division
Manually add each specific cell reference from the different sheets together and divide the total sum by the exact number of values.
When values are not uniformly located in the same cell on every worksheet, standard cross-sheet range functions like =AVERAGE(Sheet1:Sheet3!A1) cannot be used. Instead, you must build a custom mathematical formula that aggregates each cell individually.
Select the cell where you want the final average to appear, and type `=(` to begin your custom addition formula.
Click on the first worksheet tab, select the target cell containing the value (for example, B88 on the 2021 sheet), and type `+`.
Repeat the previous step for all remaining worksheets. Navigate to each sheet and select the specific cell (e.g., B78 on the 2020 sheet), adding a `+` between each reference.
After selecting the final cell, type `)` to close the bracket, followed by `/` and the total number of cells you referenced (e.g., `7`).
Press Enter to execute the formula. The final formula should look similar to: `=(2021!B88+2020!B78+2019!B78+2018!B78+2017!B78+2016!B78+2015!B78)/7`.
Use the AVERAGE Function with Individual Arguments
Leverage Excel's built-in AVERAGE function by separating each distinct sheet and cell reference with a comma, allowing the function to handle the division automatically.
Calculate Complex Cross-Sheet Averages Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful, intuitive cross-sheet referencing capabilities and full support for advanced mathematical formulas. Whether your data is neatly aligned or scattered across irregular cell locations, calculating accurate averages takes only a few clicks.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multi-sheet data.
- 2. Start the formula: Click the cell where you want the average to appear and type `=AVERAGE(`.
- 3. Select references: Click the first sheet tab, select your target cell, and type a comma. Repeat this for all required sheets and irregular cells.
- 4. Get your result: Type a closing parenthesis `)` and press Enter to instantly calculate your cross-sheet average.
Frequently Asked Questions
What is a 3D reference in Excel?
A 3D reference is a feature that allows you to refer to the exact same cell or range of cells across multiple continuous worksheets. For example, the formula =AVERAGE(Sheet1:Sheet5!A1) calculates the average of cell A1 on sheets 1 through 5. It only works when the data is located in identical cell addresses on every sheet.
Why does my manual addition formula return a #VALUE! error?
This error typically occurs if one of the cells you referenced using the plus (+) operator contains text or a space instead of a number. To avoid this, use the =AVERAGE(cell1, cell2...) function, which automatically ignores text and empty strings.
Can I average cells from different sheets if some of the cells are blank?
Yes. If you use the built-in =AVERAGE() function with individual cell arguments separated by commas, Excel will automatically ignore the blank cells and only divide by the number of cells that actually contain numerical values. Manual division (e.g., dividing explicitly by 7) might result in an artificially low average if a cell is blank.
How do I select cells on different sheets without losing my formula progress?
When you are actively typing a formula (like after typing a plus sign or a comma), you can simply click on a different worksheet tab at the bottom of the screen and click a cell. Excel will automatically write the correct sheet name and cell reference into your formula bar.
