Having the knowledge to create multiple reference formulas will ensure your spreadsheets function at the optimal level and remain uncluttered. You will be able to add up several (or more) reference formulas in a single cell to give you a calculated value. For example, should you require a total figure for all the sales made in the year; you can simply create a reference formula for the total in each monthly worksheet.
This Excel tutorial is going to show you exactly how to manually create multiple reference formulas from any worksheet in your workbook, but first you will need to formulate the first reference. This is a continuation of a previous Excel tutorial on creating a single reference formula.
Excel needs to be instructed in the location of your data. If you followed the sequence, you will have an equal sign followed by your worksheet name, an exclamation mark and your cell address. The next sequence will add multiple reference formulas to your current one. Your reference formula should now be =WORKSHEET!CELL
-
Calculation Sign
Once you have completed the first cell reference, then you will need to repeat this process for the next cell you require information from. The only difference is that now you will need to use a calculation sign instead of an equal sign, according to what you need to accomplish:
-
+
The plus sign will add the next cell reference to the previous one. So if you are adding them together, then your formula will be =WORKSHEET!CELL+
-
–
The minus sign will subtract the next cell reference from the previous one. If you need to use a subtraction formula, then it should now be =WORKSHEET!CELL-
-
*
The multiplication sign will simply multiply the next cell reference with the previous one. If this is what you require, then your formula will be =WORKSHEET!CELL*
-
/
The division sign will divide the next cell reference with the previous one. If you require a division calculation, your formula will be =WORKSHEET!CELL/
-
-
Add Next Cell Reference
Excel has now been instructed to calculate the next cell reference with the one you have entered last. You will now need to type the location and address of the next cell whose information you require. The process is exactly the same, but without the equal sign. So your formula will now be =WORKSHEET!CELL+WORKSHEET!CELL
Note that you may use information from a different worksheet or the same one. So long as the formula is typed in correctly, Excel will create the reference perfectly.
-
Enter
Now you will have two cells that have been referenced in your formula. You can calculate as many as you require. Go ahead and create references for all the information that you need. Once you have completed the sequence, simply press Enter and Excel will instantly calculate the totals you have instructed it to.
You should be able to type multiple reference formulas into your Excel spreadsheets now. There is an easier way to do this that eliminates any chance of human error. This is one of the most helpful functions that Excel offers; which is why many of our Excel training course students in South Florida suggested we write this article for you. Our advanced Excel video guides will help you discover more ways that Excel can be used to your advantage.