To replace data in Excel, press Ctrl+H, enter the existing content in Find what and the new content in Replace with, then choose Replace or Replace All. For a safer bulk change, select the intended range first and check the matches before replacing everything.
What “Replace” Means in Excel
Replacing data can mean several different things. You might change one word in a list, correct a repeated typo, update a code throughout a workbook, alter the text returned by a formula, or transform data every time a source file is refreshed. Excel offers different methods for these jobs, and they do not all behave the same way.
Find and Replace is usually the right choice for a one-time edit to existing cell contents. A formula such as SUBSTITUTE is better when you want to keep the original data and show a changed version in another cell. Power Query is useful for repeatable cleaning steps during data import. Choosing the method first helps avoid overwriting source information or changing formulas by mistake.
Replace Text or Numbers with Find and Replace
In Excel for Microsoft 365 on Windows, open the workbook and press Ctrl+H. You can also go to Home > Find & Select > Replace. Enter the current text, number, or fragment in Find what, and enter the replacement in Replace with. Use Find Next to inspect a match, Replace to change that match, or Replace All to apply the change throughout the selected search scope.
- Save a copy of the workbook if the change is broad or difficult to reverse.
- Select the cells to search, if the change should apply only to a particular range. If no range is selected, Excel searches the scope shown in the dialog.
- Open Replace and enter the old and new content. Leave Replace with blank only when you intend to remove matching content.
- Choose Find Next to check a match and Replace to make a controlled edit, or use Replace All for a verified bulk change.
- Read Excel’s report of how many replacements were made, then inspect important results and save the workbook.
On Mac, the ribbon commands are similar, and you can use Edit > Find > Replace. The keyboard shortcut differs from Windows and can depend on the keyboard setup, so use the menu if Ctrl+H does not open the replacement dialog. Excel for the web also provides find-and-replace controls, although the available options can differ from the desktop app.
Control Where Excel Searches
The search scope matters as much as the replacement text. A replacement can be limited to a selected range, cover the current worksheet, or extend across the workbook, depending on the selection and the Within setting in the dialog. If the goal is to rename a label in one table, select that table before opening Replace and confirm that the scope is not set to Workbook.
The dialog’s options also control how Excel searches. Within sets the scope, such as Sheet or Workbook. Search determines whether Excel moves by rows or columns. Look in lets you choose what to search, such as formulas or values; the exact choices can vary with the Excel version and context. For example, looking in formulas may find text that appears inside a formula, while looking in values focuses on the displayed or calculated result.
Before a workbook-wide replacement, consider whether the same text appears in unrelated worksheets, titles, notes, or formulas. Changing a shared label across several sheets can be useful, but a short search term such as “May” could also occur in names or longer words. A quick test on one match can reveal whether the scope and search settings are appropriate.
Use Exact Matches, Wildcards, and Formatting Carefully
Match case makes uppercase and lowercase letters significant. Match entire cell contents prevents Excel from matching a search phrase that is only part of a cell. For example, searching for “East” without the whole-cell option might find both “East” and “Northeast”; enabling the option restricts the match to a cell whose entire content is “East.” Choose based on whether you want to update a complete label or a fragment within text.
Excel’s Find and Replace supports wildcards for pattern matching. An asterisk (*) represents any number of characters, and a question mark (?) represents one character. A tilde (~) before a wildcard searches for the literal character instead: use ~* to find an actual asterisk. Wildcards can make a broad replacement powerful, so test the pattern with Find Next and avoid Replace All until you understand which cells it matches.
The Format button in the dialog can also search for or replace cell formatting, such as a fill or font format. This is useful when the problem is visual rather than textual—for instance, correcting a repeated format applied to selected cells. Formatting replacements can have wide effects too, so check the chosen format criteria before applying them across a sheet.
Replace Text Inside a Formula or Keep the Source Data
Find and Replace can change formula text as well as ordinary cell contents when the search is set to look in formulas. That may be intentional—for example, changing a repeated text label embedded in formulas—but it can also alter a formula’s logic or references. If you want to change the displayed result without editing the source cell, use a separate formula instead.
With the original text in A2, enter this in B2 to change every occurrence of “North” to “South” in the returned text:
=SUBSTITUTE(A2,"North","South")
If A2 contains North Zone, B2 returns South Zone. SUBSTITUTE looks for matching text, rather than a character position. It leaves the value in A2 unchanged, which makes it easier to compare the original and revised data before deciding whether to replace the source.
REPLACE works differently: it changes a specified number of characters starting at a specified position. With the text ID in A2 and the replacement formula in B2, this changes four characters beginning at position 4:
=REPLACE(A2,4,4,"2089")
If A2 contains AB-1047, B2 returns AB-2089: the first three characters remain, and the four characters from position 4 onward are replaced. This position-based method is appropriate for consistently structured codes, not for finding a word wherever it appears. Both formulas return results in their own cells. To make those results permanent source values, copy the result cells and use Paste Special > Values; check the output first because this replaces formulas with fixed values.
Use Power Query for Repeatable Data Cleaning
If you import the same kind of data repeatedly, Power Query can record a replacement as a transformation step. In Excel, load or open the data in the Power Query Editor, select the relevant column, and use Transform > Replace Values. Enter the value to find and the replacement value, then close and load the query when the transformation is ready.
Unlike a one-time Find and Replace in worksheet cells, a Power Query step is part of the query and can run again when the source is refreshed. For example, a recurring customer file may use “N/A” to represent a missing region. A query can replace that marker consistently during each refresh, while leaving the raw source file untouched. Confirm the data type and the selected column: replacing a text value in a numeric column, or applying the step to the wrong column, can produce unwanted results.
Common Replacement Problems to Avoid
- Replacing too much: A short term may appear inside unrelated words or on other sheets. Narrow the range, enable Match entire cell contents when suitable, or inspect results one at a time.
- Changing formulas accidentally: If the search is set to formulas, replacement can edit formula text. Check Look in and consider testing on a copy before a workbook-wide action.
- Removing content unintentionally: An empty Replace with field deletes the matched text. This is useful for removing a known marker, but can leave incomplete labels or identifiers.
- Confusing displayed numbers with stored values: A cell may display a rounded number or a formatted date while storing a different underlying value. Test the replacement and inspect the resulting cell, especially when number formats matter.
- Assuming every replacement is equivalent: A text replacement, a positional change, a formatting fix, and a repeatable import cleanup are separate tasks. Use the method suited to the data and desired outcome.
For a large or important change, make a backup, run a small test, and compare a sample of results with the original. Excel usually allows an immediate Undo after a replacement, but relying on Undo is not a substitute for a copy: other edits after the replacement can complicate recovery.
Which Replacement Method Should You Choose?
Use Find and Replace when you need to edit existing cells once, such as correcting “recieve” to “receive” in a selected worksheet. Use Replace for a short list of confirmed matches and Replace All only when the search scope and match rules are clear. Use SUBSTITUTE when you need a formula-driven version of text with a particular word changed, or REPLACE when the position and number of characters are known. Use Power Query when the cleanup must be repeated as new data is imported.
If you only need to remove or overwrite a whole cell, typing the new value directly may be simpler than using Replace. If the change affects a unique identifier, a formula, or a large workbook, preserve the original data until you have checked the result. A clear before-and-after sample is often the fastest way to discover a mistake before it spreads.
Frequently Asked Questions
What is the shortcut for Replace in Excel?
In Excel for Microsoft 365 on Windows, Ctrl+H opens Find and Replace. On Mac, use Edit > Find > Replace if the shortcut is not familiar or differs on your keyboard.
How do I replace a word in only one column?
Select the cells in that column that you want to change, open Replace, and check the search scope before proceeding. Use Find Next first to confirm that Excel is matching the intended cells, then choose Replace or Replace All.
Can I replace a value throughout an entire workbook?
Yes. In the Replace dialog, set Within to Workbook if that option is available, then review matches across sheets before choosing Replace All. This can also change text in unrelated areas or formulas, so save a copy and use a distinctive search term.
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE finds specified text and changes its occurrence; REPLACE changes a set number of characters beginning at a chosen position. Use SUBSTITUTE for text such as a word in a sentence, and REPLACE for fixed-position strings such as consistently formatted codes.
Can I undo Replace All?
Ctrl+Z on Windows generally undoes the most recent replacement action if you have not performed intervening actions. On Mac, use the Undo command or the keyboard shortcut shown in Excel. For high-impact changes, save a backup before replacing data rather than depending on Undo.
Related
- Excel Not Equal Sign: How to Use the <> Operator
- Excel Not Equal: The <> Operator Explained
- Excel Arrow Keys Not Moving Between Cells? Quick Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert Alternating Color Rows in Excel EasilyTo insert alternating color rows in Excel, select your data, choose Home > Format as Table, pick a…
- Excel Dynamic Title on Box Plot: Link It to a CellTo make an Excel box plot title dynamic, put the title text or a formula in a worksheet…
- Absolute Reference in Excel Without F4: Manual MethodsTo make an Excel reference absolute without pressing F4, type a dollar sign before both the column letter…
- How to Copy Text With Track Changes in Microsoft WordTo copy text with its existing tracked edits, show all markup in the source document, select the passage,…
- Highlight Duplicate Names in Excel: A Step-by-Step GuideTo highlight duplicate names in Excel for Microsoft 365 on Windows, select the cells containing the names, then…
- Remove Duplicate Names in Excel: Step-by-Step InstructionsTo remove duplicate names in Excel, select the name list or the entire related dataset, then choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.