How to synchronize data between Microsoft Excel documents

You can configure Excel to automatically import data from one file to another, which is a great feature for creating summary spreadsheets. Here’s how!

Microsoft Excel offers two ways to link data: one between worksheets in the same file, the other between worksheets in separate files. Here we will show you how to link two Microsoft Excel spreadsheet files and automatically import data from one into the other.

With this method, you don’t need to link Excel documents together. Also, when new data is entered into the source file, the destination Excel file is immediately updated to reflect the change.

How to synchronize data between Microsoft Excel files

Here, we’ll show you how to make sure that when data is entered into one Excel file (“Source Data” in our example), it’s automatically imported into another (“Destination File”). This is just one of the many ways to save time when using Microsoft Excel.

This is what the source file for our example looks like.

To synchronize data between Excel files, follow these steps:

  1. Open both Microsoft Excel documents.
  2. In the destination file, click on the cell in which you want to automatically import the data and press the key Equal to key on your keyboard.
  3. Switch to the source Excel file and click on the cell from which you want to export the data.
  4. Return to the destination file. You will see a formula in the cell you were working in.
  5. Remove the dollar signs from the equation. They are part of the cell reference part of the equation.

  6. Hit Walk in.

  7. You can now copy this cell and paste it into any cell in the destination file that you want to mirror in the source file. With this configuration, the corresponding cell in the source file will be linked to the cell in the destination file. For example, if you copy and paste this formula into the cell AT 5 of the destination file, it will be linked to the cell AT 5 in the source file.

  8. You can also drag the cell from the lower right corner to fill the column. Any cells formatted this way will mirror the corresponding cells in the source file.

USE VIDEO OF THE DAY

Whenever data is added to the source file, it will automatically populate into the corresponding cells of the destination file, as long as those cells contain the formula we used above.

General formula to automatically import data into Excel

The power of Microsoft Spreadsheet is that it lets you get the most out of your work with some of Excel’s advanced features. All you need to know are a few handy formulas. Here is the formula for importing data into Excel files in general. You can enter it manually if the source file is not open.

='File_path[File_name]Sheet_name'!Cell_reference

You will need to replace “File_path” with the actual path of your source file, “File_name” with the name of the source file including the extension, “Sheet_name” with the Excel spreadsheet name, and “Cell_reference” by the source file cell from which the data is to be imported. Remember to keep the quotation marks and the exclamation mark.

You can use it directly if you know the file path, file name, sheet name and cell from which the data should be imported.

Here is the formula used in our example:

='D:Excel doc data sync[Source data.xlsx]Sheet1'!A3

Work smarter and increase your productivity in Excel

The simple method to automatically import data into Microsoft Excel by synchronizing files will save you a lot of work and increase your productivity. This makes it easy to create summary spreadsheets and you can have all the relevant data on separate Excel sheets without having to configure them manually.


learn-excel

How to Learn Microsoft Excel Fast: 8 Tips

Read more


Comments are closed.