How to Synchronize Spreadsheets in Excel

Updated April 17, 2017

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'll Need

  • Excel 1995 or later
Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Ken Burnside has been writing freelance since 1990, contributing to publications as diverse as "Pyramid" and "Training & Simulations Journal." A Microsoft MVP in Excel, he holds a Bachelor of Arts in English from the University of Alaska. He won the Origins Award for Attack Vector: Tactical, a board game about space combat.