Resolved: Differences between $B5, B$5, and $B$5?
In 2:56, why you put B$5 for the array, but $B61 for the match lookup value and $G$5 for the second range?
Hi Angela,
Good to hear from you.
Fixing cell references is tricky at first, but then it becomes much easier over time.
So, we have to fix the column reference $B5 whenever we want to paste to the right. In this way column B will stay fixed.
Instead, we want to fix the row reference B$5 (basically we do that by adding the dollar sign in front of the row reference) whenever we will paste downwards and want to keep the cell in row 5.
And lastly, we anchor both references with a dollar sign whenever we want to keep the reference to B5 whenever we paste to the right or downwards.
Hope this helps!
Best,
Ned
thank you！
-
$B5 (Column is Absolute)
- When you copy this reference from one cell to another:
- The column remains constant (
B), because of the$before theB. - The row can change (e.g., 5 to 6, 5 to 4) based on where you paste it.
- The column remains constant (
- When you copy this reference from one cell to another:
-
B$5 (Row is Absolute)
- When you copy this reference:
- The column can change (e.g.,
BtoC,BtoA). - The row remains constant (5), because of the
$before the 5.
- The column can change (e.g.,
- When you copy this reference:
-
$B$5 (Both Column and Row are Absolute)
- No matter where you copy this reference, it will always point to cell
B5. Both the columnBand the row5are fixed.
- No matter where you copy this reference, it will always point to cell
Simple Explanation for a Beginner: Think of the $ sign as an anchor. Wherever you put the anchor, that part won't move. If you anchor the letter (column), it stays the same. If you anchor the number (row), it stays the same. If you anchor both, neither of them will change. If there's no anchor, they can move freely based on where you copy and paste.