Reference Another Sheet in Excel (3 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. We can do this easily. Here we explained those methods.

Here, we introduce a data set of month January of Prices of Fruits in sheet Jan Price. We will refer to this sheet with another sheet Reference Sheet. Here Jan Price is our source sheet and Reference Sheet is our target sheet.


Download Practice Workbook

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


3 Methods to Reference Another Sheet in Excel

1. Reference to another Sheet – Create a Formula

We can create formulas that will reference a cell in a different worksheet from the sheet you are working on.

Step 1:

  • Select the cell where the formula should go. In our Reference Sheet select B3 cell.

Reference sheet of Reference Another Sheet in Excel

Step 2:

  • Press the equal sign (=) (see point 1 on the following image).
  • Then click on the source sheet (see point 2 on the following image).

Process to Reference Another Sheet - Create a Formula

Step 3:

  • We will see the formula on the formula bar (see point 1 on the following image).
  • Now select the cell we want to refer to data. Here we will select cell B4 (see point 2 on the following image).

Formula writing process through Create a Formula method

Step 4:

  • After That, we will see that the formula bar is updated.
  • Then press Enter.

Before going to reference sheet

Step 5:

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

Result of Create a Formula methodNote:

The sheet name will always have an exclamation mark at the end.  This is followed by the cell address.

Sheet_name!Cell_address

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


=Jan!B4

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


='Jan Price'!B4

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

You can now drag that formula across to cells B3 and D6 to reference the values in the corresponding cells in the source worksheet.


Similar Readings:


2. Reference to another Sheet – an Array Formula

We reference another sheet using an Array formula. When we need to refer to a range of data at a glance we will use an array formula.

Step 1:

  • First, select the range in our target sheet Reference2.
  • We select B3 to C6.

Select cells for Array Formula

Step 2:

  • Press the equal sign (see point 1 on the following image).
  • Then click on the source sheet (see point 2 on the following image).

Formula writing for Array Formula

Step 3:

  • We will see the formula on the formula bar.

Formula on Array Formula

Step 4:

  • Now select the cells we want to refer to. Here we will select cells B4 to C7.
  • We will see the formula on the formula bar.

Select the source sheet cells

Step 5:

  • Now press Ctrl+Shift+Enter as it is an array function. And we will refer our data to the target sheet.

Result of Array formula


3. Reference to Another Worksheet – Cell Value 

This method is ideal when referencing a cell/range from a different worksheet within the same Excel. 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.

Step 1:

  • First, select the cell/range from the source data.
  • From the ribbon go to the Formula bar.
  • Click on Defined Names and see a drop-down.
  • From the drop-down, we will get Define Name and a new drop-down will appear.
  • From the last drop-down select Define name.

Process of Cell Value method

Step 2:

  • We will get a Pop-Up.
  • On Name put a name that will be our reference name in the future.
  • Here we put Price as the name and then press OK.

Set name for reference in Cell Value

Step 3:

  • Then go to our target sheet and put a sum and name.
  • The formula becomes,


=SUM(Price)

Use a function to check the Cell Value method

Step 4:

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

Result of Cell Value method


Things to Remember

When using an array, you must press Ctrl+Shift+Enter instead of only Enter. When using the Cell Value method names must be unique.


Conclusion

We discussed in detail the three methods to reference another sheet in Excel. We easily described those methods with datasets and pictures.


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