Table of Contents
Find and Replace is a useful feature in Excel that allows users to quickly and efficiently modify data within a spreadsheet. This feature can also be used within formulas, making it possible to update multiple formulas at once. To use Find and Replace within formulas, simply select the cells containing the formulas you wish to change, then use the keyboard shortcut “Ctrl+H” or go to the “Find and Replace” option in the “Home” tab. From there, you can enter the old value you want to replace and the new value you want to replace it with. This will update all the formulas in the selected cells, saving you time and effort. This feature is particularly helpful when working with large amounts of data or when making repetitive changes to formulas.
Excel: 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:
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: