Fixing ranges

Definition

Before we start with any formulas it is important to understand how to fix the range for formulas and why it is this done.

The dollars sign ($) is used to fix ranges for formulas.

If I have a cell link, for example =a1 then, when we copy this formulas down the range will move. That means if we copy the formula one down it will say =a2 and so on (the rows just change as we copy down). If we were to copy the formula one column right it would be =b1 (the columns thus changes if we copy left or right). This only works for the drag-copy method. Not if you go into home toolbar and select copy and then paste.

Example

See in windows below. In column G we copied down, thus the rows change. In column A we copied right and there the columns letters change. See in K5, we effectively copied down and right, so the columns and rows change.

We can fix columns or the rows so that when we copy down or right, the range does not copy with. If we want to fix the row we need to insert a dollar sign in front of the row number. If we want to fix the columns we need to insert a dollar sign in front of the column (A, B, C etc.). If we want to fix the row and column we need to put a dollar sign in front of the columns and the row.

In the below screen we fixed the row only, so everywhere we copy the row will stay on 1 but the columns will keep changing if we do not fix that.

In the below we fixed only the columns, thus when we copy the column will stay as A, but the rows will change if we copy down or up.

In the below screen we fix the row and column. It will stay thus on A1 no matter where we copy.

<-----Back to index

Next topic----->