How to Find and Replace Cell Reference in Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

Dataset Introduction

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

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

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

Conclusion

Download Practice Workbook

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

Find and Replace Cell Reference in Formula.xlsx

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

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.

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.

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

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.

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

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.

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

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.