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

**Table of Contents**hide

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

**Note:**Here, the sheet name will always have an

**exclamation**mark (

**!**) at the end. This is followed by the

**. So, the format will be as**

*cell address***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.

Finally, you will see all the values from the **Jan Price** 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**).

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**

**Mixed Cell Reference in Excel (4 Examples)****Cell Reference in Excel VBA (8 Examples)****Difference Between Absolute and Relative Reference in Excel****Different Types of Cell References in Excel (With Examples)****How to Reference Text in Another Cell in Excel (14 Ways)**

### 3. Employing Define Name Feature to Refer to Another Worksheet

This method is ideal when referencing a ** cell**/

**from a different**

*range***within the same Excel workbook. Moreover, it requires creating a name in the**

*worksheet***sheet. After that, we can use that name to link the**

*source***sheet to our**

*source***sheet. Suppose we want to find out the total prices for**

*target***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,

`=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**.

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

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.

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

## 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

**Excel VBA Examples with Cell Reference by Row and Column Number****How to Use Sheet Name in Dynamic Formula in Excel****[Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)****How to Use Cell Value as Worksheet Name in Formula Reference in Excel****Excel VBA: Get Cell Value from Another Workbook without Opening****Relative and Absolute Cell Address in the Spreadsheet**