How to Create Reference with Another Workbook in Excel (5 Ways)

We use Excel basically to ease our workload and save work time. For this, sometimes we refer to another cell in an Excel sheet or another sheet of an Excel workbook. We create references so that we don’t have to assign the data one more time. And sometimes we may also need to create a reference between our current workbook and another workbook in Excel. In this article, we will show you how to create reference with another workbook in Excel.


Download Practice Workbook

You can download the practice book from the link below.


5 Ways to Create Reference with Another Workbook in Excel

Let’s say, we have got a source workbook and in it, we have a dataset of different salesmen of an organization and their respective sales in three consecutive months and the total sales of these three months.

Source Workbook to Create Reference with Another Workbook

We want to create a reference between this source workbook and our new working book. For this, we will refer to the Total Sales range of the Source workbook in our current workbook.

In this section, you will find 5 suitable and effective ways to reference another workbook in Excel. We will demonstrate them one by one here. Let’s check them now!


1. Using Paste Link Option to Refer Another Workbook

We will create a reference between our source workbook and our current workbook. We will use the Paste Link option in order to serve our purpose. Let’s follow the steps below to refer to a different workbook.

Steps:

  • First of all, copy the range of the source workbook (i.e. Total Sales).

  • Now, get back to the new workbook and right-click on the mouse> click the Paste Link option.

Use Paste Link to Create Reference with Another Workbook

  • Hence, the command will create a reference to the previous worksheet.

Read More: Reference from Another Excel Workbook without Opening (5 Examples)


2. Create Reference with Another Workbook by Switching Between Them

We will now refer to another workbook by switching between the workbooks. In order to do so, follow the steps below.

Steps:

  • Firstly, select a cell on your current workbook and put a “=” sign on it.

  • Then, switch to the source workbook and click on the cell you want to refer (i.e. F5).

  • Now, get back to the current workbook and you will see that it is referring to the source workbook.

Switching between workbook to create refrence

  • After that, press ENTER, and the reference with the cell of the source workbook will be created.

Note: This method will create an absolute reference to the cell of the other workbook.

  • Here, remove the dollar sign to be unable Absolute reference.
  • After that, drag the Fill Handle tool to Autofill the formula down to the next cells.

Drag formula

  • Hence, your current workbook will create a reference with the source workbook.

Switch Between Excel Workbooks to Create Reference

Read More: How to Link Excel Workbooks (4 Effective Methods)


Similar Readings


3. Type Formula Manually to Create Reference

In this method, we will type the formula in the current workbook manually to create a reference with another workbook. In order to do so, just proceed with the steps below.

Steps:

  • First, select a cell in the current workbook and type the formula below.

='[Source Workbook.xlsx]Data'!F5

Here,

  • F5= the cell of the Source workbook

Type Manually to Create Reference with Another Workbook in Excel

  • Then, press ENTER, and the cell of the current book will refer to the cell of the source workbook.

  • Now, drag the formula down for the next cells to refer to the source data.

Manually Types Formula to Crate Reference with Another Workbook in Excel

Read More: How to Link Two Workbooks in Excel (5 Methods)


4. Refer Another Workbook with Named Range

We will now create a reference with another workbook by using the named range. So, let’s start the process like the below.

Steps:

  • First of all, go to the Source workbook that you want to refer.
  • Now, select the range you want to refer> go to the Formulas tab> select Define Name in the Defined Names group> click Define Name.

Define Name to Create Reference with Another Workbook in Excel

  • Then, the New Names dialogue box will appear. Assign a name (i.e. Total_sales) in the Name field and click OK.

  • Then, get back to the new workbook and apply the following formula to a selected cell.

='[Source Workbook.xlsx]Data'!F5

Here,

  • F5= the first cell of the Source workbook

Type Formula to Refer Another Workbook

  • Now, hit ENTER, and the range that you assigned name will appear here.

In this way, you can create a reference to another workbook.

Read More: How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)


5. Using Consolidate Tool

You can also create a reference to another workbook by using the Consolidate tool. For the same set of data, we want to sum the total sales of the source workbook in the new workbook. if you want to apply this tool to refer to a new workbook, proceed with the steps below.

Steps:

  • Firstly, select a cell of your current workbook> go to the Data tab> click Consolidate from the Data Tools group.

Consolidate tool to create reference with another workbook in Excel

  • Now, Consolidate dialogue box will appear. Assign the first range in the Reference field> click Add.

  • Then, click the right arrow of the Reference field to add another reference range> click Add.

  • Repeat the same process for the as mane reference as you want to add and then click OK.

  • As a result, your current workbook will create a reference with the source workbook.

Consolidation to Create Reference with Another Workbook in Excel

Read More: Link Excel Workbooks for Automatic Update (5 Methods)


Things to Remember

  • Make sure that Calculation Options is set to Automatic, otherwise, the formulas may not work properly.
  • Keep both the workbook open during the process.
  • You can refer to multiple workbooks by following the methods.
  • Don’t forget to put the ‘ ’ sign when you type the formula manually.

Conclusion

In this article, we have tried to show you some methods of how to create reference with another workbook in Excel. Hope this article has shed some light on your way to create reference with a different workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly check our website. Have a great day!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo