EXCELlent Ones Consulting Skip to Content Skip to Search

Print  |   | Download PDF

Find & Replace

Adjustments with Excel workbooks are inevitable. Whether you are updating values to reflect activity from the most current period or just correcting mistakes. One tool that assists in quickly identifying and changing multiple items on a spreadsheet is a tool called Find and Replace. Below is a quick example of how this tool can be applied to a large spreadsheet.

1. The formula returns an error message because it is referencing the wrong column.

a. Note: The formula in cell D2 should be =B2*C2

2. Highlight the range that you would like to adjust (i.e. D2:D4)

Note: This is an important step. Omitting this step will expose your entire spreadsheet to undesired changes.

3. Click the home ribbon

4. Look to the right and click Find & Select

5. Click Replace

6. Under Find what: enter the item you would like to replace (i.e. A)

7. Under Replace with: enter the item you would to replace with (i.e. B)

8. Click Replace All

9. A dialog box appears confirming the number of items replaced. Click Ok
Note: Always check to make sure the number of replacements makes sense. If a range is not highlighted this Find & Replace technique will search the entire spreadsheet. In this case anything with an “A” would be replaced with a “B”. This includes names, titles etc.

10. The final result is a series of corrected formulas. The formula references column B instead of A.

This technique is very helpful especially when you have a spreadsheet with thousands and thousands or rows.


Footer

Email: info@excellentones.com | P.O. Box 160493 | Brooklyn, NY 11216 | Phone: (347) 763-1035 | Fax: (718) 228-7859

All rights reserved. Copyright 2014 Excellent Ones Consulting LLC.