Solving the Search Mystery in Excel!

TEAM IM uses a number of Excel spreadsheets to manage its business – probably not unlike many businesses. Many of our spreadsheets are complex with thousands of rows that are often grouped or hidden and contain multiple related tabs, pivot tables, lookup formulas, etc.

One of our users reported an issue with one of our more complex and actively used spreadsheets. The user was unable to use the Excel Find function to locate something in the spreadsheet that he knew had been added only hours before. It was critical that the issue in the spreadsheet was addressed to avoid the possibility of duplicate or redundant entries.

The troubleshooting process started with a cursory review of the spreadsheet with no apparent issues. Other users of the trouble spreadsheet were then surveyed for their experience with the reported use case. Everyone reported that they could find “XYZ” in the spreadsheet with the dreaded “it must be a user problem.” The individual reporting the issue was an experienced Excel user accessing multiple sheets per day and was certainly versed with how to find something in a spreadsheet – simple or complex.

The user then demonstrated the use case to prove his issue and indicated there wasn’t a row, column, or group of cells highlighted, which would have restricted his search range. He also pointed out that his “Look in” parameter in the “Find and Replace” – “Options >>” function was set to Values rather than Formulas, Notes, or Comments. The value being searched for wasn’t in a formula, comment or note, so the Find function itself couldn’t possibly be the problem:

...or could it?

Unknowingly, the user had identified the problem. With Values as the user's “Look in” option, if the data you’re trying to find is in a grouped row or column which is also collapsed at search time (which was the case for the issue reported), the data is considered by Excel to be in a Formula – not a simple Value...ugh!. The response the user sees is, “We couldn’t find what you were looking for. Click options for more ways to search.”

The error message pointed us to where to find the problem, but not necessarily the solution itself. Upon closer review, we found that the default “Look in” setting for the Find function (which is now married with the Replace function) is the counter-intuitive option of Formulas. The Formulas option acts as a “catch all” to allow visible and non-visible values in native values or formulas to be found and, if married with the Replace function to be replaced.

Once the “Look in” parameter was changed from Values to the default of Formulas, the Find function was once again behaving as expected – mystery solved! Most casual and many experienced users pay little attention to the “Look in” options and simply leave them at the default. I consider myself to be an advanced user and don't remember ever playing with (or for that matter noticing) this setting. It just serves as a reminder that while Excel is a fundamentally simple and easy-to-use tool, it has some complexities which make it quite powerful and can take you to some non-intuitive places when you least expect it!