Guide to Referencing Other Excel Files with Formulas and Functions

Your guide to making cross-workbook formulas and functions. This includes an overview of potential errors and problems, including with referencing closed workbooks, calculation speed and disabled links.

In Excel you can use formulas and functions to reference data that is stored in another Excel workbook. This creates links between the files that can be updated and allows you to consolidate data between workbooks without having to actually import the data.

Sections:

Steps to Reference Data in a Separate Workbook (Excel File)

Working with the Cross-Workbook Formula

Speed Up Formula Entry Using Cross-Workbook Formulas

Links Disabled - Important Note

Funky Formulas After I Close or Re-Open the Excel Workbook

External Links and Speed

Notes

Steps to Reference Data in a Separate Workbook (Excel File)

1. Make sure that both Excel workbooks are open at the same time.
2. Go to the workbook where you want the data to be referenced or "imported" and select the cell where you want to put the formula.
   
3. Start typing your formula or function.
   
   Once you get to the arguments for the function, where you would reference a cell, then move to the next step.
4. Without typing anything else, navigate to the other workbook that contains the data that you want to reference.
   
   Once you do this, you will see the function that you started to enter in the other worksheet appear in the Formula Bar for this worksheet, right above column B in this example.
5. Once you are looking at the other Excel file, select the data that you want to reference in the function just like you would if the data was in the same place as the function.
   
6. If you need to input more arguments into the formula/function, simply type the comma to finish the current argument and then enter the data needed for the other arguments without navigating away from the current screen.
   If you don't need to enter anything else for the formula or function go to step 7.
7. Hit Enter when you are finished creating the formula or function and it will then take you back to the original location.  Do not navigate to the original location first and then hit Enter or it will mess-up the reference to the other file.
   

Once you do this you will have a formula or function in Excel that works just like you expect it to work, with a few additional things to note, as explained below.

The formula itself will also look a little weird and I'll explain that as well below.

Working with the Cross-Workbook Formula

This new formula will look kind of weird at first and here it is:

=SUM('[Reference Other Excel Files from Formulas and Functions 2.xlsx]Sheet1'!$B$3:$B$6)

Reference Other Excel Files from Formulas and Functions 2.xlsx

This part tells the formula in which file the data is located.

Sheet1

This part tells the formula in which worksheet the data is located, within the other file of course.

$B$3:$B$6

This part tells the formula which cell reference or range reference to use from the other location.

Speed Up Formula Entry Using Cross-Workbook Formulas

Cross-workbook formulas work almost exactly the same as regular formulas except that they have extra text to tell Excel exactly where the data is located.

You can use the quick-fill handle to copy the above formula over into new cells to quickly sum the data for the remaining months without having to go through all the steps of re-entering formulas for each one.

First, we need to change the cell references from absolute $B$3:$B$6 to relative B3:B6.  You remove the dollar signs and that allows the cell references to change when you copy the formula around the worksheet.

Next, use the quick-fill handle to copy the formula to the right:

And there you go:

If you did not change the absolute cell references to relative references, the new formulas would be exactly the same as the original one in cell B4.  If you don't understand that, just perform the last couple steps to copy the formula while leaving the dollar signs in front of the cell references and you will see what I mean.

Links Disabled - Important Note

The first time that you close and open the workbook that uses formulas to reference another workbook, Excel will usually stop these formulas from updating and getting new data from other Excel files.  This is VERY IMPORTANT because a user may not realize why the data they are seeing is not accurate or up-to-date.  As such, they will need to enable external links.

For most people, this is easy to do as there is a very bright warning that will appear and you will simply need to click the Enable Content button in that warning:

If you don't see this warning and are worried that links aren't updating, go into the Options for Excel and turn them on there; how to do this will vary based on your version of Excel.  If you are in Excel 2007 or later you will go to Options > Trust Center (tab on left side of screen) > Trust Center Settings > External Content (tab on left side of screen > and change the options as desired.

You may also see this warning when you open the workbook:

It is pretty self-explanatory so just read it and select the option that works for you.  I usually just click Update.

Funky Formulas After I Close or Re-Open the Excel Workbook

If you close the workbook that contains the data you are referencing in your formulas and functions, the reference to that file will contain the full file path in the formula.  In my case, it makes the formula go from this:

=SUM('[Reference Other Excel Files from Formulas and Functions 2.xlsx]Sheet1'!$B$3:$B$6)

to this:

=SUM('D:\Excel Related\Tutorials\Tutorial Content - General\[Reference Other Excel Files from Formulas and Functions 2.xlsx]Sheet1'!B3:B6)

Don't freak-out or worry when this happens.  If you re-open the other workbook, these references will go back to looking like the first example. 

The main thing is, don't manually change the formula back to how it used to look or you will break the formula and it won't work.

External Links and Speed

If you have thousands of external links running in a spreadsheet, especially if there is any complex math or complex formulas or lookup functions involved in each formula, this can slow down your worksheet a little or a lot depending on many variables.

If it takes a while for anything to happen after you use the quick-fill handle to copy down 250,000 externally referencing functions, don't worry, just give it some time.  Once it finishes you can then evaluate whether or not you have just created a worksheet that is no longer practically useable.  This can happen because formulas update and recalculate every time you edit something in the worksheet and that means it can take forever to enter a single number into an otherwise empty cell if it takes a long time for the formulas to update.

If the worksheet is too slow, follow this tutorial to stop formula calculation in Excel in order to increase worksheet performance.  Once you do that, you can manually decide when to update all of the formulas in the worksheet so you don't have to wait around each time you enter a small piece of data into a cell in the same worksheet.

Notes

Play around with formulas and functions that reference external workbooks and see how it works for you.  Just remember to test out if the result makes your worksheet really slow and follow the steps above to fix it if that problem happens.

Also, don't forget that you can download the two accompanying workbooks to test this feature out.