To find and replace in Excel, press Ctrl + H (or Cmd + Shift + H on Mac) to open the Find and Replace dialog box. 🔎✏️ This powerful tool allows you to search for specific values, text, numbers—or even formatting—and instantly replace them throughout your worksheet or workbook. It’s a huge time-saver when correcting errors, updating records, or reformatting large datasets. ⚡
🔍 What Is “Find and Replace” in Excel?
Excel’s Find and Replace feature lets you:
- Search for specific values, words, or characters
- Replace them with something new
- Work across selected cells, sheets, or entire workbooks
- Use advanced search like match case, entire cell contents, or wildcards
Perfect for:
- Correcting typos in big data sets 🧼
- Renaming categories or items
- Updating outdated names or product codes
- Cleaning imported CSV files 📄
🔥 Keyboard Shortcuts for Find and Replace
| Action | Windows | macOS | Ubuntu (LibreOffice Calc) |
|---|---|---|---|
| Open Find | Ctrl + F | Cmd + F | Ctrl + F |
| Open Replace | Ctrl + H | Cmd + Shift + H | Ctrl + H |
Advertisment
Once the Replace window opens:
- Type what you want to find
- Enter what to replace with
- Click Replace or Replace All
- Done in seconds! 🚀
🧠 Find and Replace Options
Inside the Replace dialog, click Options to reveal more search power:
- Within: Sheet or Workbook
- Search by: Rows or Columns
- Look in: Formulas, Values, Comments
- Match case: Case-sensitive search
- Match entire cell contents: Precise match only
- Wildcards:
*= Any number of characters?= A single character
💡 Example: To replace “Pro*” with “Standard”, Excel replaces all items starting with “Pro”.
🧼 Real-Life Examples of Using Find and Replace
Scenario 1: Rename Departments
From “HR” to “People Ops” across the sheet?
Find: HRReplace with: People Ops- Click Replace All
Scenario 2: Remove All Dashes
Clean phone numbers:
Find: -Replace with:(leave blank)- Result: “123-456-7890” becomes “1234567890”
Scenario 3: Change Product Codes
Replace “SKU-” with “ITEM-”
Find: SKU-Replace with: ITEM-
💬 Advanced Trick: Find and Replace Formatting
Excel lets you find and replace formatting, not just values!
- Open Replace dialog
- Click Format (next to Find or Replace box)
- Choose the formatting to find
- Repeat for Replace
- Hit Replace All
Perfect for switching fonts, colors, borders in styled reports 🎨
⚙️ Using Find and Replace Across Sheets
To search across multiple sheets or the entire workbook:
- In the Replace dialog, click Options
- Set “Within” to Workbook
- Excel will search across all sheets 💼
🐧 Find and Replace in Ubuntu (LibreOffice Calc)
LibreOffice Calc offers similar functionality:
- Shortcut:
Ctrl + H - Replace text, numbers, or formatting
- Use regex for advanced searches
- Navigate to Edit > Find & Replace or right-click on cells
🚨 Common Mistakes to Avoid
- Forgetting to check “Match entire cell contents” for exact matches
- Replacing formulas or references unintentionally
- Using Replace All without double-checking can lead to mass errors 😬
- Overwriting data across sheets if searching the whole workbook
✅ Tip: Always save a backup before large replacements!
📊 Use Cases for Find and Replace
| Task | Use Find & Replace? |
|---|---|
| Fix typos in large lists | ✅ Absolutely |
| Clean up phone numbers | ✅ Yes |
| Reformat product SKUs | ✅ Must-use |
| Bulk rename labels | ✅ Quick & Easy |
| Modify formulas | ✅ With caution |
| Change sheet names | ❌ Not supported |
FAQs
How do I open Find and Replace in Excel?
Press Ctrl + H (Windows), Cmd + Shift + H (Mac), or Ctrl + H (Ubuntu LibreOffice).
Can I undo a Replace All action?
Yes—press Ctrl + Z (Windows/Linux) or Cmd + Z (Mac) immediately to undo.
Can I find and replace formatting in Excel?
Yes. Click Format in the dialog to define formatting to search for or replace with.
Is it case-sensitive?
Only if you check the Match case option under Options in the Replace dialog.
Does it work in formulas?
Yes. You can find and replace inside formulas by selecting “Look in: Formulas”.
Can I replace values across multiple sheets?
Yes. Set “Within” to Workbook in the Replace dialog.
🏁 Final Thoughts on Find and Replace in Excel
Excel’s Find and Replace feature is like having a magic wand for your spreadsheets 🪄. With a simple shortcut and a few clicks, you can transform entire data sets, correct thousands of entries, or reformat reports in seconds.
From typos to table cleanup, there’s no faster way to get the job done. Just don’t forget to double-check before hitting Replace All! 😉
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment