How to Find and Replace Cell Reference in Excel Formula

Most of the time we use a cell reference in an excel formula. However, often we have to update the formula based on different situations. Now, if you are working with a large dataset, changing cell references one by one might seem annoying. Instead, you can find all the formulas that contain a particular cell reference and replace it with a new cell reference. This article will show you a step-by-step guideline to find and replace cell references in excel formulas.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Dataset Introduction

Let’s assume I have a dataset containing several employees’ daily working hours and hourly pay. I have calculated the daily total salary of each employee by using the below formula considering Hourly Pay 1.

=C5*$B$14

Dataset Introduction

Here, I have used absolute cell reference as hourly pay is fixed for each employee.

However, if I want to calculate the total salary for each employee based on Hourly Pay 2, I have to change the cell reference in the previous formula. That means I have to find $B$14 in all formulas and replace them with $C$14.


Steps to Find and Replace Cell Reference in Excel Formula with ‘Find & Select’ Option

Step 1: Find the Formula in Excel to Replace Cell Reference

  • Before replacing a cell reference, we have to find where the formulas are located. If you know which cells contain the formulas simply select them as below.

Find the Formula in Excel to Replace Cell Reference

  • On the other hand, if the dataset is large and you do not know which cells contain the formula, then press Ctrl + G to bring the Go To dialog box. After the dialog appears, press Special.

Find the Formula in Excel to Replace Cell Reference

  • As a result, the Go To Special dialog will show up. Click on the Formulas option and press OK.

Find the Formula in Excel to Replace Cell Reference

  • Pressing OK will direct you to the cells that contain formulas.


Step 2: Apply ‘Find & Select’ Option to Replace the Cell Reference in Excel Formula

  • We can find and replace a cell reference using the Find & Select option in excel. To do the task, simply select the cells that have the formula with cell references.

Apply ‘Find & Select’ Option to Replace the Cell Reference in Excel Formula

  • Next, from Excel Ribbon, go to Home > Editing > Find & Select > Replace. As a result, the Find and Replace dialog box will appear.

Apply ‘Find & Select’ Option to Replace the Cell Reference in Excel Formula

  • Now, in the Find what field, type the cell reference that you want to change (here $B$14).
  • Then type $C$14 in the Replace with field. Choose Formulas from the Look in the drop-down.
  • After that, press Replace All.

Note:

  • You can use keyboard shortcuts (Ctrl + H) to bring the Find and Replace dialog box.

Step 3: Final Output after Replacing Cell Reference in Excel Formula

  • Consequently, all the existing cell references are replaced with $C$14. Microsoft Excel will show the number of replacements in a message box. Press OK.

Final Output after Replacing Cell Reference in Excel Formula

  • After you are done with the replacements, click on Close to end the Find and Replace operation.

Read More: Cell Reference in Excel VBA (8 Examples)


Conclusion

In the above article, I have tried to discuss step-by-step guidelines to find and replace a cell reference in excel formula elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

1 Comment
  1. If in your sheet, there is cell reference to $A$141, then this method won’t work.

Leave a reply

ExcelDemy
Logo