Referencing formulas will help you create professional spreadsheets that calculate values where you need them. There will come a time when you will need access to data from other worksheets and it is far easier to create reference formulas that are updated automatically when changes occur. This eliminates the need to manually monitor changes and update figures. The other advantage is that you can perform calculations from various worksheets in a single cell, which ensures your spreadsheet remains uncluttered.
We have already discussed how to instruct Excel to automatically formulate a single cell reference using the point and click method. This Excel tutorial continues from there and will explain how to create multiple reference formulas that can be calculated into a final value. We continue the sequence where we left off, and your formula should now be =WORKSHEET!CELL
-
Type the Relevant Calculation Sign
As you can see by the formula in your fx bar (above your spreadsheet); Excel knows which worksheet the information is located in and which cell holds the data you require. Now you need to instruct it to perform the calculation you need. The “marching ants” will stop and the calculation sign added to your reference formula:
-
+
Typing in a plus sign will ensure Excel performs an addition calculation. If you want to add the next reference to the previous one, then your formula will be =WORKSHEET!CELL+
-
–
If you need Excel to subtract the next reference from the previous one, then you will type in a minus sign. Your formula should now be =WORKSHEET!CELL-
-
*
Typing in a multiplication sign will tell Excel to multiply the next reference with the previous one. It will make the formula =WORKSHEET!CELL*
-
/
Excel will also divide the next reference with the previous one if you type in a division sign. If this is your requirement, then your formula will be =WORKSHEET!CELL/
-
-
Add the Next Cell Reference
You have now told Excel where to find the information you require for the first reference and how to calculate the next one. The process is simply repeated to add additional references to the formula, but you do not type in another equal sign. Once you have typed your calculation preference; simply click on the next worksheet tab, then the cell you desire and add a relevant calculation sign before beginning with the next reference.
The entire process can be repeated until you have all the information you wish to include in your final reference formula. There is no limit to the amount of data you can include.
-
Press Enter
Once you have created references for every cell that contains the data relevant to your requirements, press the Enter key on your keyboard. Excel will automatically implement your new reference formula into your spreadsheet.
Using this method to reference formulas is far easier than manually entering the formula, and because Excel automatically does all the formulas for you; errors are unlikely to occur. Now you will be able to have clutter-free spreadsheets that are updated as changes occur.
We wrote this article after students from our Excel training courses in Fort Lauderdale and the South Florida area recommended it. There are many ways that Excel can be used to your advantage. Feel free to watch our Excel video tutorials to find out how.
