Table of Contents
Often you may want to use the Find and Replace feature in Excel to replace values in formulas in an Excel sheet.
Fortunately this is easy to do and the following example shows how to do so.
Example: How to Use Find and Replace Within Formulas in Excel
Suppose we have the following dataset in Excel that contains information about sales and revenue for various retail stores:
Currently the values in the Revenue column are calculated by multiplying the corresponding value in the Sales column by the value in the Price cell, which is located in cell $B$8.
Suppose we would like to replace the cell reference $B$8 in each formula in the Revenue column with a new cell reference of $B$9 so that the revenue values are calculated using the new price of $3.
To do so, we can type the shortcut Ctrl + G to bring up the Go To window:
Click the Special button in the bottom left corner.
In the new window that appears, click the button next to Formulas:
Once you click OK, all cells that contain formulas in the Excel sheet will be selected:
With these cells selected, type Ctrl + H to bring up the Find and Replace window.
Then type $B$8 in the Find what box and then type $B$9 in the Replace with box:
We can see that the new values in the Revenue column are now calculated by multiplying the corresponding value in the Sales column with the value in the New Price cell.