This tutorial demonstrates how to search all sheets to find a 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.)
1. First, select all sheets. Press and hold CTRL on the keyboard and click on sheet names (January-2021, February-2021, and March-2021.)
All sheets in the file are selected.
2. In the Ribbon, go to Home > Find & Select > Find (or use the keyboard shortcut CTRL + F).
3. 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.
Search All Google Sheets
By default, Google Sheets searches all sheets for a term when you use the Find and Replace option.
1. In the Menu, go to Edit > Find and replace (or use the keyboard shortcut CTRL + H).
2. In the Find box enter a word you want to find (Keyboard), and click Find.
As a result, cell B2 in the January-2021 sheet is selected.
3. Now when you click Find again, you go to the next instance of Keyboard (B7 in March-2021, and so on) until the last appearance of the term.
Unlike Excel, Google Sheets doesn’t offer the option to display a list of instances.