Absolute Reference – Relative Reference in Excel Worksheets
Step 1. through to this step open your Excel application to do some simple examples. Here I have a table to use as an example here in my article. This table made by Menu item, Unit Price, Quantity, and Line Total. The Relative reference is the default and may you use it in at the most of your formulas. Here I need to know the total cost of each menu item on the invoice. Now I’m Multiplying the price per kilo to the total kilo of the items, and use this formula ( =F62*G62 ). Now as you see the GIF I am applying this formula to all the items and get the result the same. Here the price per kilo multiplies to the quantity which related to the result or total line. due to the relative reference can be used as a default in your computer.
Relative Reference
Step 2. Now I want to add to our invoice table two new column and rows. Here I want to calculate the taxes which will be taken from total kilos of items. Our tax rate percentage is known. I wrote that on the top and between quantity and line total I added the Sale tax. Here if we use the same formula we can not get the same result because here we have the total price and the total numbers of kilos and the price per each kilo. To find the taxes we use this formula (=(F62*G62)*H59).
Here I could get the correct answer for the first item, and when I applied it to other it couldn’t calculate the taxes. Due to that, we use an absolute reference here.
Absolute Reference
Step 3. Absolute reference has a dollar sign which has placed before the column, row, or both, to keep them from changing when you copy the formula from one cell to another cell. Here we three option for keeping the rows and column from changing:
$A$3– to keep the column and the row from changing, put a dollar sign in front of both. A$3 – To keep only the row from changing while coping the formula place a dollar sign only in front of the row. $A3 – And to keep only the column from changing place a dollar sign only in front of the column.
But mostly you use the first option because you want to keep both of them from changing. Now place the dollar sign on your formula and apply it overall to see the correct result of the taxes. Now you see when you copy and apply the formula to other columns and rows. You found the correct result.
References to Multiple Worksheets
Step 4. Here again, we have Normal Reference. It works the same Absolute reference, here you just have to include the name of the worksheet along with the cell address. To get started, figure out what cell you want to use and take note of its location. Here I select the cell (I74) this cell is the sum of the line total. Next, switch on another worksheet where you want to use the reference. As you see here I have another table on other worksheets to calculate all services. Now here you see over LINE TOTAL the menu order is not filled, to calculate the total of menu order in this cell. Write the formula, first, type the name of worksheet first then an exclamation point and finally the cell address. (=Sheet1!I74). The last one (I74) is the cell address which we have on the previous worksheet. Here everything is clear, you see that the references between two sheets. And when you bring changes on the first sheet the second sheet also updated.
Conclusion
Mastering cell references are the key to create formulas that work for you and your data. Here I explained that how to Create Absolute References – Relative References and also Reference between multiple worksheets in Microsoft Excel 2016. This is useful when you want to create a formula on one worksheet which references a value from another worksheet. References in excel can be useful for your mini and supermarkets, Hotels or restaurant, or your personal finances. If you faced any question write down and comment. Thanks
