Excel: How to Use Find and Replace Within Formulas


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:

Once you click Replace All, the reference to $B$8 will be replaced with $B$9 in each formula in column C:

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Replace #N/A Values in Excel
How to Replace Blank Cells with Zero in Excel
How to Substitute Multiple Values in Cell in Excel

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *