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.
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
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.
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)
This part tells the formula in which file the data is located.
This part tells the formula in which worksheet the data is located, within the other file of course.
This part tells the formula which cell reference or range reference to use from the other location.
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.
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.
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.
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.
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.