27. Excel Tips - Find and Replace across the whole workbook

Nick's picture

This tip shows you how to find and replace across the entire workbook.
You might spend ages looking for things when a simple trick can save lots of time.
Suppose you have a Workbook that looks like this:

Sheet1
Excel-Tips-Find-and-Replace-across-the-whole-workbook

Sheet2
Excel-Tips-Find-and-Replace-across-the-whole-workbook

You have the name “Mike” on 2 worksheets.
Now suppose you want to replace “Mike” with “Nick” in the entire workbook.

Step1:
Press CTRL + H... then Options... set it up like this:
Excel-Tips-Find-and-Replace-across-the-whole-workbook

Step2:
Click “Replace All”

Result:
Excel-Tips-Find-and-Replace-across-the-whole-workbook
Excel-Tips-Find-and-Replace-across-the-whole-workbook

If you only want to replace on certain worksheets, you can select them first by clicking on “Names1”, holding CTRL, then select “Names2” tab etc... and go to Step1

Training Video on Find and Replace:

Search across whole workbook

Search in Spreadsheets, specially across multiple spreadsheets and whole workbook has always been a pain.

If you are comfortable using Web Based tool, Big Parser can solve this issue, All you need to do is to upload and parse your spreadsheets on Big Parser and Search them easily (Upto 100 spreadsheets are Free) , You can learn more about it here https://www.bigparser.com/c/N1714b06i1836

The good part is, it works seamless across different devices