Excel’s search function, by default, searches only the current sheet, which could pose a problem if you use multiple sheets. Unfortunately, the feature’s not upfront about it either. For instance, the search for Aristotle in the following spreadsheet turns up one occurrence, in cell A14. What you can’t see is that Sheet 3 contains a duplicate book list (simply for the sake of this example). As you can see in the following figure, there’s no alert or warning that specifies which sheet Excel will consider in its search. You might assume it’s just the current sheet, or you might (incorrectly) assume that it’s searching all of the sheets. This behavior is something you just have to know about and accommodate.

Excel will find matches on multiple sheets, but you must first define the search group in one of three ways:

• Hold down [Ctrl] to choose nonadjacent sheets. Then, click the tab of each sheet you want to search.
• Hold down [Shift] and click the first and last sheets in an adjacent group of sheets to select the clicked sheets and all those between.
• Select all sheets in the workbook by right-clicking any sheet tab and selecting Select All Sheets from the resulting shortcut menu.

Once you’ve created a group, Excel will display [Group] in the title bar. To ungroup sheets, click any unselected sheet or right-click a sheet tab and select Ungroup Sheets from the resulting shortcut menu.

When you run a search from a grouped sheet, Excel searches all the sheets in the group, not just the current sheet.