How to Anchor Cells in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to anchor cells in excel

  • Firstly, write the following formula in the D5 cell like this.
=C5*G5

Here, C5 is the first Quantity of Sales and G5 is the Price Per Unit.

Inserting Formula

  • 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.

how to anchor cells in excel

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.

=C5*$G$5

Using Dollar Sign to Anchor

  • 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.
=C6*$G$5

That means the Price Per Unit has not changed with the change of reference cell. It has occurred due to anchoring the cell.

how to anchor cells in excel


Download Practice Workbook


Conclusion

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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo