We use the Find function or utility in Excel to search for data in workbooks. We get frustrated when we try to search for data in worksheets, but Excel returns an error message that it cannot find the data.
The Find is Not Working
This tutorial explains some of the reasons for the Find function or utility not working in Excel and possible solutions.
Note: We can open the Find utility by pressing Ctrl + F.
Reason 1: A Filtered List is Preventing Data From Being Displayed
Sometimes the search criteria in a filtered list may prevent some data from being displayed. For example, the text value Blanket has been filtered from the following list:
If we attempt to find the data Blanket:
We get an error message:
Solution
Remove the filter by clicking Data >> Sort & Filter >> Clear:
Now when you search for the data:
The data is found:
Reason 2: The Worksheet is Protected
If the worksheet is protected such that not to allow the selection of locked cells:
The Find function will not work in that worksheet.
Solution
Unprotect the worksheet by clicking Review >> Protect >> Unprotect Sheet.
Note: You may be required to provide a password to unprotect the worksheet.
Reason 3: The Worksheet Does Not Contain the Data You are Looking For
If the worksheet does not contain the data you are looking for, Excel will tell you as much.
For example, the following dataset does not contain the text value Computer.
If we attempt to find the text value Computer in the dataset:
Excel informs us that it could not find what we were looking for:
Reason 4: Case of Search Term Does Not Match Case of Data
If the case of the search term we enter in the Find what drop-down does not match the case of the data we are looking for, the data will not be found.
For example, the following dataset contains the text value Printers:
Let’s attempt to find the data by instructing the Find utility as follows:
Excel returns the message box indicating that it could not find what we were looking for:
Solution
Enter the search term in the case that matches the case of the data you are looking for or uncheck the Match case option.
Reason 5: Wrong Option in the Look in Drop-down
Sometimes the Find function may not find the data we are looking for because we have, maybe mistakenly, selected the wrong option in the Look in the drop-down list.
For example, if we look for Printers in the following dataset:
But select Comments in the Look-in drop-down of the Find utility:
Excel will return the error message:
Solution
Select Formulas or Values in the Look in the drop-down list if you are not looking for Notes or Comments.
Reason 6: Search Term Does Not Match Entire Cell Contents
If the search term we enter in the Find utility does not match the entire cell contents in the worksheet, the data we are looking for will not be found.
For example, if we are looking for Printers in the following dataset:
But instruct the Find utility as follows:
Excel returns an error message indicating that it could not find what we were looking for:
Note: The search term Printer does not match the entire cell contents (Printers) of cell A2.
Solution
Ensure that the search term matches the entire contents of the cells you are searching for. If you are only interested in a partial match then uncheck the Match entire cell contents option.
Reason 7: Format Mismatch
Sometimes the Find utility will not find the data we are looking for because the format of the search term does not match the format of the data we are looking for.
For example, the following dataset contains text strings:
The values are left-aligned in the cells meaning that they are text strings.
If we attempt to look for the value 1000 in the dataset using a search term that has a number format, the value will not be found.
Note: The word Preview appears next to the Find what drop-down when a format is applied to the search term.
Solution
Remove the format applied to the search term by clicking the Format button in the Find and Replace dialog box and then clicking the Clear button in the Find Format dialog box that appears.
Conclusion
It is frustrating when the Find function or utility fails to find the data we are looking for. This tutorial looked at 7 possible reasons for the Find function not working in Excel and solutions.
