Find & Select

New Contributor

Is it possible to have the default in Find set to Values instead of Formulas

5 Replies


If you mean finding exact values that make up the entire cell (without formulas), you can check the Match entire cell contents



Yes but I don't usually look for cells containing part of a cell. I would search dining if I wanted to see all the dining tables, chairs, and benches in this file. I want to know if it is possible for the default in the "Look In" box to be Values 




Can you provide an example of your workbook? In most cases, when using the Find search alone, formulas and values are interchangeable. It is more sensitive when using the Find and Replace option.


Nope, there is no such setting. You may use macro on workbook run to change the settings, first I found is quite old Change Excel Find & Replace Options Defaults (, but you may google for more samples if such variant works with you.



You could use a macro that invokes a built-in dialog and set that argument to find values. The downside is it doesn't have all of the options (find formats and scope) and it doesn't appear to limit the search to the range you've selected before invoking the dialog - it appears to search the entire sheet regardless.


Sub ShowFindDialog()
     '//  arg1   What            text to find
     '//  arg2   LookIn          formulas (1) or values (2) or comments (3)
     '//  arg3   LookAt          whole cells (1 or xlWhole) or part (2 or xlPart)
     '//  arg4   SearchOrder     xlByRows  (1) or xlByColumns (2)
     '//  arg5   SearchDirection xlNext (1) or xlPrevious (2)
     '//  arg6   MatchCase       True or False
     '//  arg7   MatchByte       True or False
     Application.Dialogs(xlDialogFormulaFind).Show arg2:=2
End Sub