Holding Down the Fort - Anchors

After you create a formula, you may not simply be able to paste the results in a different area such as a new worksheet or a different workbook. If the formula arguments are still in the cell you're trying to copy over to a new cell, what you're doing is actually copying over the formula!


When creating a formula, Excel doesn't look at the data in the cell, and it doesn't necessarily look at the address of the cell (A2, B5, D12, etc) either. It looks at the relative position of a cell in reference to the cell that in which the formula is being created. For example, if you're putting a formula in cell D3 that is adding cells D1 & D2, you would type =(D1+D2). What Excel is understanding by that is add "me minus two rows" + "me minus one row". Therefore, if you were to copy that formula and past it to cell F37, it would read =(F35+F36) which is, in reference,  "me minus two rows" + "me minus one row".

In order to be able to copy that formula to cell F37 or any other cell without changing the reference (D1+D2) you need to "Anchor" the cell references. This is done by adding a dollar sign ($) to either or both of the cell references, row and column. By adding an anchor to the column reference, the column is anchored ($D1+$D2), and by adding an anchor to the row reference, the row becomes anchored (D$1+D$2). By adding the anchor to both the column and row references, they both become anchored ($D$1+$D$2).

As you're typing in the formula, you can add anchors by simply striking the "F4' key before the next part of the formula (comma, parentheses, plus sign, etc). This allows you to cycle through the anchors: pressing the F4 key once will anchor both the row and column, pressing it again will only anchor the row, one more time and it will anchor the column, pressing it a fourth time will remove all anchors and basically anchor none (pressing it one more time will start the cycle over again).

So the next time you copy your formulas over to a new cell and you get an error (#N/A, #VALUE!, etc) it's probably because your references are not anchored. A quick fix will take care of it for you.


Keep Excelling!

Do you like this post? Comment below and / or share on Facebook or Twitter!