How to Reference a Cell from a Different Worksheet in Excel

Before understanding this topic, you have to understand cell references in a formula. Say I have a formula like this ‘=B3*C3+D3’. In this formula there are three cell references, they are B3, C3, and D3. Every reference in a formula links with a cell in an excel worksheet. In other words, every cell has a unique reference. B3 can link to only one cell in the worksheet. You can use the cell reference of one worksheet in the same worksheet or with another worksheet. So today we are going to show you some easy steps to reference a cell from a different worksheet in Excel.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Easy Steps to Reference a Cell from a Different Worksheet in Excel

Using a real-time example, we shall make clear to you how to use cell references with multiple worksheets. In our example, we have two worksheets, they are ‘Menu Order’ and ‘Total Cost’. We shall use the cell reference of worksheet ‘Menu Order’ with a cell in our other worksheet ‘Total Cost’.

It’s the ‘Total Cost’ sheet.

Step 1: Select a Cell to Insert Cell Reference

• Firstly type = in Cell C5 of the sheet ‘Total Cost’.
• Next, click on the other sheet name- ‘Menu Order’. It will take you to that sheet.

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

Step 2: Choose the Cell from Another Sheet

• Now select the cell that’s cell reference you want to use. We clicked Cell G18.
• Finally, just hit the ENTER button.

After that, the formula will look like this-

Also, you can insert the whole formula by writing it instead of selecting.

NOTE: If a worksheet name contains space, then you have to use single quotation marks(”) around the name, otherwise use the name direct in the formula.

Here’s the value of that cell.

Attention 1: If you rename your worksheet name later, the formula will be updated automatically with the new name of the worksheet.

Attention 2: If you type the worksheet name wrongly, then a #REF! error will be visible. Click the yellow error button to edit the worksheet name with the ‘Edit in Formula Bar‘ option.

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

Similar Readings

• Creating and Copying Formula Using Relative References in Excel
• What Is and How to Do Absolute Cell Reference in Excel?
• Relative Cell Reference in Excel (Detailed Analysis)
• Excel VBA: R1C1 Formula with Variable (3 Examples)
• How to Reference Text in Another Cell in Excel (14 Ways)

How to Reference a Cell from Another Excel File

Now we’ll learn to use a cell reference from another file in Excel which means the sheet will remain in another different worksheet. And of course, it’s not so difficult, we’ll have to add the file name and file path in the formula just. For that, we modified the dataset and placed the sheet ‘Menu Order’ in a new workbook named- Practice File.

1. Reference a Cell from an Open File

First, we’ll show how to reference a cell if the worksheet is remain open. If the file remains open then we’ll need the file name just, no need for the file path.

Steps:

• First, in Cell C5 of the sheet ‘Total Cost’ and type =.
• Later, navigate to your another file. We’ll go to the ‘Practice File’ sheet for our example.

• Then from the Menu Order sheet, select Cell G18 and press the ENTER button.

• The final formula will look like this-

Here also, we can insert the whole formula by writing it instead of selecting.

Now see, the value of the reference is now added to our workbook.

Let’s change one value from that file. We inserted 100 in Cell G14.

Have a look, here the formula is updated automatically.

2. Reference a Cell from a Closed File

To reference a cell from a different closed workbook, we’ll have to just add the file path with the previous formula. Look at the image below, we added the file path before the file name of the previous formula.

That’s it. Rest is done by Excel itself to fetch the value from the cell reference we have made from the sheet of the different workbook.

Read More: Excel VBA: Get Cell Value from Another Workbook without Opening

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to reference a cell from a different worksheet in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.

Related Articles

• Excel VBA Examples with Cell Reference by Row and Column Number
• How to Find and Replace Cell Reference in Excel Formula
• Different Types of Cell References in Excel (With Examples)
• [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
• Reference Another Sheet in Excel (3 Methods)