How to Remove Anchor in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with Excel, users use various types of formulas to get desired values. In a large data set, typing the formula separately for each cell is time-consuming and tedious. Also, while dragging the formula the cell reference changes in the formula. That’s when you need to absolute the cell reference which is also known as anchoring. But, the whole absolute cell reference or anchoring is not necessary all the time. In this article, I will show you how to remove anchor in Excel.


How to Remove Anchor in Excel: 2 Easy Methods

In this article, you will see two easy methods to remove anchor in Excel. Firstly, I will use a keyboard shortcut for the removal. And secondly, I will apply a VBA code for the same.

To illustrate the article further, I will use the following sample data set.

2 Easy Methods to Remove Anchor in Excel


1. Use Keyboard Shortcut

The first method will include the use of a keyboard shortcut for the removal of anchoring. In this method, you will see what would happen if you use the same cell reference for the whole reference where the referenced cells are not the same for all the result cells. To learn more about this procedure, see the following steps.

Steps:

  • Firstly, look at the following image, where I will find out the conversion of the price in euros of each fruit in different currencies.

  • Secondly, to do that, input the following formula in cell D5.
=$C$5*$D$12

  • Thirdly, press Enter, and using AutoFill, you get values for the cell range D5:F9.
  • From the following result, you can see that the resultant prices in the cell range are all the same.
  • As I have anchored both the column and the row in the cell reference, it has shown the same result for the whole data range.

  • Fourthly, to remove the anchor from the whole cell reference and provide where only it is necessary do the following.
  • First of all, look at the previous formula, where for the cell range D5:E5, I want to show the price of apple in different currencies.
  • So here, I will anchor the column position of the price of apple in euros which is in column C but not the row position.
  • For that, after typing C5 in cell D5, press F4 on your keyboard three times, and the result will look like the following image, where I have only anchored the column.

Anchoring only Column Using Keyboard Shortcut as An Easy Method to Remove Anchor in Excel

  • Then, as the price of apples in different currencies are in the same row and different columns, here I want to anchor the row number only.
  • In order to do that, type D12 in cell D5 and again press F4 on your keyboard but this time press only twice.
  • By doing this, while dragging the formula only the column number will change but the row number will not.

Anchoring only Row Using Keyboard Shortcut as An Easy Method to Remove Anchor in Excel

  • So, the whole formula after the removal of unnecessary anchoring will look like the following image.
=$C5*D$12

Using Keyboard Shortcut for Removing Unnecessary as An Easy Method to Remove Anchor in Excel

  • Afterward, press Enter and drag the formula using AutoFill throughout the whole resultant cell range.

  • Finally, you will see the desired results in all the cells, and this time the price are all accurate which were all wrong in the third step of this procedure.

  • Consequently, put the cursor of your mouse in any of the prices and you will see the formula without unnecessary anchoring.


2. Run a VBA Code

You will see the application of a VBA code as the second method of this procedure. By applying some commands and giving the correct sequence, you will be able to run the code and remove the unnecessary anchors. The detailed steps of this procedure are as follows.

Steps:

  • Firstly, from the following image, you can see the error in the price of each food in different currencies as I anchored both columns and rows in the formula.

  • In order to remove the anchoring with the VBA code, go to the Developer tab of the ribbon.
  • Then, from the Code group, select Visual Basic.

  • Thirdly, you will see the VBA window after the previous step.
  • Then, from the Insert tab select Module.

  • Fourthly, copy the following code and paste it into the module.
Sub Removal_of_Anchor()
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC3*R12C"
Range("D5").Select
Selection.AutoFill Destination:=Range("D5:F5"), Type:=xlFillDefault
Range("D5:F5").Select
Selection.AutoFill Destination:=Range("D5:F9"), Type:=xlFillDefault
Range("D5:F9").Select
End Sub

Writing Code for Applying VBA Code as An Easy Method to Remove Anchor in Excel

VBA Breakdown

  • Firstly, name the sub_procedure.
Sub Removal_of_Anchor()
  • Then, select the cell and insert the formula.
Range("D5").Select
ActiveCell.FormulaR1C1 = "=RC3*R12C"
  • Lastly, drag the formula throughout the whole resultant cell range.
Range("D5").Select
Selection.AutoFill Destination:=Range("D5:F5"), Type:=xlFillDefault
Range("D5:F5").Select
Selection.AutoFill Destination:=Range("D5:F9"), Type:=xlFillDefault
Range("D5:F9").Select
  • After that, save the code and press F5 or the Run button after keeping the cursor in the module.

  • Finally, after running the code will change the value of the D5:F9 data range.

Showing Final Result after Applying VBA Code as An Easy Method to Remove Anchor in Excel

  • Consequently, by clicking on any of the result cells see the formula where the unnecessary anchoring is removed.

Read More: Anchoring Columns in Excel


Things to Remember

  • While you are using any macro or VBA code, remember to enable the macro feature while saving the file.
  • Remember to insert proper cell references while typing the formula.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to remove anchor in Excel. Please share any further queries or recommendations with us in the comments section below. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.

<< Go Back To Anchoring in Excel | Linking in Excel | Learn Excel

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.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo