Resolved: Differences between $B5, B$5, and $B$5?

Question

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?

Answer 1

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

Answer 2

Angela Zhang

Posted on:

27 Feb 2023

0

thank you！

Answer 3

$B5 (Column is Absolute)
- When you copy this reference from one cell to another:
  - The column remains constant (B), because of the $ before the B.
  - The row can change (e.g., 5 to 6, 5 to 4) based on where you paste it.
B$5 (Row is Absolute)
- When you copy this reference:
  - The column can change (e.g., B to C, B to A).
  - The row remains constant (5), because of the $ before the 5.
$B$5 (Both Column and Row are Absolute)
- No matter where you copy this reference, it will always point to cell B5. Both the column B and the row 5 are fixed.

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.

Resolved: Differences between $B5, B$5, and $B$5?

Submit an answer

related questions