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

How to Reference a Cell from a Different Worksheet in Excel

It’s the ‘Total Cost’ sheet.

How to Reference a Cell from a Different Worksheet in Excel


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.

Select a Cell to Insert Cell Reference from Another Sheet


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.

Choose a Cell to Insert Cell Reference from Another Sheet

After that, the formula will look like this-

='Menu Order'!G18

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.


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.

How to Reference a Cell from Another Excel 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.

Reference a Cell from Another Open Excel File

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

Select a Cell from Another Open Excel File

  • The final formula will look like this-
='[Practice File.xlsx]Menu Order'!$G$18

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

Formula of Referencing a Cell from Another Open Excel File

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

Output of Referencing a Cell from Another Open Excel File

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.


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.


Read More…

How to use cell references with multiple worksheets in Excel

Excel Reference Cell In Another Sheet Dynamically

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo