Absolute Reference – Relative Reference in Microsoft Excel 2016

In this article, we are going to concentrate on Absolute Reference and Relative Reference in Excel 2016. And how to use the percentage, absolute reference and relative reference in Microsoft Excel 2016. At first, I want to tell you that when we need to use the percentage in Excel worksheets and how to use it. Then I will explain the usages of Absolute and Relative Reference. Percentage use to show that how much you spend from total and how much remaining. Often people use this option when they pay the salary of their employees. Or somewhere else that you know. But about the usages of references, we use them while copy pastes the contents or the numbers. Through to Relative reference you can paste the copied number just on the below of that number or where which has limited by the main number. And Absolute reference you can use out of the limited places by bringing some changes on the copied number. All of them will be explained step by step.

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

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.

Absolute Reference

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.

References across Multiple worksheets

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