If you are looking for how to anchor cells in Excel, then you are in the right place. While using Excel, we often need to use formulas where we have to calculate based on a fixed value or cell. This thing is not possible without anchoring the cells. In this article, we’ll try to discuss how to anchor cells in Excel.
What Is Anchoring in Microsoft Excel?
With the anchoring function in Microsoft Excel, you may quickly copy formulas and paste them into the cells of your choice. Certain formulas must be applied to certain cells, and anchoring lets you apply the formula to many cells at once. The cell reference and location are both guaranteed to remain the same when you copy and apply a formula. Because it is efficient, user-friendly, and compatible with common cell references, it is usually recognized as one of Excel’s most widely used alternatives.
Excel offers a couple of steps to anchor cells. The steps are very easy to apply.
1. Inserting Formula to Anchor Cells in Excel
We need to insert a formula first to anchor cells. We can insert any kind of formula according to our requirements based on a fixed value or cell.
Suppose, we have the following dataset named Inserting Formula. It has column headers as Branch Number, Quantity of Sales. The Price Per Unit is $25 which takes place in cell G5. If we need to find out Total Price in Column D, we have to multiply Quantity of Sales with Price Per Unit. And here we need to anchor our cells.
- Firstly, write the following formula in the D5 cell like this.
Here, C5 is the first Quantity of Sales and G5 is the Price Per Unit.
- Secondly, press ENTER to get the output as $500.
- Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the D5
- Eventually, we’ll see that all the outputs are 0.
This is because when we use Fill Handle, each cell takes the multiplication of the corresponding C Column’s cell and G Column’s cell. Here, the value of the Price Per Unit is not fixed and Excel automatically tries to find out the new value of the corresponding Price Per Unit which is not given here. So, the output is 0.
That’s why we need to fix the Price Per Unit in each column’s formula by anchoring it.
Read More: Anchoring Columns in Excel
2. Using Absolute Reference/Dollar Sign to Anchor
We can use the dollar sign ($) to anchor the cells. To add the dollar sign in the figure write the formula =C5*G5 first and then press the F4 key while putting the cursor on G5. So, the formula becomes.
- Secondly, press ENTER.
- Thirdly, use the Fill Handle.
- Eventually, now we’ll get all the valid outputs like this.
- Now, if we want to cross-check what has happened here, click any of the cells of the outputs, and press F2 to see the formula. In the case of the D6 cell, we can see that the formula is.
That means the Price Per Unit has not changed with the change of reference cell. It has occurred due to anchoring the cell.
Download Practice Workbook
That’s all about today’s session. And these are the steps to anchor cells in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website ExcelDemy, a one-stop Excel solution provider.