When working in Excel, it is important that you understand how to reference formulas from another workbook. At some point, you are going to need to combine information from multiple workbooks into a single workbook and create values from the data. Often this is used for detailed reporting on stocks, sales, expenses, staff salaries and more. An example would be if all these aspects of your company were in workbooks of their own. You would need to include those figures in one place for an accurate accounting of them.
There are two methods that you can use to reference formulas across workbooks. You can either type them manually, or you can simply point and click to instruct Excel to do it automatically for you. This Excel tutorial will teach you how to manually create reference formulas from different workbooks.
The first thing that you need to know is how Excel computes this information. In order for the reference formula to actually work, it needs to follow a set sequence and be entered correctly. Excel needs to know where to find the information you require and you need to tell it exactly how to do this within your formula. We are going to follow this sequence with you and the first step is for you to click on the cell that you wish to formulate with references.
In the above example, you can see how the reference has been formulated. You will always be able to see the formula in the fx bar above your spreadsheet. Once you have clicked on the cell required, then you will need to follow this sequence:
-
=
The first entry is always an equal sign. This is because it lets Excel know that you are planning to use information from elsewhere that will need to be calculated. So the first part of your formula will simply be =
-
Workbook Name
Now you need to tell Excel which workbook the information belongs to. Note that all Excel documents end in .xlsx or .xls. You can hover over the book at the bottom of your computer to see how yours are named. Workbooks are recognized by square brackets, so you will to enclose the workbook name within them. Your formula now will be =[WORKBOOK.xlsx]
If your workbook name consists of more than one word and there are spaces between them, then you will need to use single quotation marks as well. If this is the case then your formula should be ='[WORK BOOK.xlsx]’
-
Worksheet Name
Now that Excel knows which workbook your reference belongs to, you need to instruct it about the worksheet the data belongs to. So you will add the worksheet name and your formula should now be ='[WORK BOOK.xlsx]Sheet1′
If you are using quotation marks to indicate spaces between words in your workbook name, then you need to close off the quotation marks only after you have typed in the name of your worksheet.
-
!
Once Excel has been instructed in the workbook and worksheet where the information can be found, you need to type in an exclamation mark. So your formula should now be ='[WORK BOOK.xlsx]Sheet1′!
-
Cell Address
Now that your formula has the workbook and worksheet names, we can include the cell address. This involves naming the cell first by the column it is situated in and then by the row it is on. Your formula is now ='[WORK BOOK.xlsx]Sheet1′!B1
-
Press Enter
Your first reference formula has now been created and you can press the Enter key on your keyboard. Excel will find the data you have instructed it to and put it into your spreadsheet for you. Many people create single reference formulas and add them together with AutoSum, but you can continue the sequence in another article and add multiple reference formulas. This will keep your spreadsheet clutter-free and perform advanced calculations for you.
This method only works if the workbook you are referencing is open. You can type in reference formulas for closed workbooks; but they involve typing the server location, as well as the file or folder where the Excel document is stored on your computer. This gets very complicated and many mistakes are made, causing Excel to send error messages instead. We recommend just opening the workbook before you type the reference formula.
It is far easier to just use the point and click method over this one. Excel will automatically formulate the references for you and you do not have to manually type them out. We wrote this article because several students on our Excel training courses in Miami and the surrounding South Florida area recommended it for you. To find out what other Excel techniques can be used to help you, please watch our Excel video tutorials.
