How to calculate average cells from different sheets in Excel?

If you need to calculate the average of the same range across several worksheets—for example, to find the average sales in range A1:A10 across sheets named Sheet1 to Sheet5—Excel provides a direct formula-based solution. This approach works best when all sheets have the same structure and consistent naming.

Steps:

Select a blank cell where you want the result (e.g., cell C3), and enter the following formula:

=AVERAGE(Sheet1:Sheet5!A1:A10)

After pressing Enter, Excel will return the average value of the specified range across all sheets from Sheet1 to Sheet5.

If you need to average values from different ranges across sheets, you can list them manually:

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

This version is helpful when the ranges vary between sheets. Enter it in your result cell and press Enter.

Kutools for Excel enhances your ability to extract and consolidate values from the same cell or range across multiple worksheets using its Dynamically Refer to Worksheets feature. This is especially helpful when working with a large number of sheets with consistent layout.

Usage steps:

1. Open a new worksheet (such as a summary sheet), then select the cell where you want to calculate the average—for example, D7.

2. Go to Kutools > More (within the Formula group) > Dynamically Refer to Worksheets.

3. In the dialog box:
  - Choose a fill order from the Fill order drop-down (e.g., Fill vertically cell after cell).
  - In the Worksheet list, check the sheets that contain the cell you want to average.
  - Click Fill Range, then close the dialog.

4. The selected cell values will be listed in the range (e.g., D7:D11). Then enter the following formula in another blank cell to calculate the average:

=AVERAGE(D7:D11)

Press Enter to get the result. This simplifies consolidation but does not auto-extend to include newly added sheets—you'll need to rerun the feature when the sheet list changes.

In some cases, you may need to calculate averages for several corresponding cells across multiple worksheets simultaneously—for example, summarizing results for A1, B1, and C1 from every worksheet. This can become cumbersome with standard formulas, but Kutools for Excel's Combine (worksheets and workbooks) utility greatly simplifies this process.

How to use this function:

1. Click Kutools Plus > Combine to open the Combine Worksheets wizard.

2. In the wizard (Step 1 of 3):
Check Consolidate and calculate values across multiple workbooks into one worksheet, then click Next to continue.

3. In Step 2 of 3:
  - Select the worksheets to include under Worksheet list.
  - Use the Browse button to define the range for averaging.
  - Click Same Range if the ranges are identical among all sheets.
  - Click Next to proceed.

4. In Step 3 of 3:
Select Average from the Function drop-down. Optionally, configure row/column labels as needed, then click Finish.

5. A dialog will ask whether you'd like to save the current settings as a scenario for future use. Choose Yes or No based on your needs.

Now, each cell in your defined output range will reflect the average of the corresponding cells from all selected worksheets. This method is especially useful for recurring operations or when consolidating large amounts of structured data quickly.

For users who need to automate averaging cells across multiple worksheets—especially when the sheet names are non-adjacent, frequently change, or when you want to specify ranges at runtime—a VBA macro can be an efficient solution. This method is best for advanced users or workbooks where sheets are often added or renamed.

The following VBA code allows you to input sheet names and cell ranges dynamically, then calculates the average of the specified range across all listed sheets. It's ideal for consolidating values from complex or frequently updated workbooks.

How to set up and use this VBA solution:

1. Go to the Developer tab in Excel. If it's not visible, enable it via File > Options > Customize Ribbon. Click Visual Basic to open the editor. Then go to Insert > Module and paste the following code:

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. To run the macro, press F5 in the editor, or close it and go to Developer > Macros, select AverageAcrossSheets, and click Run.

3. When prompted, enter a comma-separated list of worksheet names (e.g., Sheet1,Sheet3,Summary), then specify the range (e.g., A1:A10).

4. The macro will calculate the total and count from each valid sheet and display the average in a message box.

Parameter notes:

• Sheet names are case-insensitive but must match exactly.
• Range can be a single cell, full column (e.g., B:B), or rectangular range (e.g., D2:E12).
• Invalid or missing sheets will be skipped silently.