You can synchronize Microsoft Excel spreadsheets to ensure that changes in one will automatically be reflected in the other. It is possible to create links between different worksheets as well as separate Excel workbooks. Let’s look at three ways to do this.
Synchronize Excel Spreadsheets Using the Link Paste Feature
Paste Link functionality in Excel provides a simple way to synchronize Excel spreadsheets. In this example, we want to create a summary sheet of total sales from several different worksheets.
Start by opening your Excel spreadsheet, clicking the cell that you want to link, and then selecting the “Copy” button on the “Home” tab.
Select the cell that you are linking to, click the “Paste” list arrow, then select “Paste Link.”
The address where cells are synchronized is displayed in the Formula Bar. It contains the name of the sheet followed by the cell address.
Synchronize Excel Spreadsheets Using Formulas
Another approach is to create your own formula without using the Paste Link button.
Synchronize Cells on Different Worksheets
First, click the cell where you created the link and type “=”.
Next, select the sheet that contains the cell that you want to link. Reference sheets are shown in the Formula Bar.
Finally, click the cell that you want to link. The complete formula is displayed in the Formula Bar. Press the “Enter” key.
Synchronize Cells in Separate Workbooks
You can also link to cells in a completely different workbook. To do this, you must first ensure that another workbook is open before you start the formula.
Click the cell that you want to link to and type “=”. Switch to another workbook, select the sheet, then click cells to link. The workbook name precedes the sheet name in the formula bar.
If the Excel workbook that you linked is closed, the formula will show the full path to the file.
And when a workbook containing a link to another workbook is opened, you might be greeted with a message to enable link updates. This depends on your security settings.
Click “Enable Content” to ensure that updates in other workbooks are automatically reflected in the current one.
Synchronize Excel Spreadsheets Using the Search Function
The previous method that synchronized two sheets or workbooks using links to specific cells. Sometimes, this might not be good enough because the link will return incorrect values if the data is sorted and moved to a different cell. In this scenario, using the search function is a good approach.
There are many search functions, but the most commonly used is VLOOKUP, so let’s use that.
In this example, we have a simple list of employee data.
On another worksheet, we store training data about employees. We want to find and return the age of employees to be analyzed.
This function requires four pieces of information: what to look for, where to look, column numbers with values to return, and what type of lookup you need.
The following VLOOKUP formula is used.
A2 contains the employee ID to look for the Employee sheet in range A: D. Column 4 of that range contains the age to return. And False determines the exact lookup on the ID.
The method you choose to synchronize an Excel spreadsheet together is largely determined by how your data is organized and how it is used.