This tutorial demonstrates how to search all sheets to find a word or phrase in Excel and Google Sheets.
Search All Sheets
The search functionality in Excel searches in the current worksheet by default. However, you can also make it search all sheets in the file. Say you have the following Excel file with three sheets (January-2021, February-2021, and March-2021) and the same columns: Product Name and Price.
You want to search for Keyboard in the entire workbook. (It appears in cell B2 in sheet January-2021 and B7 in sheet March-2021.)
- First, select all sheets. Press and hold CTRL and click on sheet names (January-2021, February-2021, and March-2021.)
All sheets in the file are selected.
- In the Ribbon, go to Home > Find & Select > Find (or use the shortcut CTRL + F).
- Now, in the Find what box enter the word you want to find (“Keyboard“) and click Find All.
The bottom section of the window displays all appearances of the searched term in the workbook. As you can see, there are matches in the January-2021 (cell B2) and March-2021 (B7) sheets. If you click Find Next, you are cycled through each found cell.
See also: Using Find and Replace in Excel VBA
Search All Google Sheets
By default, Google Sheets searches all sheets for a term when you use Find and Replace.
- In the Menu, go to Edit > Find and replace (or use the shortcut CTRL + H).
- In the Find box enter the word you want to find (“Keyboard“) and click Find.
As a result, cell B2 in the January-2021 sheet is selected.
- Now when you click Find again, you go to the next instance of Keyboard (B7 in March-2021, and so on) through the last appearance of the term.
Unlike Excel, Google Sheets doesn’t offer the option to display a list of instances.