How to Link Data from One Spreadsheet to Another in Excel

In this tutorial, I will discuss how to link MS Excel data from one spreadsheet to another. We can easily copy data from one sheet to another sheet of the same workbook or another workbook. However, while working with big data, the usual copy-paste option is not that effective. This is because, if you change any data in one spreadsheet, you will have to change it in the other sheet too. But, we can solve the above-mentioned problem by linking one spreadsheet data to another. Besides that, this linking reduces errors in data and saves time. So, let’s explore the methods of linking different worksheets.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


6 Methods to Link Data From One Spreadsheet to Another

1. Link Spreadsheet Cells Data with ‘!’ Sign

We can link two spreadsheets in the same workbook using the exclamatory ‘!’ sign. This is a very easy and quick option. For example, my source dataset (Sheet1) contains total monthly sales of different food items.

Link Spreadsheet Cells Data with ‘!’ Sign

On the other hand, the destination dataset (Sheet2) does not have the total amount mentioned.

Link Spreadsheet Cells Data with ‘!’ Sign

So, now I will ‘!’ sign to link data from Sheet1 and Sheet2. Let’s have a look at the linking process.

Steps:

  • First, go to Sheet2 and write the below formula in Cell C5:
=Sheet1!C12

Link Spreadsheet Cells Data with ‘!’ Sign

  • Next, press Enter and you will see the Total Sales amount ($11,000) of Sheet1 in Sheet2. Here, cell data of Sheet2 is linked to cell data of Sheet1, and the link formula is created with a Relative Cell Reference.

Read more: How to Link Two Sheets in Excel (3 Ways)


2. Apply Equal (=) Sign to Link One to Another

Now, we will use the equal ‘=’ sign to link different spreadsheet data ranges. This is also an easy way to link one worksheet data to another. For example, in Sheet1, we have monthly sales data of different food items listed.

However, in Sheet2, sales data for the month of Feb is missing.

Apply Equal (=) Sign to Link One Spreadsheet Data Range to Another

Now, we will link the data of Feb from Sheet1 to Sheet2.

Steps:

  • Go to Sheet2 and write ‘=’ in Cell D5 but not press Enter.

Apply Equal (=) Sign to Link One Spreadsheet Data Range to Another

  • Next, go to Sheet1 and select the range D5:D11.
  • Now, hit Enter.

Apply Equal (=) Sign to Link One Spreadsheet Data Range to Another

  • As a result, excel will direct you to the destination spreadsheet (Sheet2) and you will find the Feb sales data in Sheet2.

Read More: How to Link Excel Sheets to Another Sheet (5 Ways)


3. Use Plus(+) Symbol to Link Data from Different Spreadsheet

Similar to the previous two methods, now we will use a plus (+) sign to link one spreadsheet data to another. For instance, in Sheet1 we have a dataset containing US states, abbreviations, and regarding largest cities.

But, in Sheet2 the Largest City data range is missing.

Use Plus(+) Symbol to Link Data from Different Spreadsheet

So, now we will link these two sheets to each other.

Steps:

  • First, go to Sheet2 and type ‘+’ in Cell D5, but not hit Enter.

Use Plus(+) Symbol to Link Data from Different Spreadsheet

  • Now, go to Sheet1 and select the range D5:D11, and hit Enter.

Use Plus(+) Symbol to Link Data from Different Spreadsheet

  • Consequently, the data of Sheet2 is linked to Sheet1.

Read More: How to Link Cell to Another Sheet in Excel (7 Methods)


Similar Readings


4. Link Spreadsheet Data Using Paste Link Option

In this method, we will link one worksheet data to another using the Paste Link option in excel. For example, in Sheet1, we have a dataset containing several US states’ Total Population.

Unlikely, Sheet2 does not contain the amount of Total Population.

Link Spreadsheet Data Using Paste Link Option

Now, we will link the above spreadsheets to each other.

Steps:

  • First, go to Sheet1 and copy the data of Cell C5 (Press Ctrl + C to copy the range).

Link Spreadsheet Data Using Paste Link Option

  • Then, go to Sheet2 and right-click on Cell C5, and click on the Paste Link option (shown in the below screenshot).

Link Spreadsheet Data Using Paste Link Option

  • As a consequence, you will get the Total Population amount in Sheet2 which means data is linked between two sheets.

Link Spreadsheet Data Using Paste Link Option

Here, excel created a link with Absolute Cell Reference.

Note:

You can use the Paste Link option following the path Home > Paste > Paste Link.

Read More: How to Link Sheets to a Master Sheet in Excel (5 Ways)


5. Link  One Spreadsheet Data to Another Using Excel Function

We can apply excel functions to link two spreadsheet data. For example, we have monthly sales data in Sheet1.

In Sheet2 we will calculate the total sales of food items.

Link  One Spreadsheet Data to Another Using Excel Function

Subsequently, if we want to get the summation of sales of the Jan we will follow the below steps.

Steps:

  • Go to Sheet2 and start to type the SUM function formula in Cell C4 as below screenshot. Do not type the argument of the function.

Link  One Spreadsheet Data to Another Using Excel Function

  • Now go to Sheet1 and select the range B5:B11 and hit Enter.

Link  One Spreadsheet Data to Another Using Excel Function

  • As a result, excel will take us to Sheet2 and return the Total Sales.

Read More: How to Link Sheets in Excel with a Formula (4 Methods)


6. Link Spreadsheet Data from Different Workbook

Till now, we have linked spreadsheets that are placed in a single workbook. Now, we will link spreadsheets from different workbooks. In this method, I will use the below generic formula:

=[BookName.xlsx]SheetName!CellReference

Remember, you have to put the workbook name in square brackets ([ ]) to indicate external references. For example, our source workbook (Data.xlsx) contains sales data of different fruits in the sheet ‘Fruits’.

Link Spreadsheet Data from Different Workbook

But, our destination worksheet (Destination) is from another workbook.

Link Spreadsheet Data from Different Workbook

Now, we will link the above sheets following the steps below.

Steps:

  • Go to the sheet ‘Destination’ and type the below formula in Cell B5.
=[Data.xlsx]Fruits!B5:C11

Link Spreadsheet Data from Different Workbook

  • As a result, you will get all the sales data in the destination worksheet from the source sheet.

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

Note:

➤ This formula will work when both workbooks are from the same folder. If workbooks are from different folders, then you have to provide the file path in the formula.

➤ You can link sheets from different workbooks using functions too. In that case, follow the below formula.

=SUM([WorkbookName.xlsx]SheetNamel!CellReference)

This formula too will work when both workbooks are from the same folder.


Conclusion

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Further Readings

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo