How to Reference to Another Sheet in Excel (5 Suitable Methods)

We mostly work with data in Excel. When calculating data in Excel, we often find situations where we need to pull data from one worksheet to another worksheet in the same Excel file. Actually, we can use the reference to another sheet in Excel easily. Here, we explained those methods.
Furthermore, for conducting the session, we will use Microsoft 365 version.

Download Practice Workbook

Download this practice sheet to exercise while you are reading this article.

5 Suitable Methods to Reference to Another Sheet in Excel

Here, we introduce a data set on the month of January of Prices of Fruits in a sheet named Jan Price. Now, we will refer to this sheet with another sheet. Here, Jan Price is our source sheet.

1. Directly Referencing from One Sheet to Another Sheet

Here, we can refer directly from one sheet to another sheet in Excel. Furthermore, we can create formulas that will reference a cell in a different worksheet from the sheet you are working on.

📌 Steps:

• Firstly, select the cell where the formula should go. Here, we are going to use the sheet named Reference Sheet and select Cell B5.
• Secondly, press the equal sign (=).
• Thirdly, click on the source sheet (Jan Price).

Here, we will see the formula on the formula bar.

• Now select the cell we want to refer to data. Here we will select Cell B5.

After that, we will see that the formula bar is updated.

• Subsequently, press ENTER.
• Finally, we will see that we are on our target sheet with desired data.

If the source data sheet is named Jan, it will be:

As our source sheet name contains spaces, then the reference to the sheet will appear in single quotes.

If you change the value in the source sheet, then the value of this cell will also change.

• Then, you can now drag the Fill Handle icon horizontally to cells B5 and C5 to reference the values in the corresponding cells in the source worksheet.

• Lastly, again drag the Fill Handle icon to paste the used formula respectively to the other cells of the columns.

Finally, you will see all the values from the Jan Price sheet.

2. Reference to Another Sheet Using an Array Formula

When we need to refer to a range of data at a glance then we will use an array formula. So, in this section, we will use the array formula along with referencing sheet.

📌 Steps:

• First, select a cell in our target sheet Reference Sheet- Array.
• Then, press the equal sign (=).
• After that, click on the source sheet (Jan Price).

Also, we will see the formula on the formula bar.

• Now, select the cells we want to refer to. Here, we will select cells B4 to C8.

• Then, press ENTER, and we will refer our data to the target sheet.

3. Employing Define Name Feature to Refer to Another Worksheet

This method is ideal when referencing a cell/range from a different worksheet within the same Excel workbook. Moreover, it requires creating a name in the source sheet. After that, we can use that name to link the source sheet to our target sheet. Suppose we want to find out the total prices for January.

📌 Steps:

• First, select the range from the source data.
• Then, from the Top Ribbon >> go to the Formulas bar >> click on Defined Names and see a drop-down.
• Then, from the drop-down >> we will get Define Name and a new drop-down will appear.
• Lastly, from the last drop-down select Define Name.

As a result, we will get a Pop-Up named New Name.

• Then, on the Name box put a name that will be our reference name in the future. Here, we put Price as the name.
• After that, press OK.

Here, the names must be unique.

• Then go to your target sheet and use the SUM function.
• In the SUM function when you write “Pr” then you will find some options. So, choose Price.

• Actually, the formula is,

• After pressing ENTER we will get the sum of the selected range.

4. Use of INDIRECT Function for Calling Another Sheet in Excel

You can apply the INDIRECT function to use a reference of another sheet in Excel. Now, follow the steps given below.

📌 Steps:

• Firstly, you must write the sheet name in a cell. Here, we have written that in a new cell C10.

• Now, you must select another cell B5 where you want to keep the Fruits.
• Secondly, you should use the formula given below in the B5 cell.

• Thirdly, press ENTER to get the output.

• Similarly, write the corresponding formula in the C5 cell.

• Then, press ENTER.

Here, if you notice then you will see that we have to change manually the corresponding cell containing Fruit or Price. But the sheet name is fixed for all the cells. Thus, we used the Dollar sign ($) here.

• So, write the same formula changing the cell reference for the other cells.

Finally, you will get all the cell values.

5. Combine INDIRECT, ADDRESS, ROW & COLUMN Functions

Here, you can use a combination of some Excel functions for referencing another sheet in Excel. Basically, we will use the INDIRECT, ADDRESS, ROW, and COLUMN functions. So, let’s see the steps.

📌 Steps:

• Firstly, you must write the sheet name in a cell. Here, we have written that in a new cell C10.
• Secondly, you should use the formula given below in a blank cell B5.

• Thirdly, press ENTER to get the result.

• Then, you can now drag the Fill Handle icon horizontally to cells B5 and C5.

• Lastly, again drag the Fill Handle icon to paste the used formula respectively to the other cells of the columns.

Practice Section

Now, you can practice the explained method by yourself.

Conclusion

We discussed in detail the five methods to reference another sheet in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Reference to Another Sheet: Knowledge Hub

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel