Anchoring Formula in Excel

How to Use Anchoring Formula?

Do you remember the time when you copied and pasted your formula, and realized that the cells moved? You could not correctly receive the results that you want. At least half of those errors occurred because of not anchoring the right cell in your formula. Today, keySkillset will walk you through the anchoring formula in Excel and see how it can help you to reduce a significant amount of your time working on your Excel files.

Anchoring is probably the most used shortcut in Excel. By pressing F4, you can select different options of anchoring. It means locking the cells inside the formulas from different dimensions. Let’s look at the examples below to see how anchoring option works.

How Many Times Can I Press F4?

If we look at the first example above, if we press once it will lock the cell from both its column and row. In other words, if you copy the formula and paste on a different cell, the formula will still give you the index inside cell B7. The first dollar sign shows that it is anchored by its column. Therefore, no matter how many columns left or right do you copy the formula. It will still stay on the column B. The same process works for the row 7 because of the dollar sign before the number 7.

‍

If you use F4 twice, you can see that the formula now has only the dollar sign before the row number. If you copy the formula and move itanywhere between the same column, the formula will still show you the same result – B7. However, it is not locked from the column. If you copy the cell and paste into anywhere in column D, it will give C7.

If you press F4 button three times, it will lock only the column and remove the anchor sign from the row. That is exactly the opposite of the situation above that we described for using F4 twice. Therefore, no matter how many columns do you copy the formula left or right., I will still show the cell B7. However, if you move two rows above in any column, it will show you the result B5.

If you press F4 for the fourth time, it will remove all the anchoring signs from the formula. Moreover, it will return the formula to the same format it used to be. Every time you copy and paste the formula to another cell, either the row or column location of the formula will change.

Anchoring is definitely the most essential trick that you can learn in Excel. By locking any specific cell used in the formula you will save tons of time. Especially, when it comes to manually correct or type the formula over and over again.

‍