How do I search for a word or phrase across multiple sheets in a workbook in Excel 2007, 2010, 2013 and 2016?
Why would I want to search a whole workbook?
We think of Excel as being used primarily for numbers (although you might want to search for those, too), but I often encounter spreadsheets full of text. For example, when I’m localising text from US to UK English, or editing text that’s been translated, and it’s been output from a translation tool such as Trados, it often comes to me in an Excel spreadsheet.
Just like when I’m editing a Word or PDF file, I often want to either look for all instances of a word I want to change or check that I haven’t missed anything. And if that word might be in any one of many sheets in a workbook, I will want to search all of those sheets.
How do I perform a search in all the sheets of a workbook?
In this example, I want to find all the instances of the word “authorized” in all the many sheets in an Excel workbook.
First of all, press Control and F at the same time to bring up the Find and Replace dialogue box:
Using this search without changing anything will just search in the sheet I’m currently in.
Click on Options:
This brings up a load of options, including some other exciting ones we’re not looking at here, but which might be useful as well:
Click on the drop-down arrow to the right of Within: Sheet and change it to Workbook:
Now when you click “Find Next”, it will find the cell where that text is throughout the whole workbook.
In this article, we’ve learned how to search a whole workbook in Excel 2007, 2010, 2013 and 2016.
If you’ve found this article helpful, please do post a comment below, and if you think others would find it useful, please share it using the sharing buttons below the article. Thank you!
Other useful posts on Excel on this blog:
Freezing rows and columns in Excel – and freezing both at the same time
How to flip a column in Excel – turn it upside down but keep the exact same order!