Microsoft Excel Cell Referencing Tricks
6 Make use of all kinds of reference in Excel, don’t just stick with Absolute Reference $A$1.
If you have followed us from the previous lesson, you should have read about cell referencing in Excel. Reference your value from cell and ranges instead of hardcoding values while creating formula. If you do not already read it, please do through it before continuing this article to avoid any confusion.
Relative Reference
Below showing 2 lists of value being stored in 2 columns, Col A and Col B. While in the column A+B, the user will have to get the sum of the number in Col A and Col B. After that, the formula (calculation) will be copied to the corresponding rows.
While the formula is copied to the adjacent row, Excel will automatically reference the value from the corresponding cells too. This we call it as Relative Reference, which means that while the first formula (A2+B2) is being copied to the adjacent row, the formula will automatically reference towards the next row which is (A3+B3) ßRelative Reference.
While Relative Reference might not be what you need of all time. Continue reading to see what are the issues I would like to highlight.
Take a look at the report below, I would like to calculate the commission of each of the salesperson in the spreadsheet.
There are 2 difference schemes available in this XXX company. For those who served the company below 2 years will entitle amount commission of 4.5% only, while who served more than 2 years will be entitled 6.8%. When the formula is being written into the cell C7, the formula will then be copied to the corresponding rows. When Relative Reference take place, the reference will then started to reference towards the adjacent reference. In this case, we’re only allowing the B7(Sum of Sales) reference towards the adjacent rows, NOT C4 (the 4.5% commission rate).
Check out the clip below:
The problem that we face now can easily turn the reference of C4 (which is the 4.5% commission rate into an Absolute Row Reference). This means that we will have to tell Excel to lock this cell in its row, do not reference to the adjacent row reference. To do so, you will have to locate $ in front of the row reference, turning C4 into C$4 (placing the dollar sign in front of 4.
Some of you will be asking why am I not placing 2 $ instead, making it into $C$4? Continue reading you will then realize why. Now once I’m done with placing the $, it’s time to re-copy the formula to the adjacent rows.
Now, we’re not done just yet. The formula will then needed to be copied to the adjacent column, the column where we gotta calculate commission for those who served the compare more than 2 years.
Copy the formula from C column to D column, let’s see what’s gonna happened.
When the formula is being copied to the adjacent column, the Relative Reference immediately take place as well. The Sum of Sales is now reference towards the adjacent column which is not what we wish for. What we want is the Sum of Sales reference stays at B column in stead of the other column. So now you know the trick, you’ll have to turn the reference of the Sum of Sales into Absolute column. Place the $ in front of the column of reference, turning B7 into $B7.
So we have successfully make sure the Sum of Sales reference stays at Column B by placing the $ In front of the B.
Now let’s talk back about the reference of the commission rate. Remember previously I mentioned not to place 2 dollar as such $C$4, this is because when the $ is being place in front of the C means that we’re locking the 4.5% reference forever stays at column C. If you look at this report carefully, we need the 4.5% reference to the adjacent column the formula will be able to reference to the 6.8% which is located at the column D.
The original reference of 4.5% (cell C4) we are only placing the dollar sign to the “4” (C$4) which is the row to avoid it from picking up the reference from adjacent row only. We need the column reference to stay relative so it will be able to reference to D column.
Microsoft Excel : Deadly sins you should not do in Excel (Part 1)
Microsoft Excel : Deadly sins of maintaining list in Excel sheet (Part 2)
Microsoft Excel: Deadly sins of maintaining list in Excel sheet Part 3
Microsoft Excel: Deadly sins of maintaining list in Excel sheet (Part 4)
Microsoft Excel: Deadly sins of maintaining list in Excel sheet (Part 5)
