How to Reference Another Sheet in Excel

Cell referencing, also known as 3-D referencing, is a convenient method to link contents in two or more sheets. For instance, you could perform calculations in one sheet and then present them on the next sheet by referencing the cells. Professionals mainly benefit from this feature of linking spreadsheets.

If you’re interested in learning more about 3-D referencing, we’ve got you covered. This article will discuss how you can refer cells in two sheets, what you can expect while referencing, and a bonus tip in the end, so keep reading!

What to Expect While Referencing Sheets?

When you reference cells between two sheets, Excel copies the content of the referred cell to the cell with the formula. You cannot change the cell content in the destination cell; all you can edit is the reference formula. 

If you choose to remove the formula in total, nothing changes in the actual cell. However, if you remove the content from the referred cell, the content is also deleted on the other sheet. This also means that 3-D referencing is dynamic in nature. 

Furthermore, when you reference the cell, you’re referencing the content of the cell. So, if you move the cell content to a different cell location, Excel will automatically change the cell reference in the formula.

How to Reference Another Sheet in Excel?

To reference another sheet in Excel, you need to enter a formula in the cell that includes the sheet name and either the cell or the cell range. In addition to referencing a cell’s content, you can also pass a cell from another cell as an argument in Excel formulas. 

In this section, we have included both of these methods of referencing cells; as a cell content and as an argument.

Refer Cells From Another Sheet

If you want to create a copy of the cell contents from one cell to another, you can do it by entering a simple formula. Here are the steps you can follow to refer to another sheet in Excel:

1. Launch Excel and open your workbook with your sheets.
2. On the cell you wish to refer cells from another sheet, enter the formula in this format:
   • For a single cell: (Sheetname)!(cell location)
   • For cell range: (Sheetname)!(start cell):(end cell)
     
3. Hit Enter on your keyboard.
4. If you referred a range, use Flash Fill to fill the corresponding cells with the referred value.
   

Pass Cell From Another Cell As Argument

Certain library functions offered by Excel allow you to refer to a cell from a different sheet to pass as an argument. If you wish to further manipulate the content of the referred cells from a different sheet using a supported function, follow these steps:

1. Open your workbook with your sheets.
2. Select the Sheet you wish to enter the formula with the referred cell.
3. On an empty cell, enter the equals (=) sign.
   
4. Enter your formula and then insert an open parentheses ‘(’ sign.
   
5. Head to the sheet with the cell you wish to refer, and hit Shift on your keyboard.
6. Select the cell you wish to refer.
   

How Can I Make Referencing Easier?

At times, referencing can become tricky because of the rookie errors we sometimes make. When referencing another sheet, you need to ensure that the sheet name and the cells you’re referring to are entered correctly. To avoid mistakes, you can define your Sheet and the cell range with a name.

Name Your Sheet

Excel, by default, names your sheets as Sheet1, Sheet2, and so on. This naming can get confusing when you have many sheets open. While referencing your cell, rename your sheet to something relevant so that you can visit the referred sheet later.

Refer to the following steps to name your sheet in an Excel workbook:

1. Open your Excel file.
2. Right-click on your sheet at the bottom of the Excel program.
3. Choose Rename.
   
4. Enter a name for your worksheet.

Define a Range

Defining your cell range leaves less room for incorrectly referencing your cells. If you incorrectly enter either one of your beginning or ending cells, the value of the entire range will be affected. If you wish to eliminate this issue, follow these steps to define a range in Excel following these steps:

1. Open your Excel workbook.
2. Select the range you wish to refer to on the next sheet.
3. Head to the Formulas tab on the menubar.
4. Select Name Manager.
   
5. On the window, click on New.
   
6. Enter the following details on the New Name Window:
   • Name: Give your range a name.
   • Scope: Select Workbook.
   • Comment: If you wish to note down something else about this range, you can do so in this section.
   • Refers to: Confirm if the cells you wish to define are selected. If not, select the icon next to it, then select the cells you wish to name.
     
7. Click OK.