User question: The Excel Find and Replace dialog drives me crazy. I always have to go to the Options button to specify that it should look in Values. In the figure below, the mouse pointer is showing the value that Excel says is not actually there. Why can’t Excel find a number?

Issue-152--December-15fig1

Mr. Excel: Psst, Excel! Try looking under the mouse pointer. You’ve pointed out a lot of the problems with Find and Replace [in Excel]. Let’s take a quick review to uncover some of them. First, when you select Home, Find & Select, Find, Excel presents the simplified version of the Find and Replace dialog without the important settings shown at the bottom of the figure (left).

There are important settings hiding behind the Options button. These settings often cause a Find to fail. Say that you have a calculation for sales tax in column D. Cell D3 shows 70.81 as the result of a formula. By default, Excel searches the formulas instead of the values. If you tried Find without changing Formulas to Values, it will not find $70.81.

Searching the text of the formulas is a bit annoying. How often do you say to yourself,
“Wow, I wonder in which cell I used the SQRTPI function?” But even more annoying
are the other settings, such as Match Case and Match Entire Cell Contents. These settings can be useful, but if you happened to change them at 8:04 a.m. today and haven’t closed Excel since then, even though you’ve opened and closed 40 other workbooks and are working on something completely different, Excel will remember that previous setting. You will often get stung by a strange setting left behind earlier in the day, or even a setting changed when a macro tried to use the Find command with Match Entire Cell Contents turned on.

So why can’t Excel see the 1354.80 value in the figure? Excel is displaying cell C16
with a currency symbol and a comma, and in order to find the cell, you have to search
for $1,354.80! Because Excel’s forté is numbers, it’s rather disappointing that Excel
works like this. But when you understand it, you can work around it.

ADDITIONAL DETAILS: People often ask about how it is possible to search through all sheets in a workbook. You do this by changing the Within dropdown from Sheet to Workbook.

ADDITIONAL DETAILS: Amazingly, Excel can find cells that are displaying as number
signs (#) instead of numbers. Say that you have a column where 5% of the numbers are
showing as #####.

Now, any sane person would make the column wider or turn on Shrink to Fit, but
Excel allows you to perform the following rather crazy set of steps:

1). Select the range of numbers. Press Ctrl+F to display the Find dialog.

2). Type ### in the Find What dialog.

3). If the dialog is not showing the options, click the Options button.

4). Ensure Look In is set to Values and Match Entire Cell Contents is not checked.

5). Instead of clicking Find, click Find All. Excel adds a new section to the dialog, with a list of all the cells that contain ###.

6). While still focusing on the dialog, click Ctrl+A. This will select all the cells in the bottom of the Find All dialog.

You can now format just the selected cells. For example, you could choose
fewer decimals, or a smaller font size, or you could even choose to display the numbers
in thousands.

CAUTION: In Step 6, you are supposed to press Ctrl+A to select all of the found cells.
Be careful that the focus is on the dialog box before pressing Ctrl+A. For example, if
you change the font size, the focus would switch to the worksheet, even though the dialog is still displayed. Pressing Ctrl+A at this point would select all cells in the worksheet instead of just the matching cells. To reestablish focus on the dialog box, you need to click the title bar of the Find and Replace dialog.

This article first appeared in the December 15, 2014, edition of the CFO LearningPro, Excel Edition, newsletter.

CFO contributor Bill Jelen. a.k.a. Mr. Excel, is an Excel MVP and the author of 35 books about Microsoft Excel.

, , ,

14 responses to “Excel Tip: ‘Find and Replace’ Problems”

  1. On the same worksheet, excel cannot find some numbers while it can find the others. All numbers are formatted the same way, without any $ sign and the query is being put in without any sign as well. Any reason?

  2. Unfortunately, I couldn’t find an answer to my question in your tip.
    In Excel 2013, I have entered a value of 9.00, format is currency, 2 decimal places, symbol: none, Negative Numbers (1234.10). I performed a Find and it will not find this cell. If I Find just 9, then it works. Very frustrating!

  3. I just converted from Excel 2010 to 2013, and the rest of my group (we all work in the same files) is still in 2010. It seems that a lot of things (such as comments and formatting styles) are no longer working correctly. One thing I use all the time is Find and Replace within formulas to change references to workbooks, tabs, etc. To ensure an exact match, I do this by copying the the reference I want to change from a formula and pasting it in to the Find and Replace box, but I’m now getting the “We can’t find what you’re looking for” message. I have the cells with the offending text selected. I have the “Look in: Formulas” option set. Aaargh!!!

    • I guess typing the question caused me to find the answer. Apparently, Excel thought I wanted to search for the text formatted in a specific way. When I chose the “Clear Find Format” from the Format button, it worked as usual.

  4. separate issue:
    I have two or three Excel worsheets open at a time; I used to (earlier excel version) be able to click Ctrl-F for -each- Excel page and get a find-replace window for each worksheet..
    Now: if I have 2 or more totally different worksheets open, there’s only one Find-Replace window, so i have to re-enter the same search object (for example: “state” on one sheet and “company” on the other)..
    Is there a way in Settings or Options to have a Find-Replace for each worksheet?

  5. I created an inventory, with words, in Excel this week, and when I try to find something, it does nothing. Not even a “cannot find” message. It just stares at me.

  6. hi I need some help. I pressed the “clear file format” in find and replace in excel and now “Find and Replace” is not working can anyone help pls as I am desperate

  7. Hi there,

    I’m looking for some help on the ‘Find and Replace’ function in Excel 2016.

    I recently upgraded to 2016 and since then, my ‘Find and Replace’ function is appearing over the top of other programs and in its only separate window.

    How do I fix it to only appear when in Excel as this causes issues when I’m using the ‘Alt+Tab’ function to switch between programs.

    Thank you!
    Amber

  8. Hi, I normally could find “-” in excel. For example a reference with ABC-123-456. I want to find – and replace with nothing. so it will show as ABC123456. I have been using this find and replace without any issue for over 10 years but today. It just did not work. Do you know what went wrong?

  9. Find & Replace function now requires me to find the referenced file for a linked spreadsheet when changing a cell reference? Did not do this previously – would change the entire highlighted group of cells to the values entered in the Replace field.

Leave a Reply

Your email address will not be published. Required fields are marked *