Mithun
It’s bothersome if we face problems while using the Find and Replace tool in Excel. Because it’s the most commonly used tool in Excel. So this article will cover all the reasons and 6 useful fixes if the Find and Replace tool is not working in Excel. Hope the sharp steps and clear images will give you the proper guideline.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
6 Solutions: Excel Find and Replace Not Working
To demonstrate the reasons and fixes we’ll use the following dataset that represents the 5 best-nominated movies in the Best Picture category for Oscar Award 2022.
1. Ensure Cell Selection If Find and Replace Is Not Working in Excel
First of all, I’ll introduce the most common mistake while using the Find and Replace tool. See that I have selected only the last three rows from the dataset.
Then tried to replace Dune with Flee. Pressed Replace All but take a look at the next image to see what happened.
It’s showing- Excel couldn’t find anything to replace.
The reason is the name Dune was not in my selected range. Keep in mind, if you select multiple cells then Excel will find those cells only.
Solution:
- Select one cell or none and then apply the Find and Replace tool.
Then it will work properly.
Read More: How to Find and Replace Using Formula in Excel (4 Examples)
2. Unmark Match Entire Cell Contents If Find and Replace Is Not Working
If you want to find and replace a word of a cell marking with Match Entire Cell Contents then see what happens! I tried to replace the word- Car with Bus of the movie name Drive My Car.
You will get the error message. Because if you mark that option then Excel will only search for the whole content of a cell, partial search won’t work.
Solution:
- Unmark that option before using the Find and Replace tool.
Now see that, replaced successfully.
Read More: How to Find and Replace within Selection in Excel (7 Methods)
3. Remove Filter If Find and Replace Does Not Work in Excel
If you filter a column before using the Find and Replace tool then Excel will apply the tool only for filtered values, not for all values. See that I filtered the movies that were released in March.
Then tried to find and replace the movie Dune by Flee.
But it showed the error message. Because Dune is not in my filtered list.
Solution:
- So you must have to mark the Select All option before using the Find and Replace tool.
Related Content: How to Replace Text in Excel Formula (7 Easy Ways)
Similar Readings:
- How to Find and Replace Asterisk (*) Character in Excel
- How to Find and Replace Values in Multiple Excel Files (3 Methods)
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- How to Find and Replace Multiple Words at Once in Excel (7 Methods)
- Excel VBA to Find and Replace Text in a Column (2 Examples)
4. Delete Unwanted Spaces in Excel If Find and Replace Is Not Working
Here, I tried to find and replace the movie Drive My Car with Flee and all the options was rightly selected but again got the error message.
Excel got nothing although the name was in my dataset.
The reason is- there is an extra space between the word ‘My’ and ‘Car’.
Solution:
- Remove the extra space and then try the Find and Replace tool, Of course, it will work.
Read More: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
5. Unprotect Sheet to Use Find and Replace Tool
If your sheet is protected with a password then you can not change anything also no tool will work too. You will be able to view the sheet just.
To check whether your sheet is protected or not, click as follows: Home > Cells > Format.
If your sheet is protected, it will show Unprotect Sheet.
Solution:
- Unprotect your sheet by clicking: Home > Cells > Format > Unprotect Sheet before using the Find and Replace tool.
- Later, give the password and press OK. Then the tool will work by default.
Related Content: How to Find and Replace from List with Macro in Excel (5 Examples)
6. Repair Corrupted Excel Worksheet
Sometimes the Find and Replace tool doesn’t work because the value you are looking for that is missing from the sheet. It may happen because of unexpected data loss, especially for a large dataset.
Solution:
- There are many Excel data recovering tools available on the internet. Google it, download one of them and try. Hope you will be able to recover the data.
Related Content: Excel VBA: How to Find and Replace Text in Word Document
Conclusion
I hope the procedures described above will be good enough to solve the problem when the Find and Replace tool is not working in Excel. Feel free to ask any question in the comment section and please give me feedback.
