How to Calculate Average among Multiple Different Worksheets in Excel

If we want to calculate the average for all numbers from multiple worksheets, how can we do calculate? Currently we may know the way to calculate the average for a selected range on one worksheet, this article will introduce you a convenient way to calculate the average for all numbers across multiple worksheets.

As the data range on multiple worksheets may be the same or different, so we prepare two examples to do demonstration.

1. Calculate Average for Same Selected Range from Multiple Different Worksheets

First, we need to prepare two worksheets with numbers entered in the same range. See example below.

Worksheet1->Season 1                             Worksheet2->Season 2

Calculate Average among Multiple Different Worksheets 1
Calculate Average among Multiple Different Worksheets 2

a. Select a blank cell for saving the result, in this case we select E2.

Calculate Average among Multiple Different Worksheets 3

b. in E2, enter the following formula:

 =AVERAGE('Season 1:Season 2'!B2:B4)
Calculate Average among Multiple Different Worksheets 4

Notes:

‘Season 1:Season 2’ is the worksheet range. Season 1 and Season 2 are the worksheet names. If you enter ‘sheet1:sheet3’ in this field, that means the worksheet you selected includes sheet1, sheet2 and sheet3, total three adjacent worksheets. And user need to add quotes for ‘Season 1:Season 2’.

‘B2:B4’ is the data range you want to do calculate the average.

c. Click Enter to get the result.

Calculate Average among Multiple Different Worksheets 5

2. Calculate Average for Different Selected Ranges from Multiple Different Worksheets

First, we need to prepare two worksheets with numbers entered in the different ranges. See example below.

Worksheet1->Season 1                             Worksheet2->Season 2

Calculate Average among Multiple Different Worksheets 6
Calculate Average among Multiple Different Worksheets 7

a. This time, in E2, enter the formula:

 =AVERAGE('Season 1'!B2:B6,'Season 2'!B2:B5)

The format is ‘worksheet!data range’.

Calculate Average among Multiple Different Worksheets 8

b. Click Enter to get the result.

Calculate Average among Multiple Different Worksheets 9

3. Video: Calculate Average among Multiple Different Worksheets

This tutorial video will show you a simple yet powerful formula-based method(AVERAGE) to efficiently compute averages across various sheets in Excel 2013/2016/2019/365.

4. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

5. Related Functions

  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….