Hosne Ara
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.
On the other hand, the destination dataset (Sheet2) does not have the total amount mentioned.
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
- 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.
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.
- Next, go to Sheet1 and select the range D5:D11.
- Now, hit Enter.
- 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.
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.
- Now, go to Sheet1 and select the range D5:D11, and hit Enter.
- Consequently, the data of Sheet2 is linked to Sheet1.
Read More: How to Link Cell to Another Sheet in Excel (7 Methods)
Similar Readings
- How to Link Files in Excel (5 Different Approaches)
- Link Excel Workbooks for Automatic Update (5 Methods)
- How to Link Word Document to Excel (2 Easy Methods)
- Link Excel Workbooks (4 Effective Methods)
- How to Link Two Workbooks in Excel (5 Methods)
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.
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).
- Then, go to Sheet2 and right-click on Cell C5, and click on the Paste Link option (shown in the below screenshot).
- As a consequence, you will get the Total Population amount in Sheet2 which means data is linked between two sheets.
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.
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.
- Now go to Sheet1 and select the range B5:B11 and hit Enter.
- 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!CellReferenceRemember, 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’.
But, our destination worksheet (Destination) is from another 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
- 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.
