Hi Guys,
As you delve deeper into excel formulas you have to be insanely good at some ‘bird’ called Cell Referencing. If you have not heard of it.. that’s okay, heard of it but not know much .. that is also okay. Let’s go and unravel this concept..
Cell Referencing – What is it ?
To put it in simple words, when you refer to any cell (for example =A1) it is called cell referencing. Well, nothing great about it, agree! but let’s explore this a bit further. So I have a case where I am adding Salary and Commission to get Total Compensation
Take a note of few things above
- I make the formula in the first cell and I drag it down to the rest of the cells
- Excel automatically understands that it has to pick up cells in the next row to get the next total
- The cell references change automatically ! (from B3+C3 in first row to B10+C10 in last row)
Absolute Cell Referencing
When the cell references don’t move at all it is an absolute cell reference. Take a look at a slightly different case, we are supposed to calculate a 10% commission for all
Take a note of
- The commission % cell (D2) does not move down as I copy the formula down
- I apply $ sign to freeze ($D$2) by pressing the F4 key once (my cursor being on D2 in the formula)
- An alternative to absolute referencing is Cell Naming
Mixed Referencing
Sometimes or rather most often the need is a bit more tricky. Take a look, we need to calculate the total salaries of Senior to Junior Management which is equal to Salaries + Allowances (month wise) + Perks (level wise). Now we have to make one formula and copy it across the cells
Take a note of
- Allowances column is fixed so I am freezing only the column by pressing F4 three times ($C9)
- Salaries are different (for all employees) and are spread in columns and rows so we don’t freeze anything here (E9)
- Perks are kept in row 5 but, so only freeze the row here by pressing F4 two times, (E$5)
Time for Some Tricks 😀
RUNNING TOTALS– Consider this example where we have to total the sales as we proceed in months (running total)
The trick is to freeze the only start of the range i.e. $C$5 (pressing F4 once) and we are sorted !
EXPANDING RANGES IN VLOOKUP
So here we have Months, Sales and Region and we want to lookup for 3 months sales and region in the look up table
Note a few interesting things
- I am freezing the month column ($F6) because when I copy the formula to the right, I still want to look up based on month
- I am just selecting the month and sales in table_array input, as I copy the formula right the range expands because I have done absolute referencing to the start of the range ($B$6) and only freezing the row number (C$17) at the end of the array
- The row numbers don’t change (G$4) for column index number in VLOOKUP, so a mixed referencing (2 times F4, for locking only the row)
- Learn VLOOKUP + some additional VLOOKUP tricks
Closing thoughts
- Most often people refer to cell referencing as locking the cell, which is entirely not correct but kind of understood in the same way by everyone, so that is fine. They’ll say like … hey man, lock the row in your formula, or lock the column and the formula would work 😀
- Remember for locking ( 😀 ), I mean applying the $ sign
- The entire cell (row and column) Press F4 once
- Only the row, press F4 twice
- Only the column, press F4 thrice
- Unlock, press F4 four times
- Now cell referencing is just not limited to formulas, it becomes a beast in cell naming and formula driven condition formatting (we’ll spare that for some other day)
Download the file here from down below. Please tell me how do you use cell referencing ?
