Creating reference formulas will greatly improve your spreadsheets. You are able to easily use formulas that reference specific cells from any of your workbooks; allowing you to perform advanced calculations to obtain a final value. An example of this is where you have different workbooks containing monthly sales information, stock levels, employee salaries and other vital information. They can be brought together to calculate total figures for accurate accounting; without having pages of manual entries. The biggest advantages are that Excel automatically updates any changes made to the referenced cells for you and your spreadsheets are free of clutter and performing at their optimum.
Two methods can be used to reference formulas. One involves manually typing each reference into the formula, while the other can achieve the same objective by simply pointing and clicking. This Excel tutorial will discuss how to create multiple references in a single formula using the click-and-point method. We have already explained how to create a single reference formula in another article, and now we will show you exactly how to add additional references from other workbooks onto it.
As you can see in the fx bar in the above example, the first reference to a cell contained in another workbook has been created automatically by Excel after we followed the instruction sequence. This continues as shown below, and you need to follow it exactly in order for Excel to understand your reference locations. Make sure the workbooks you need to reference are open or this process will not work.
-
Calculation Sign
Once we have our first reference, we need to instruct Excel in the calculation we need performed. This is achieved by manually typing in the calculation sign required at the end of the formula:
-
+
If we type in a plus sign, it will tell Excel to add the new reference to the previous one. Your formula will now be:
-
–
For Excel to perform a subtraction calculation, you will need to type in a minus sign. This will make your formula look like this:
-
*
Excel will multiply the previous reference with the next one if you enter a multiplication command. Your formula should now appear as:
-
/
If you need to divide the new reference with the previous one, then type in a division command so that your formula becomes:
-
-
Add Next Reference
Excel now knows that it needs to perform a calculation with your new reference. You can create the next one the same way you did the first one, but leave out the equal sign. So you are going to click on the next workbook and then click on the worksheet tab to which the new data belongs. Then you need to click on the cell whose information you require and add another calculation command before the next reference cycle. You will see in your fx bar that Excel is automatically creating the formula for you.
-
Press Enter
After you have added each reference that you require into the formula, press the Enter key on your keyboard. Every reference within your formula will instantly become effective in your spreadsheet, exactly the way that you have instructed Excel to calculate them.
This is by far the easier method of the two available for referencing formulas; and performing this function in your spreadsheets is an essential part of using Excel professionally. Now you will be able to utilize data from other workbooks without having to manually go back and forth to update information, or have hundreds of individually typed entries for calculation. This elicited a sigh of relief from our Excel training course students in Fort Lauderdale and the surrounding South Florida area, which is why they recommended we write this article for you. To find out about other ways that Excel can benefit you, watch our Excel videos.
