How to Synchronize Spreadsheets in Excel

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.

...
Synchronizing

Linking Separate Excel Workbooks Together

Step

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

Step

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

Step

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

Step

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

Step

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.

Synchronizing Worksheets In The Same Workbook

Step

Open your workbook in Excel.

Step

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.

Step

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.