This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Why Can’t Excel Find a Number?
Problem: 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 there is actually there. Why can’t Excel find a number?
So why can’t Excel see the 1354.80 value in Figure 96? 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 forte 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 how they can 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 that Look In is set to Values and that 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 the focus is still 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 choose to display the numbers in thousands.
Gotcha: 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.