You need to update many instances of a word, number, or phrase across your Excel workbook. Manually searching for each one is slow and risks missing data. The Find and Replace tool lets you change all matching values at once. This article explains how to use the Ctrl+H shortcut to perform bulk updates efficiently.
Key Takeaways: Find and Replace in Excel
- Ctrl+H: Opens the Find and Replace dialog box to the Replace tab instantly.
- Options >> button: Expands the dialog to search by formula, format, or within a specific sheet.
- Replace All button: Updates every matching cell in your search scope with one click.
What the Find and Replace Feature Does
Find and Replace is a core editing tool in Excel. It searches for text or numbers you specify and substitutes new values. You can use it on a selected range, an entire worksheet, or a whole workbook. The tool works on cell values, formulas, and comments. It also includes advanced options for matching case, entire cell contents, or specific formatting.
Before you start, identify the exact text you want to find. The tool is literal and will match the characters you type. For example, searching for “100” will not find the number 100.0 unless you use wildcards. Ensure your workbook is saved or changes are reviewed before using Replace All on a large dataset.
Steps to Use Find and Replace for Bulk Updates
Follow these steps to update values across your spreadsheet.
- Select your search scope
Click on a single cell to search the entire active sheet. To limit the search, first select the specific range of cells you want to update. - Open the Find and Replace dialog
Press Ctrl+H on your keyboard. The Replace tab of the Find and Replace dialog box will open immediately. - Enter the text to find and replace
In the “Find what” field, type the text or number you want to change. In the “Replace with” field, type the new text or number. - Use the Replace or Replace All button
Click “Replace All” to change every instance in your search scope at once. To review and change instances one by one, click “Find Next” and then “Replace”. - Confirm the changes
A message will show how many replacements were made. Click OK to close the message and review your updated data.
Using Advanced Find and Replace Options
Click the “Options >>” button in the Find and Replace dialog to access more controls.
- Set the search scope
Use the “Within” dropdown to search the active Sheet or the entire Workbook. - Search by rows or columns
Use the “Search” dropdown to choose “By Rows” or “By Columns” for the order of the search. - Match case or entire cell contents
Check “Match case” to find text with the exact capitalization. Check “Match entire cell contents” to find only cells that contain exactly your “Find what” text and nothing else. - Use wildcards for partial matches
Use the asterisk (*) to represent any number of characters. Use the question mark (?) to represent a single character. For example, “North*” finds “North”, “Northern”, and “Northwest”.
Common Mistakes and Limitations to Avoid
Find and Replace Changes Formulas Unexpectedly
Find and Replace works within formulas. Searching for “Cost” will also change the text “Cost” inside a formula like =SUM(Cost_Column). This can break your formulas. To avoid this, use the “Look in” dropdown in the advanced options and set it to “Values” instead of “Formulas”. This limits changes to displayed cell results.
Replace All Changed Too Many Cells
If your search term is too broad, you may update cells you did not intend to change. For example, replacing “cat” will also change “catalog” to “alog”. Always use “Find Next” first to review a few matches. For precise updates, use the “Match entire cell contents” option or include more specific text in your search term.
Cannot Find Numbers With Formatting
Find and Replace searches the underlying cell value, not its displayed format. You cannot find “$1,000” by typing the dollar sign and comma. You must search for the number 1000. To find cells based on their visual format, use the Format button next to the “Find what” box to specify the cell format you are looking for.
Find Next vs Replace All: Key Differences
| Item | Find Next / Replace | Replace All |
|---|---|---|
| Control | Reviews each match individually | Applies changes to all matches automatically |
| Speed | Slower, manual process | Instant, one-click operation |
| Risk | Low risk of unintended changes | High risk if search term is not precise |
| Best for | Small datasets or critical data review | Large, uniform datasets where changes are certain |
| Undo | Each change can be undone step-by-step | All changes are undone with a single Ctrl+Z |
You can now update hundreds of values in your spreadsheet with a few keystrokes. Use Ctrl+H to open the Replace dialog quickly. For more control, remember to click the Options button to match case or entire cells. Try using the wildcard asterisk (*) to find and replace partial text strings in product codes or names.