Keep a Number Constant in Excel

Let’s say that you have a formula with cell references. Normally, if you drag them, all cells will change accordingly.

Let’s take a look at the following example, to illustrate what I mean.

We want to add a constant (D2) to our example, so it will give us the following result.

We can’t just enter formula =A2+D2 because we want D2 to stay D2 and not change to D3, D4, and D5. In order to do it, you have to use something called absolute cell references.

Instead of writing =A2+D2, write =A2+$D$2. The dollar sign before the column and row number means that the reference should be constant both in columns and in rows. You can also click inside the formula bar, inside D2, and press the F4 button on Windows or Command + T on Mac.

Now, if you autofill cells from B2 to B5 you are going to get the correct values.

Press Ctrl + ~ to display formulas instead of values. That’s what you get.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.