RSS

How do I search in a whole workbook in Excel?

20 Jun

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:

How to view two workbooks side by side in Excel 2007 and 2010

How to view two pages of a workbook at the same time

How do I print the column headings on every sheet in Excel?

How to print the column and row numbers/ letters and gridlines

How to change rows into columns and columns into rows in Excel

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!

 
Leave a comment

Posted by on June 20, 2018 in Excel, Short cuts

 

Tags: , ,

I love hearing from my readers - do please leave a comment!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: