How to Synchronize Spreadsheets in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Synchronizing

Excel has two ways to synchronize data, both between separate spreadsheets (by linking files) and between worksheets in the same sheet. This is useful for building summary spreadsheets and for ensuring that changes in one worksheet are reflected (and replicated) in others.

Advertisement

Linking Separate Excel Workbooks Together

Video of the Day

Step 1

Open the workbook with the data you expect to be shared in Excel. This is the source workbook.

Video of the Day

Step 2

Open the workbook you want the data to be replicated in. This is the destination workbook.

Advertisement

Step 3

Select the cell in the source workbook you want the data to come from, and enter "Ctrl+C" to copy it.

Step 4

Switch to the target workbook, and select the cell you want the data to appear in.

Advertisement

Step 5

Enter "Ctrl+V" to paste the reference in. If you examine the formula, you'll see that prior to the row and column entry, there will be a reference to the drive location (or network location) and the file name of the source workbook. An example would look like this: [C:\Book1.xlsx]Sheet1!A1. This means that Excel will pull the value from the linked file.

Advertisement

Advertisement

Synchronizing Worksheets In The Same Workbook

Step 1

Open your workbook in Excel.

Step 2

Hold down the "Ctrl" key while clicking on multiple worksheet tabs. Be sure to leave one tab unselected when you do this. This will ensure that any edits you make on one tab will be replicated across all selected tabs, with data entered in the corresponding cells.

Advertisement

Step 3

Click the tab that's not part of the group to undo the tab group selection. This will get Excel to revert to "normal mode," where changes in one tab only affect that tab.

Advertisement

Advertisement

references