Excel Power Query: Remove Rows Based on Cell Values

How-to basics · Updated November 21, 2026

To remove rows based on a value in Excel Power Query, open the column’s filter menu, clear the value you want to exclude, and select OK. Power Query records that choice as a repeatable filter step, so the excluded rows stay out when you refresh the query.

How value-based row removal works

Power Query is Excel’s data-import and transformation tool. Instead of deleting records from the original worksheet or source file, you describe the changes to make in the query editor. When you refresh, Power Query reads the source again and applies those steps in order. This makes filtering useful for recurring reports: you can exclude test records, cancelled orders, inactive customers, or any other unwanted values without changing the source data.

For a simple exclusion, the column filter is usually the easiest method. Power Query displays distinct values for that column, and you choose which ones to keep. The resulting step filters out the unchecked values. You can later edit or remove the step, and the original source remains unchanged.

Remove rows with a specific value

In Excel for Microsoft 365 on Windows, start by selecting a cell in your source table and choosing Data > From Table/Range. If the data is not yet formatted as a table, Excel will prompt you to create one. In the Power Query Editor, use the filter on the column that contains the value:

  1. Open the column’s filter dropdown.
  2. Clear Select All, then select the values you want to keep. To exclude just one value, leave the other values selected and clear the unwanted one.
  3. Select OK and review the preview to confirm the rows you expected to remove are gone.
  4. Choose Home > Close & Load to return the result to Excel.

For example, if a Status column contains Open, Complete, and Cancelled, clear Cancelled while leaving Open and Complete selected. The query output will contain the first two statuses. If the list is long, use the dropdown’s search box to locate the value before changing its selection. The filter applies to the column’s values, not to the original worksheet cells.

Remove blank, null, or error rows

Blank-looking entries can have different causes. A cell may be genuinely empty, contain a null value from the source, or contain an empty text string. These may not all behave identically in filters. Open the column dropdown and inspect the available entries; deselect the blank or null entry if it appears. For a whole row that is empty across the table, use Home > Remove Rows > Remove Blank Rows. That command is different from removing rows where one particular column is blank: filter that column instead when other fields in the row still contain data.

Errors are another case. To remove rows with errors in a selected column, use Home > Remove Rows > Remove Errors. Be careful: this discards the entire row from the query result, even if the other columns have useful information. If the error points to a data-quality issue you need to investigate, consider keeping the row and correcting or replacing the problematic value instead.

Choose the right filter for text, numbers, and dates

The filter dropdown adapts to the column’s data type. Text columns can be filtered by listed values or with text conditions such as equals, begins with, or contains. Number columns offer number conditions, and date columns provide date-based conditions. For instance, to remove every record with a quantity below 1, filter the Quantity column to keep values greater than or equal to 1. To exclude dates before a cutoff, keep dates on or after that date.

Check the column’s data type before applying a condition. A date stored as text may be sorted alphabetically or fail to offer date filters. In the editor, select the column and choose the appropriate type from the data type control, such as Date or Whole Number. If conversion produces errors, inspect the source format and regional date conventions before removing error rows; the conversion may be the real problem.

Filter by multiple values or conditions

You can exclude more than one listed value by clearing several entries in the same dropdown. For example, to keep only active transactions, you might clear Cancelled and Test. This is convenient when the list is manageable and the exact values are known.

For conditions that involve more than one column, apply filters to each relevant column in turn. Suppose you want to remove rows where Status is Cancelled and Region is West. Filter Status to exclude Cancelled, then filter Region to exclude West. Applying both filters keeps rows that satisfy both remaining selections; it excludes any row failing either filter. This matters because combining filters across columns generally narrows the result more than excluding a single combination. If your requirement is specifically to remove only rows where both conditions are true, use a custom rule rather than blindly filtering each column. Check the resulting sample rows against the intended logic.

To filter using a range or a rule that is awkward to express through the dropdown, choose Home > Advanced Editor to inspect or edit the query’s M code. Make a small change at a time, and verify the preview after each edit. The ordinary filter controls are often safer for occasional users because Power Query writes the corresponding code for you.

What the M code means

Each filter appears as a step in the query. For a table with a Status column, a typical M expression for excluding Cancelled is Table.SelectRows(#"Changed Type", each ([Status] <> "Cancelled")). The step keeps rows for which the condition is true. The preceding step name, here Changed Type, must match the actual name in your query. Power Query may create a different step name, so do not paste an example without checking that reference.

Nulls need particular attention when you edit M directly. A comparison involving null does not behave like an ordinary comparison between two text values. If you want to exclude Cancelled but keep rows where Status is null, use an explicit condition such as Table.SelectRows(#"Changed Type", each ([Status] = null or [Status] <> "Cancelled")). Confirm the preview, especially when the source includes missing values.

Text can also differ by capitalization or surrounding spaces. Values such as Cancelled, cancelled, and Cancelled followed by a space may not be identical for a direct comparison. If these variations occur, clean or standardize the column before filtering—for example, trim whitespace and use a consistent case—then check that the cleanup does not merge values that should remain distinct.

Keep the filter working after refresh

When you select Close & Load, the filtered output is loaded to a worksheet or another destination. To rerun the query, use Data > Refresh All, or right-click the query output and choose Refresh. Power Query reapplies the saved steps to the latest source data. Newly added rows with the excluded value will therefore be filtered out too, provided they are included in the source range or table.

If you expect new source rows, use an Excel Table rather than a fixed range where practical. Tables expand as rows are added, which makes them a dependable source for many workbook queries. A query connected to an external file or database depends on that source being available and on the connection’s permissions and settings. If refresh fails, check the source path, access, and any prompts before assuming the filter is broken.

Common problems and how to fix them

  • The unwanted value still appears: Confirm the correct column was filtered, then check for spelling, spaces, capitalization, or a second representation of the value.
  • More rows disappear than expected: Review each applied filter in the Query Settings pane. Filters on separate columns combine, so together they can exclude records beyond the one combination you intended.
  • New rows do not appear after refresh: Confirm the source table or range includes them and that the query is refreshing the expected source.
  • Blank records remain: Determine whether the cells are null, empty strings, or merely look blank because of formatting. Filter the specific column or clean its values as needed.
  • A type conversion creates errors: Check the source values and locale before using Remove Errors. Removing errors hides those records from the result rather than fixing their underlying data.

Power Query versus deleting rows in the worksheet

Deleting rows directly in Excel changes the worksheet data and may be difficult to repeat safely when the source is refreshed or replaced. Power Query keeps the transformation separate: the source stays intact, the filtering steps are visible, and the result can be rebuilt. That is usually preferable for recurring imports and reports.

Worksheet filters, by contrast, hide rows in the current sheet without creating a reusable import-and-cleaning process. They can be quicker when you only need to inspect a table temporarily. Choose Power Query when the same exclusion needs to happen consistently on refreshed data; choose an ordinary worksheet filter for a quick view that does not need to become part of a repeatable transformation.

Frequently Asked Questions

Can I remove rows where a cell equals a value?
Yes. In Power Query Editor, open that column’s filter dropdown and deselect the value. The query retains the other selected values and excludes matching rows when it loads or refreshes.

How do I remove rows that contain a word?
Open the text column’s filter menu and use Text Filters, then choose a condition such as Does Not Contain. Review the preview, since a contains rule can exclude values with the word anywhere in the text, not only exact matches.

Can I exclude several values at once?
Yes. Clear each unwanted value in the same column’s dropdown, or use an appropriate text or number condition. If the rule spans columns or depends on a particular combination, verify the logic in the preview.

Does removing rows in Power Query delete them from the source?
No. It excludes rows from the query result; it does not delete them from the original worksheet, file, or database. The source remains available unless you separately edit it.

Why did my filtered rows return after refreshing?
A correctly saved Power Query filter should be reapplied on refresh. If rows return, check whether you filtered a different query or column, whether the filter step was removed, and whether the refreshed output is the same query result.

Are the steps different on Mac or Excel for the web?
This walkthrough targets Excel for Microsoft 365 on Windows. Power Query availability and the editor’s features can vary by Excel version and platform, so menu locations or supported connections may differ on Mac and in Excel for the web. If a command is missing, use the desktop editor available for your version.

Related

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.