Good to know: this is how you freeze a cell in an Excel formula

Excel is an indispensable program for many. Even if you don’t have to work with spreadsheets for work, it’s useful to know how this piece of software works. For example, to map out your finances or to keep track of other things. In those cases, it’s especially helpful to know how formulas work. For example, how to pin a cell reference.

You have probably been in a situation where you want to use a formula more often. You can then select the cell and then use the plus sign at the bottom right of the cell to copy this formula to the cells below or next to it. You can also select the cell and then copy and paste elsewhere. Handy, but there is also a drawback.

This is how you freeze a formula in Excel

The formula adapts to the cell where you paste it. Suppose you have the formula =B1+C1 in cell A1. When you copy this to cell A10, Excel adjusts the formula to =B10+C10. That can be useful, but there are cases where this is not the intention. You may want to copy the same formula or have only part of the formula modified based on the new cell. Fortunately, that is possible.

You can freeze parts of a formula in Excel by using the dollar sign ($). You put this in front of the reference you want to secure. Suppose you have the formula =A1+B1 and you want to extend it to the right, but not that it adjusts the A. This can be done by using the formula =$A1+B1. Wherever you place this formula in the worksheet, the A is now fixed.

Dollar signs to lock cell references

You can also do the same for the number of the row. If you want to move a formula up or down in Excel, put the dollar sign in front of the number of the row you want to lock in the formula. Suppose you have the formula =B1+C1 in cell A1 and you want to move it down, but not that it adjusts B1. What you do then is change the formula to: =B$1+C1.

If you want to use a formula in a different row and column, you can also freeze an entire reference by using a dollar sign twice. For example: =$A$1+B1. No matter where you put this formula in an Excel workbook, it will always use A1. The B1 is now not fixed and is therefore adjusted based on where you put the formula in your workbook.

Did you know that you can also scan tables in Excel with your smartphone camera?