How to Reference a Cell from a Different Worksheet in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents Expand

How to Reference a Cell from a Different Worksheet in Excel: Step-by-Step Procedures

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.

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-

='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.

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-

='[Practice File.xlsx]Menu Order'!$G$18

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.

Download Practice Workbook

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

Reference a Cell from a Different Worksheet.xlsx

Practice File.xlsx

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.

<< Go Back to Reference to Another Sheet | Cell Reference in Excel | Excel Formulas | Learn Excel