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.
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.
How to Find and Replace Cell Reference in Excel Formula: Step-by-Step Procedures
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.
Read More: How to Use OFFSET for Cell Reference in Excel
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.
Read More: How to Use Cell Value as Worksheet Name in Formula Reference in Excel
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: How to Reference Cell in Another Sheet Dynamically in Excel
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
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
- How to Reference a Cell from a Different Worksheet in Excel
- How to Display Text from Another Cell in Excel
- How to Reference Text in Another Cell in Excel
- How to Use Variable Row Number as Cell Reference in Excel
- How to Reference Cell by Row and Column Number in Excel
- Excel VBA Examples with Cell Reference by Row and Column Number
- Excel VBA: Cell Reference in Another Sheet
<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel
If in your sheet, there is cell reference to $A$141, then this method won’t work.