Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I use comments allot in Excel and I need to search for a particular text that is in both values and comments. Is there a way to configure the Find and Replace in Excel to look by default in these fields?

share|improve this question
up vote 1 down vote accepted

I do this: For my workbook, go to VBA. Under Microsoft Excel Objects > ThisWorkbook

Option Explicit
Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Worksheets(1).Cells.Find What:="", After:=ActiveCell, _
    LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False
    Application.ScreenUpdating = True
End Sub

So, whenever the workbook loads, it will run this code which basically do a dummy search and set the LookIn to Values (from Formula). Excel tends to "remember" your last setting. Thus your future CTRL-F will use this setting.

I know you can put a dummy workbook which auto-loads each time Excel loads, but I prefer to set it for specific workbook instead. Hope this helps.

share|improve this answer

You can try Advanced Find & Replace for Microsoft Excel.

Search and replace in all opened Excel workbooks and worksheets. Simultaneous search in values, formulas, hyperlinks and comments.

"Advanced Find & Replace for Microsoft Excel" enhances Excel's built-in Find and gives you:

  • Find and Replace in all opened Excel workbooks & worksheets - Advanced Find & Replace for Excel
  • Simultaneous search in values, formulas, hyperlinks and comments.
  • Search in all opened or selected workbooks and worksheets.
  • All search results displayed in a tree-view.
  • Easy navigation in search results.
  • Replacing in all or selected found items.
  • Exporting found instances to a new workbook.
  • Show / hide the add-in easily (standard Ctrl+F shortcut).
  • Support of Microsoft Excel 2007.

Before the search starts, you define the search region by selecting workbooks, worksheets, comments, hyperlinks, formulas or/and values.

Download the 15-days fully-functional trial version (3 MB)

share|improve this answer
Thanks, but like 99.9% of users I was hoping for a free solution. – Nicu Zecheru Mar 1 '10 at 14:01
You need to find/develop a macro for that. Good luck. – Mehper C. Palavuzlar Mar 1 '10 at 14:34
Since no other answers were posted I'll accept your answer, thanks! – Nicu Zecheru Mar 16 '10 at 10:15
You're welcome. – Mehper C. Palavuzlar Mar 16 '10 at 13:07

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.