Problems with Find/Replace in Excel

Question

• I am trying to change the contents of 11 cells to match the 12th one. However, whenever I try to Find or Replace the contents of the cells, I get a message stating that either "we could not find what you are looking for" (Find) or "Microsoft Excel cannot find a match" (Replace).
• As you can see, in the attached screen grab, there ARE cells with the information I wish to Find/Replace displayed on the spreadsheet.

You can also see that when clicking on the "Look in" menu, the only option is "Formulas". This list is ONLY produced when I'm in the "Find" tab.

I want to know how I can get the 4 different options when in Replace. I rather liked the automatic Find/Replace feature in an older version of Excel and now find it a major pain that it doesn't work in the newer releases. Is there a way I can change things so that Replace works again?

Thank you, in advance, for anyone's solutions to my problem!

Answer 1

It’s definitely a quirky change in newer Excel versions, and you didn't do anything wrong there. Since it’s just 11 cells, you’re in good shape and hopefully, Microsoft brings back the full “Look in” options in a future update.

Answer 2

Thanks for sharing the screenshot, gordobrysk! That clearly narrows down what's happening. You've encountered a known limitation for find and replace, when you switch to the Replace tab, the “Look in” dropdown always locks to Formulas, and you can’t change it anymore. Excel is actually looking in the underlying formulas, not the displayed values. To fix this here's the workaround:

Use Find (not Replace) and Change “Look in”

Open Find and Replace (Ctrl + F).

Go to Find tab.

Click Options >>.

Change Look in → Values.

Find your cells successfully.

Then, without closing the window, switch to Replace.

• Excel keeps the last “Look in = Values” internally, even though the dropdown disappears.

Now you can safely Replace All.

This trick works as long as you don’t close the dialog.

See if this helps. If you need further assistance just let me know.

Best regards,

Kimberly

Answer 3

Thanks for the answer Kimberly. Sadly, it doesn't work. This is what is displayed when I CTRL + F and then select Valuesl.

This is what happens when I click on the Replace tab:

I've tried exiting the box, shutting down the spreadsheet, bringing it back up again, then CTRL + F and not entering anything, and then doing the Find/Values/Replace and it still only brings up Formulas in Replace. :( It is just sooo bizarre!