Excel has two ways to synchronise 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.
Open the workbook with the data you expect to be shared in Excel. This is the source workbook.
Open the workbook you want the data to be replicated in. This is the destination workbook.
Select the cell in the source workbook you want the data to come from, and enter "Ctrl+C" to copy it.
Switch to the target workbook, and select the cell you want the data to appear in.
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.
Open your workbook in Excel.
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.
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.
Things you need
- Excel 1995 or later