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.
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.
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.
- 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.
Read More: Cell Reference in Excel VBA (8 Examples)
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.
- Different Types of Cell References in Excel (With Examples)
- Difference Between Absolute and Relative Reference in Excel
- Absolute Reference in Excel (With Examples)
- Excel Sheet Name in Formula Dynamic (3 Approaches)
- Reference Another Sheet in Excel (3 Methods)
- How to Lock a Cell in Excel Formula (2 Ways)