How to

How to synchronize Microsoft Excel Spreadsheets

How to synchronize Microsoft Excel Spreadsheets

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.

1

Select the cell that you are linking to, click the “Paste” list arrow, then select “Paste Link.”

2

The address where cells are synchronized is displayed in the Formula Bar. It contains the name of the sheet followed by the cell address.

3

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 “=”.

4

Next, select the sheet that contains the cell that you want to link. Reference sheets are shown in the Formula Bar.

5

Finally, click the cell that you want to link. The complete formula is displayed in the Formula Bar. Press the “Enter” key.

6

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.

7

If the Excel workbook that you linked is closed, the formula will show the full path to the file.

8

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.

9

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.

10

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.

=VLOOKUP(A2,Employees!A:D,4,FALSE)

11

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.

Related posts

How to Check Inclusive Languages in Microsoft Word

Howto

How to Use Blending Mode in Photoshop

Howto

How to Change Your Hulu Password

Howto

How to change wallpaper on your Chromebook: Step by step guide

Howto

How to Connect a GameCube Controller to a Nintendo Switch

Howto

How to watch live broadcast Realme X50 Pro 5G

Howto

How to Access PC from Cellphone or Tablet Remotely

Howto

How to find your Microsoft Office product key

Howto

How to Mute Someone on Facebook

Howto