Get Data from Separate Workbooks in Excel

How to get data from separate workbooks in Excel. This tutorial includes an example using a formula that uses data from a separate workbook and also a quick method to copy and link to a series of data from another workbook.

Sections:

Formula with Data on Another Workbook

Link to a Series of Data from Another Workbook

Notes

Formula with Data on Another Workbook

Using a formula on data from a separate Excel file.

1. Make sure that both workbooks are open at the same time.
2. Start typing the function/formula
   
3. When you get to the point where you need to reference data from the other workbook, simply navigate to that workbook - do not type anything at all before going to the other workbook.
   
   Once on the second workbook, you should notice the formula that we started to enter appear in the formula bar. This is how you know you did this step correctly.
4. Now, continue filling-out the formula.
   
   You will notice that the formula bar fills-in with a lot of extra stuff instead of just the range reference. This is normal and is how the formula knows that the range is located in a separate file.
5. Continue to enter the formula/function here on the source-data workbook.
   If you need to navigate back to the original file for another argument, make sure that you first type the comma that separates the last argument from the current one. If you fail to do this, the references to the other workbook will not function.
6. Once you finish the formula, hit Enter and Excel should take you back to the original file where you started entering the formula.
   

If you take a look at the formula now, you will see it looks like this:

=SUM('[Get Data from Separate Workbooks 2.xlsx]Sheet1'!$B$2:$B$10)

Get Data from Separate Workbooks 2.xlsx is the name of the other workbook where we got the data from.

Sheet1 is the sheet in the other workbook.

$B$2:$B$10 is the range reference.

When linking between multiple workbooks, all 3 of these pieces of information will be present.

Link to a Series of Data from Another Workbook

You can quickly setup a series of links that pull data in from another workbook. This allows you to do things like, essentially, copy a data table from one workbook to another.

There are a number of ways you can do this; here, I'll show you a very simple way that will pull-in the most recent data from another workbook each time the workbook is opened or refreshed.

1. Make sure both workbooks are open.
2. Type = into the cell on the first workbook:
   
3. Go to the other workbook and click the first cell of the data series.
   
   You know you did this correctly when you see the formula bar look like it does in the photo above; the name of the current workbook along with the sheet name and range reference should be present.
4. Hit Enter and it should complete the formula and take you back to the original workbook.
   
   You should now see the value from the other workbook appear here.
5. To get the rest of the data, double-click the cell with the formula we just made, and remove the dollar signs from in front of the range reference.
   
   It should now look like this:
   
   Hit Enter and we are ready for the next step.
6. Select the cell with the formula and click the quick-grab handle in the lower-right corner of the cell. Drag this cell down for as many cells as are needed to get all of the cells from the source-data table.
   
7. Click the quick-grab handle again and drag it to the right to do the same thing.
   

Now, all of the data is linked to the other workbook and, when you update the original workbook, this one will also be updated with the new data.

Each one of these cells is a link to the other workbook:

Notes

The methods above will work to get data from the source-workbooks even when they are closed.

If you open the master workbook while the source-workbook is closed, the references to the other Excel file will also include the directory in which it is located on your computer; this is normal; don't worry about it and don't change the formulas unless you really have to as it is very easy to mess them up.

Don't forget to download the sample files for this tutorial to work along in Excel.