Get Data from Separate Workbooks in Excel

Add to Favorites
Author: | Edits: don

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
    23a5b97ff088f0462a3ffdf1b24f47b5.png
  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.
    b5b54106506f20d87cbc9ffee7b3a3a3.jpg
    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.
    5ba88a74336409a09b54dc6060bbdbc0.png
    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.
    ee736132005264c8539f856f6105beb4.png

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)

c7ed5a1fed7cdddc93e50bf632973edd.png

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:
    15e6f65620117d675c3a48e971e56c25.png
  3. Go to the other workbook and click the first cell of the data series.
    1a26f5c98bda3794927d60ea663151a4.png
    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.
    d5de3b61140ab5c515d59927e2dc3a0e.png
    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.
    bb178a4f652afe31af3acc4dadcf57db.png
    It should now look like this:
    45b021fef2416888113c8791b0346b36.png
    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.
    f4340621ddc7e9c04df70ed6cf6eb1cd.png
  7. Click the quick-grab handle again and drag it to the right to do the same thing.
    4df2c9afeb2ce42ff3e0b7b12d73063b.png

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:

ab277a50fa78b09c0031bb6069af5f93.png

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File 1, Excel File 2

Similar Content on TeachExcel
Macro to get Data from Another Workbook in Excel
Tutorial: Macro to get data from a workbook, closed or open, over a network or locally on your comp...
Getting Data from a UserForm
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Get Values from a Chart
Macro: This macro will pull the values from a chart in excel and list those values on another spr...
Select Data from Separate Worksheets with Macros VBA in Excel
Tutorial: Select data from other worksheets with Macros and VBA without navigating to those workshee...
Activate or Navigate to a Worksheet using Macros VBA in Excel
Tutorial: Make a particular worksheet visible using a macro in Excel. This is called activating a wo...
Tutorial Details
Downloadable Files: Excel File 1, Excel File 2
Similar Content
Macro to get Data from Another Workbook in Excel
Tutorial: Macro to get data from a workbook, closed or open, over a network or locally on your comp...
Getting Data from a UserForm
Tutorial: How to get data from a UserForm in Excel, including from: text inputs (TextBox), list boxe...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Excel Forum