Be careful when you use Excel's search feature

Excel's search feature might not find every occurrence of a search string if you don't understand how it works. Here's a trick you can use to overcome this problematic behavior.

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 below, 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 choosing 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.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox