Excel Power Query Find and Replace: Steps and Tips

To find and replace data in Excel Power Query, open the query, select the column, then choose Transform > Replace Values and enter the text or value to find and its replacement. Power Query records the change as a query step, so it is applied again when the query refreshes; it does not directly edit the original source data.

What Find and Replace Means in Power Query

Power Query is Excel’s tool for importing, cleaning, and reshaping data before loading it into a worksheet or data model. Its replacement feature is part of the query transformation process. It is useful when the same correction must be repeated reliably—for example, changing inconsistent product labels in every monthly file that feeds a report.

This differs from Excel’s ordinary worksheet Find and Replace. On a worksheet, a replacement changes cell contents in the workbook. In Power Query, a replacement changes the output produced by the query. The source file, database, or table remains unchanged unless you separately edit that source. This separation is useful because it keeps the original data available and makes cleanup steps visible and repeatable.

Power Query does not work as a single search box that scans every table and every column in the workbook. The normal workflow is to choose a query and one or more columns, then apply a replacement to those columns. If you need to search for a value first, inspect the relevant column in the preview or filter it; the replacement itself is configured through the column transformation.

How to Replace a Value in a Query

In Excel for Microsoft 365 on Windows, open the workbook and go to Data > Queries & Connections. In the pane, double-click the query you want to edit. You can also use Data > Get Data to open the Power Query Editor when creating or editing a query.
In the Power Query Editor preview, select the column header for the field that contains the value. To apply the same replacement to multiple columns, select the relevant headers together, if the replacement is appropriate for each selected column.
Choose Transform > Replace Values. The same command is commonly available by right-clicking a selected column header and choosing Replace Values.
Enter the existing value in Value to Find and the new value in Replace With, then confirm the dialog. Check the preview to see how the rows changed.
Choose Home > Close & Load to return the query results to Excel. If the query is already loaded, use Close & Load or Close & Load To, as appropriate for its existing destination.

For example, imagine a column named Department contains the label “Cust. Service” in several rows. Replacing it with “Customer Service” standardizes the label in the query output. If the source is refreshed with another file containing “Cust. Service,” the recorded replacement is run again. It will not automatically correct a new spelling such as “Customer Svc”; that is a different value and needs its own rule.

These instructions describe the Windows editor in Microsoft 365. Power Query is also available in some Microsoft 365 editions of Excel for Mac, but supported connectors and the editor interface can differ, so ribbon labels or availability may not match exactly. Excel for the web also has a different feature set; if the query cannot be edited in the browser, open the workbook in desktop Excel.

Exact Matches Versus Partial Text Matches

A key choice is whether the replacement should affect only a whole cell value or every occurrence of a piece of text. For example, replacing “North” with “N” could change a cell whose entire value is “North,” but a partial-text replacement could also alter “Northwest” or “North Road.” A broad replacement can therefore introduce errors even when the replacement looks reasonable in the dialog.

When the Replace Values dialog offers Match entire cell contents under its advanced options, select it when only complete cell values should match. Leave it off when you intentionally want to replace a text fragment inside longer values. Availability and wording can vary with the selected data type and Excel build, so inspect the dialog and verify the preview rather than assuming a particular matching mode.

Whole-value cleanup: Replace “N/A” with “Not provided” when the cell contains exactly “N/A.”
Substring cleanup: Replace “Co.” with “Company” in names only if every occurrence is intended to change.
Risky abbreviation: Replacing “St” with “Street” can damage names such as “Stella” if the operation matches part of a longer value.

Text replacement is generally case-sensitive in Power Query’s text operations. If the source has variants such as “acme,” “ACME,” and “Acme,” one replacement may not standardize all forms. A practical approach is to normalize the case first using a text transformation such as Transform > Format > UPPERCASE or lowercase, if that suits the data, or to add separate replacement steps for the variants. Test representative records before applying a rule broadly.

What Happens When You Refresh

Every transformation appears as a step in the query’s Applied Steps list. A replacement step is usually added after earlier steps, such as selecting columns or changing data types. When you refresh the query, Power Query reads the current source and runs the saved steps in order. That is why the replacement persists across refreshes without modifying each new source file.

Step order matters. If the replacement is created before a later step that changes the column’s type, the later operation may affect how the output is represented. If an earlier step removes the column, the replacement step can fail because its target no longer exists. You can select a step in Applied Steps to inspect the preview at that point, or use the gear icon beside a step when available to reopen its settings. The X beside a step removes it; deleting a step may change every later result.

Power Query may also use query folding for some data sources, meaning it can translate transformations into operations performed by the source system. Whether a replacement folds depends on the connector and transformation. For an ordinary Excel table or text file, the transformation is generally handled as part of Power Query’s processing. In either case, the replacement is a query instruction, not a permanent edit to the source.

Replacing Values in Several Columns

You can select more than one column before choosing Transform > Replace Values. This is convenient when the same typo occurs in several fields, such as “Unkown” appearing in both City and Delivery City. Only select columns where the value has the same meaning and the replacement is safe. Replacing “0” across a price, postal code, and status column, for instance, could produce inappropriate or type-changing results.

For more complex rules, use the formula bar or Home > Advanced Editor to inspect the M code behind a step. A text replacement step can resemble this expression, where the prior step is named Changed Type and the target field is Company:

Table.ReplaceValue(#"Changed Type","Ltd.","Limited",Replacer.ReplaceText,{"Company"})

In this example, Replacer.ReplaceText specifies text replacement, and the list at the end identifies the column to transform. Power Query can also use Replacer.ReplaceValue for value-level replacement. Normally, let the interface create the step unless you have a reason to edit M; a misspelled prior-step name or column name can make the query fail.

Common Problems and How to Avoid Them

The value does not change: Check that the right query and column are selected, that the spelling and spaces match, and that the data type is suitable. A trailing space can make “Blue” different from “Blue ”. Consider applying a trim transformation before replacing values.
More rows change than expected: Check whether the operation is matching part of the cell rather than the whole value. Use the whole-cell option if available, narrow the selected columns, and review the preview before loading.
Only one capitalization changes: Treat capitalization variants as separate cases or normalize the text first. Do not assume a case-insensitive search.
The query errors after a source update: Confirm the source still has the expected column name and that earlier steps have not renamed or removed it. A replacement step depends on the column it targets.
A blank result is not a true null: An empty text string and a Power Query null value are different. A replacement that removes text can leave an empty string, which may behave differently from a missing value in filters and later calculations.
The worksheet did not change immediately: Editing the query changes its definition; use Close & Load and refresh as needed to update the loaded output. Check the query’s load destination if the result is not on the sheet you expected.

For data-quality work, make a copy of the workbook before changing a complicated query, especially if later steps depend on the cleaned text. Power Query’s Applied Steps help you revise the transformation, but a workbook copy makes it easier to compare the original output and recover the prior setup.

Replace Values, Replace Errors, and Worksheet Find

Replace Values handles values or text in a selected column. It is not the same as Replace Errors, which is intended for cells that contain Power Query errors. To substitute a readable label for an error, select the relevant column and use Transform > Replace Errors when available. If you are trying to fix the cause of an error, inspect the preceding steps instead; replacing the visible error can hide a conversion or source-data problem.

Excel’s worksheet Find and Replace is better when you want to change cells already loaded into a sheet and do not need the change to be a repeatable query step. In Excel on Windows, the usual shortcut is Ctrl+H; on Mac, use Control+H in many Excel versions, though keyboard mappings can vary. Worksheet replacement changes the selected worksheet cells, while Power Query replacement changes query output when the query runs. Choose based on whether you want a one-time sheet edit or reusable data-cleaning logic.

Frequently Asked Questions

Can Power Query find and replace across an entire workbook?
Not as one global operation. Open each relevant query, select the columns to transform, and add the replacement step. For a one-time search across worksheet cells, use Excel’s regular Find and Replace instead.

Does Power Query change the original Excel or CSV file?
No. A replacement normally changes the query’s output, not the source file. To change the original data, edit the source separately or use an appropriate write-back process outside the normal Power Query transformation workflow.

Will a replacement still work after I refresh?
Yes, provided the query can still access the source and the target column and step dependencies remain valid. Power Query reapplies the saved transformation to the refreshed data; newly introduced variants that do not match the rule remain unchanged.

How do I replace part of a text value rather than the whole cell?
Select the text column, choose Transform > Replace Values, and leave Match entire cell contents unchecked if that option appears. Check the preview for unintended changes inside longer words or names.

Can I replace Power Query errors with a value?
Yes. Use Transform > Replace Errors on the affected column to assign a replacement for error cells. If the errors point to an underlying issue, such as text that cannot convert to a number, correcting the source or conversion step may be better than masking them.

Choosing the Right Approach

Use Power Query’s Replace Values when cleanup should be documented, repeatable, and applied each time data refreshes. Use worksheet Find and Replace for direct edits to existing cells, and use Replace Errors for actual error values rather than ordinary text. In all cases, the safest habit is to select only the columns that need attention, decide whether the match must cover a whole cell or a substring, and inspect the resulting preview before loading the query.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.