Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

How to Reference Another Sheet in Excel


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).

Directly Referencing from One Sheet to Another Sheet in Excel

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.

Note: Here, the sheet name will always have an exclamation mark (!) at the end.  This is followed by the cell address. So, the format will be as Sheet_name!Cell_address.

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

=Jan!B5

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

='Jan Price'!B5

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.

Use Fill Handle Icon to Reference Another Sheet

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

Reference Another Sheet

Read More: Excel VBA: Cell Reference in Another Sheet (4 Methods)


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).

Reference to Another Sheet Using an Array Formula in Excel

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.

Read More: How to Use Cell References in Excel Formula (All Possible Ways)


Similar Readings


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.

Employing Define Name Feature to Refer to Another Worksheet in Excel

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,
=SUM(Price)
  • 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.

Use of INDIRECT Function for Calling Another Sheet in Excel

  • 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.
=INDIRECT("'" & $C$10& "'!B5")
  • Thirdly, press ENTER to get the output.

Formula Breakdown

  • First, $C$10 returns the value of the C10 cell. Which is Jan Price. Actually, the Dollar sign will freeze the C10 position for other cells too.
  • Here, the Inverted Comma is required for using any text or sign.
  • Lastly, the Ampersand sign (&) will join all these terms.
  • Thurs, INDIRECT(“‘” & $C$10& “‘!B5”)—> becomes
    • INDIRECT(‘Jan Price’!B5). Which will return the value of B5 from the Jan Price sheet.

  • Similarly, write the corresponding formula in the C5 cell.
=INDIRECT("'" & $C$10& "'!C5")
  • Then, press ENTER.

Reference Another Sheet in Excel Using INDIRECT Function

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.
=INDIRECT("'"&$C$10&"'!" &ADDRESS(ROW(B5),COLUMN(B5)))
  • Thirdly, press ENTER to get the result.

Combine INDIRECT, ADDRESS, ROW & COLUMN Functions in Excel

Formula Breakdown

  • ROW(B5)→ returns the row number of the cell B5.
    • Output→ 5.
  • COLUMN(B5)→ returns the column number of the cell B5.
    • Output→ 2.
  • ADDRESS(ROW(B5),COLUMN(B5)) becomes ADDRESS(5,2).
    • Output→ $B$5.
  • Lastly, the Ampersand sign (&) will join all these terms.
  • INDIRECT(“‘”&$C$10&”‘!” &ADDRESS(ROW(B5),COLUMN(B5))) becomes INDIRECT(“‘”&$C$10&”‘!” &$B$5)INDIRECT(‘JanPrice’!$B$5).
    • Output→ Papaya.

  • 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.

Read More: How to Use Reference of Worksheets in Excel (5 Examples)


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to Reference Another Sheet


Conclusion

We discussed in detail the five methods to reference another sheet in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Further Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo