How to Reference Another Sheet in Excel

Ever experienced a situation where you needed to retrieve information from another worksheet to your current worksheet or workbook? Well, this case might seem tricky. But it shouldn’t be anymore. Learn how to reference another sheet in excel without lifting a finger. 

Referencing another sheet in excel means creating a link between your current worksheet, and another worksheet or workbook. Through this external reference, you can link a cell or range of cells outside your present sheet.

You stand to save time and put an end to duplication of data when you link worksheets. What’s more, any information change in the external reference is automatically updated in the referenced cell. 

In this tutorial, we teach you the simple basics of how to reference a cell in excel from another sheet.

Internal Referencing

Referencing a cell from another sheet within the same workbook in excel

You're probably wondering how to reference a cell from another sheet in excel? It’s straightforward to retrieve a cell value from a different worksheet in Excel. Let’s assume you have two worksheets Sheet 1 and Sheet 2 in the Sales workbook. Sheet 2 contains employee addresses. However, Sheet 1, we require these addresses to compliment the sales data. 

Method 1: Retrieve a Cell Value using the A-1 Referencing Style

Consider this simple method on how to reference a cell in excel from another sheet. In this case, we need Excel to reference Sheet 2 cell D4.

1. First, type the equal (=) sign in the target cell within your current worksheet.
   
   
   
   
2. Next, click on the other sheet2 tab 
3. Now, select the cell that you desire to link with your current worksheet
4. Since it’s an individual cell, the formula is Sheet_name! Cell_address. However, for a range of cells, the formula is Sheet_name!First_cell:Last_cell
5. Close the formula and press ENTER

Assuming that you are working on Sheet 1 and your desired cell value is located in Sheet 2 cell D4. The final formula for the referenced cell is = Sheet 2!D4

Now, the cell value for the target cell in your current worksheet becomes = Sheet 1!D4.

Excel automatically inserts the right reference.

Tip:  If the sheet name has space, then you must input the single quotation marks. However, if the sheet name lacks space, then it’s not necessary to enclose these quotation marks. 

Method 2: Use a Name Reference to Retrieve a Cell Value 

This method is ideal when referencing a cell from a different worksheet within the same Excel workbook or a different workbook entirely. It requires you to create a name in the source sheet. After that, you can use that name to link the source sheet or workbook with an external sheet or workbook.  

1. Creating your desired name in Excel
   • First, select your desired individual cell or range of cells in your current worksheet.
   • Next, click on the Formulas tab on your spreadsheet.
   • Select Define Name. A New Name dialogue box appears.
   • After that, type a friendly name in the New Name dialogue for instance Sales.
     
     
     
     
   • Now, click OK after typing your desired name.
     Tip: Excel won’t store the name if you fail to press OK. Also, the name should not contain a space. What’s more, ensure the friendly name does not conflict with the spreadsheet’s cell names such as C1. 
2. How to reference a name in another worksheet within the same workbook
   Once you’ve created a name on the workbook level, it’s easy to create external references.

Just type the reference name using the Function formula in the other excel worksheet within the same workbook. 

=Function(name)

If you want to find the sum of all the fruit sales made in January, the formula becomes

=SUM(Sales) 

• Next, click ENTER to apply the formula in the range of cells or individual cells.
  
  

External Referencing

Referencing data from another sheet in excel in a different workbook

Learning how to how to reference data from another sheet in excel from a different workbook is not rocket science. Usually, when referencing data from different worksheet cells within the same workbook, you get the sheet name. However, when referencing different workbooks, the result is the workbook name, worksheet name and the referenced cell. 

Assuming we have two workbooks Employee Sales and Employee Address Report

Now, our goal is to retrieve zip code from the cell D3 Address workbook and reference it in the Employee Sales workbook. Here’s how to reference data from another workbook like a pro:

1. First, open the Employee Sales workbook
2. Next, type the equal (=) sign in the desired cell
3. Open the Address workbook Sheet 1 and select cell D3.
   

   The resulting reference formula for another sheet in excel is =[Employee Address.xlsx]Sheet1!$D$3 

   Does the formula seem complicated? No, it isn't. Let’s take a closer look. 

   Address represents the workbook that we are referring to or referencing. .xlsx stands for the workbook’s extension file format. Sheet 1 is the workbook’s extension name. $D$3 is the cell in question in Sheet 1 of the Address Workbook.

   The cell reference from the Address workbook is shown below.

   
4. Now, close the Employee Address Workbook and open the Employee Sales workbook.
   Can you see the impact of the cell reference from the Address workbook?
   
   
5. Now, hit the ENTER key. Is your desired cell referenced from another workbook like in the image below?
   

 The resulting reference formula is ='[Employee Address.xlsx]Sheet1'!$D$3

Employee Address.xslx is the name of your file. Sheet1 refers to the worksheet name, while $D$3 is the cell reference.

• Tip: $D$3 is an absolute cell reference since it is derived from a specific location within another excel workbook. What's more, the formula is fixed to the particular cell and cannot be changed. However, to copy the formula to other cells, remove the dollar ($) sign to make the cells relative or mixed reference.

 Impressive! Right? 

Now, you can reference another sheet from a different workbook effortlessly.

This example is ideal when referencing an open workbook. What if you want an external reference to a closed workbook?

Referencing a closed workbook

Referencing an excel workbook does not necessarily mean that that workbook must be open. Instead, it's possible to create a link to a closed workbook in Excel. The trick is, to sum up, the whole path that defines your external reference. Check our complete Excel cheat sheet here.

Assuming we have two workbooks January Sales and Total Sales.

 

Total Sales is open, but the January Sales workbook is closed. If you want the total sales, here's how to add up the cell values for January Sales stored in the Documents folder in Drive C: 

• First, type the entire formula in the destination cell of your open Total Sales workbook.

=SUM('C:\Documents\[January Sales.xslx]January Sales'!B2:B6

The external reference parts are broken down as follows:

File Path: This points to the storage location of the external file. In this example, our file is located within the Documents directory in Drive C (C:\Documents)

Workbook Name: It contains the file extension such as .xlsx, .xslm, or .xsl and must be enclosed in square brackets. In this example, the workbook name is [January Sales.xslx]. 

Sheet Name: Normally, the sheet name refers to the referenced cell location. An exclamation mark follows it. In this example, the sheet name is Sheet1'! 

Tip: If the workbook or sheet name contains spaces, the pathname must be enclosed in single quotation marks. For example, 'C:\Documents\[January Sales.xslx]Sheet1'

Cell Reference: This points to the external cell or range of cells being referenced. In the formula, B2: B6 is our range of cells. 

• Next, hit the ENTER key to apply the formula.
• Now, an Updates Values: January Sales.xslx dialogue box opens.
• Type the File Name of the external reference workbook. In this case, it's January Sales.
  
  
• Click OK

 The destination cell value in the open Total Sales workbook is automatically updated as shown below.
        

You have successfully learned how to reference a cell in another sheet in excel. How has the experience been? Now, you have a first-hand experience that referencing another sheet in excel is simple. If you need to link information from a different workbook or worksheet, you now know what to do.