Table of Contents
To calculate the average of a data set across multiple sheets in Excel, you can use the AVERAGE function. This function allows you to specify the range of cells containing the data you want to include in the calculation, which can span across multiple sheets. Simply select all the relevant cells from each sheet and enter them as arguments in the AVERAGE function. This will give you the average value of the data set across all the selected sheets. You can also use the AVERAGEIF function to calculate the average of a specific criteria across multiple sheets. By using these functions, you can easily and accurately calculate the average of a data set across multiple sheets in Excel.
Average Across Multiple Sheets in Excel
You can use the following basic syntax to average values across multiple sheets in Excel:
=AVERAGE(Sheet1!A1, Sheet2!B5, Sheet3!A12, ...)
The following example shows how to use this syntax in practice.
Example: Average Across Multiple Sheets in Excel
Suppose we have three sheets titled week1, week2, and week3 that each contain data on eight basketball players and their total points scored during that week:
Each sheet has the exact same layout with “Player” in column A and “Points” in column B.
Now suppose we’d like to calculate the average of points scored for each player during each week and display the average in a new sheet called avg:
We can use the following formula to do so:
=AVERAGE(week1!B2, week2!B2, week3!B2)
The following screenshot shows how to use this formula in practice:
The “Average Points” column contains the average of the points scored for each player across week1, week2, and week3.
For example:
- Player A scored an average of 6.67 points across the three weeks.
- Player B scored an average of 6 points across the three weeks.
- Player C scored an average of 7 points across the three weeks.
And so on.
=AVERAGE(week1:week3!B2)
The following screenshot shows how to use this formula in practice:
Notice that the values for the Average Points column match the ones we calculated earlier.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: