Excel Find and Replace Guide
Navigate Data Like a Pro
Find and Replace is one of Excel's most powerful features for data management. Learn to quickly locate information, make bulk changes, and use advanced search techniques that will save you hours of manual work.
1. Basic Find Operations
Opening Find Dialog
Find
Keyboard: Ctrl + F
Menu: Home → Find & Select → Find
Find & Replace
Keyboard: Ctrl + H
Menu: Home → Find & Select → Replace
Basic Find Features
Find Next
After entering search text, click "Find Next" or press Enter to jump to the first match. Continue pressing to cycle through all matches.
Find All
Click "Find All" to see a list of all matches with their cell references. Click any result to jump directly to that cell.
Close and Continue
Close the dialog and use F3 orShift + F4 to find next/previous without reopening.
2. Find and Replace Basics
Replace Operations
Replace the current found item and move to the next match
Replace all matches in the entire worksheet or selected range at once
Step-by-Step Replace
- Open Replace Dialog: Press Ctrl + H
- Enter Find What: Type the text/value you want to find
- Enter Replace With: Type the replacement text/value
- Choose Scope: Select range first, or replace in entire sheet
- Execute: Click Replace (one at a time) or Replace All
⚠️ Caution with Replace All
Always review what will be replaced before using Replace All. Consider using Find All first to see all matches, or work with a backup copy of your data.
3. Advanced Find Options
Click "Options >>" to Access
Within
- Sheet: Search only the active worksheet
- Workbook: Search all worksheets in the workbook
Search
- By Rows: Search left to right, then down (default)
- By Columns: Search top to bottom, then right
Look in
- Formulas: Search in actual formulas (e.g., =A1+B1)
- Values: Search in displayed results
- Comments: Search in cell comments
Match Options
- Match case: Differentiate between upper/lowercase
- Match entire cell contents: Find exact matches only
Format-Based Search
You can find cells based on formatting:
- Click "Format..." button in Find dialog
- Choose formatting criteria (font, fill, border, etc.)
- Or use "Choose Format From Cell" to pick existing formatting
- Find all cells matching that format
4. Using Wildcards
Power Up Your Searches
Wildcards let you find patterns rather than exact text. They're incredibly powerful for finding variations of data.
? (Question Mark)
Represents any single character
J?hn
Finds: John, Jahn, J9hn
* (Asterisk)
Represents any number of characters
Sales*
Finds: Sales, Sales2023, SalesReport
Wildcard Examples
*@gmail.comFind all Gmail addresses
???-????Find patterns like ABC-1234
Product*2024Find all 2024 products
~*Find actual asterisk character (~ is escape)
5. Finding in Formulas and Values
Finding in Formulas
Set "Look in" to Formulas to search formula text:
- Find all VLOOKUP formulas:
VLOOKUP - Find references to Sheet2:
Sheet2! - Find absolute references:
$ - Find specific cell references:
A1
Finding in Values
Set "Look in" to Values to search results:
- Find calculated results
- Find displayed text (not formulas)
- Find formatted numbers as shown
- Useful for finding errors:
#DIV/0!
Special Searches
Find Errors
- #DIV/0! - Division by zero
- #NAME? - Unrecognized name
- #VALUE! - Wrong value type
- #REF! - Invalid reference
Find Special Items
- Blank cells: Leave find field empty
- Non-blank cells: Use
* - Numbers only: Use Find & Select → Go To Special
- Formulas only: Use Go To Special → Formulas
6. Advanced Replace Techniques
Format Replacement
Replace formatting without changing text:
- Leave "Find what" empty or enter specific text
- Click "Format" button next to "Find what"
- Select the format to find
- Click "Format" button next to "Replace with"
- Select the new format
- Click Replace All
Useful Replace Scenarios
Find: (two spaces)
Replace: (one space)
Find: (space)
Replace: (leave empty)
Find: *
Replace: PREFIX-& (& represents found text)
Line Break Replacement
Replace line breaks within cells:
- Find line break: Hold Alt and type 010 on numeric keypad
- Or use Ctrl+J in the Find field
- Replace with space, comma, or other separator
7. Best Practices & Tips
Do's ✅
- ✓Always use Find All before Replace All to preview changes
- ✓Save your workbook before major replacements
- ✓Use "Match entire cell contents" for precise replacements
- ✓Select a specific range to limit replacement scope
- ✓Use wildcards for flexible pattern matching
Don'ts ❌
- ✗Don't use Replace All without checking scope
- ✗Don't forget wildcards can match unexpected patterns
- ✗Don't ignore the "Look in" setting - it matters!
- ✗Don't replace in hidden rows/columns by accident
- ✗Don't forget Undo (Ctrl+Z) has limits with large replacements
Pro Tips 💡
Quick Select All: After Find All, press Ctrl+A to select all found cells
Navigation: Use Ctrl+Page Up/Down to move between sheets while finding
History: The dropdown remembers recent searches
Case Changes: Find lowercase, replace with UPPER() function
Regular Selections: Close Find dialog, then Shift+F4 repeats last find
Multiple Criteria: Use filters instead for complex multi-criteria searches
Quick Reference
Essential Shortcuts
- Find: Ctrl + F
- Replace: Ctrl + H
- Find Next: F3
- Find Previous: Shift + F4
Wildcards
- ? = Any single character
- * = Any characters
- ~ = Escape special character
Special Finds
- Line break: Ctrl + J
- Tab: Ctrl + Tab
- Any text: *
- Blank cells: (empty)
Ready for the Next Step?
Continue your Excel journey with: Excel Copy and Paste Special