In this tutorial you will learn how you can use reference formulas with multiple excel work sheets. With reference formula you can use multiple values of multiple work sheets in excel. It is very useful for the calculation of data which is on different work sheet of one work book of excel. Reference formula is write as following.

=Sheet1Name!CellColumnCellRow+Sheet2Name!CellColumnCellRow

In function it is same as following.

=SUM(Sheet1Name!CellColumnCellRow+Sheet2Name!CellColumnCellRow)

By using the reference formulas work become more easier and time saving. Through it you can use the values of other sheets in the sheet in which you required.

reference formulas with multiple excel work sheets example:-

To use reference formula in multiple sheets of work book of excel create a work book. Which has twelve marks sheets of students of a class tests. One work sheet has data of one month. Open Microsoft excel and select sheet tab. Now right click of the mouse on the sheet tab and you see like as following.

rename-sheet

Click on Rename option and type January then press enter key.

sheet-1-as-junary

Add new sheet is work book of excel by clicking on + sign which is at the right side of January tab.

reference formulas with multiple excel work sheets

Now rename Sheet2 as February and appearance become as following.

sheet2-as-february

Now insert sheets of names March, April, May, June, July, August, September, October, November, December and having data of students as like following.

yearly-work-book-of-students-of-monthly-lass-test-resuls

Insert data in all sheet of work book in microsoft excel or download it from here. You need to calculate the average marks of student in a year and the obtain marks of students are in different sheets. Insert a new sheet and give is name Average Marks as like following.

average-marks-reference formulas with multiple excel work sheets

Type fields in Average Mark sheets as like following.

average-marks-sheet

In cell B2 type average function like as following.

=AVERAGE(January!H2,February!H2,March!H2,April!H2,May!H2,June!H2,July!H2,August!H2,September!H2,October!H2,November!H2,December!H2).

average-obtained-marks-of-year

After press enter key function apply and result appear in B2 as same like following.

average-obtained-marks-of-john

Now copy and paste the formula of cell B2 in cell B3, B4 and B5 same as following sheet.

reference formulas with multiple excel work sheets

Hence you have leaned what is reference formula and why to use it. Also you learned how it is use.

 

 

 

 

 

Please follow and like us:

ali hiader

Leave a Reply

Your email address will not be published. Required fields are marked *