Software

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 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.

2 comments
tcclimber
tcclimber

Built-in option Susan is correct with this suggestion, but Excel also has an option in the Find & Replace box that allows you to search all worksheets in the workbook. Selection the Options button in the Find & Replace dialogue box to expand the box. Select the drop-down on the Within: field and click on Workbook. When you then carry out the search Excel will look through all worksheets in the workbook. Great if you want to search in all worksheets, but you'll still need to use Susan's suggestion to search non-adjacent sheets. Terry Castleton

dogknees
dogknees

Did the Find/Replace dialog automatically open the Options section on the second run? It doesn't seem to do this for me, I've got to click the "Options >>" button to get it to display the options. I'm aware of this but I'm sure some of your readers wouldn't know about it. Regards PS Not being a smart-a, just trying to be helpful.

Editor's Picks