Creating multiple reference formulas is the professional and uncluttered way of using information from other spreadsheets. Often this data is found in another workbook entirely and Excel needs to be told exactly where to find it. The advantage of using reference formulas is that any changes made in the original cell will instantly reflect in the reference created, so you do not need to monitor changes and update them continuously. In addition to this, Excel will calculate all the references in the formula so that you do not need to have pages of data just to add them together.
This Excel tutorial continues from a previous article that explained how to manually create a reference formula and focuses on how to continue the sequence from there. Before you can add another reference formula; you need to already have typed out the name of the workbook, the worksheet name and the cell address. Your formula should currently be ='[WORK BOOK.xlsx]Sheet1′!B1
-
Calculation Sign
To add another reference formula, the first one will need to be followed by a calculation sign:
-
+
To add the previous reference with the new one you are about to create, follow the sequence with a plus sign. Your formula will now be ='[WORK BOOK.xlsx]Sheet1′!B1+
-
–
If you need to subtract the new reference with the previous one, then type in a minus sign to make your formula ='[WORK BOOK.xlsx]Sheet1′!B1-
-
*
Excel will multiply the previous cell reference with the next one if you enter a multiplication sign into the formula, which will now be ='[WORK BOOK.xlsx]Sheet1′!B1*
-
/
To divide the previous reference with the one you are about to enter, then type in a division sign. This will make your formula ='[WORK BOOK.xlsx]Sheet1′!B1/
-
-
Add Next Reference
Now that the first cell reference has been formulated and a calculation instruction included, you can formulate the next cell reference. The process is exactly the same, except you do not include another equal sign. So you would type in a quotation mark if your workbook contains a name with spaces, follow this with a square bracket, type in the name of your workbook with .xlsx at the end or .xls in later versions of Excel, close the square bracket, type in the name of your worksheet, close the quotation marks, follow that with an exclamation mark and enter the new cell address. Your formula is now ='[WORK BOOK.xlsx]Sheet1′!B1+'[WORK BOOK 2.xlsx]Sheet2′!B2
You can use another workbook entirely, along with a new worksheet and cell address. If you type in the location and address of your reference formula correctly, then Excel will have no trouble finding it. You can continue the process until you have formulated all the references that you require. There is no limit to the amount of references that can be calculated.
-
Press Enter
Once all the cell references that you require have been typed out with no errors, then you can press the Enter key on your keyboard. Excel will instantly add, subtract, multiply or divide each reference as instructed by you to give a final value.
It is important to note that this method will only work if the workbook is open. Although closed workbooks can be referenced, it makes typing the reference formula longer and more complicated. This is because you will need to instruct Excel in the location of the workbook on your computer; the file or folder where it can be found, as well as possibly the server information. Mistakes are guaranteed. We advise just opening the workbook before referencing it in your formula.
There is a much easier way to create multiple formula references. You can simply point and click while Excel configures the formula for you automatically. Due to the importance of this function; this article was recommended by several of our Excel training course students in Miami, Fort Lauderdale and the greater South Florida area. To find out how to use Excel for professional spreadsheets, feel free to watch our Excel video guides.
