In this guide, we're going to show you how to remove all rows containing specific value in Excel. We are going to be taking a look at two methods:
- Find and Replace
Removing all rows containing specific value using Find and Replace
- If you wish to perform removing a specific range, select your data, including the values you want to remove. Otherwise, you do not need to select any cells.
- Press the Ctrl + F key combination to open Find and Replace.
- Type in the value in the rows you want to remove.
- Click Find All.
- Select any of the found values in the results pane.
- Press Ctrl + A to select all of them. You can select specific values you want to remove by using Ctrl or Shift keys.
- Close the Find and Replace window.
- Press Ctrl + - to open Delete
- Select Entire row
- Click OK button to delete those rows.
After clicking OK, Excel will remove all rows containing the selected cells.
Removing all rows containing specific value using Filter
This approach is more suitable for data in a table structure.
- Select your data, including the values you want to remove.
- Press Ctrl + Shift + L to enable Filters. Alternatively, you can click Filter in Data tab.
- Click the down arrow in the header of the column that contains the value you want to select.
- Select the value(s) you want to remove in the filter dialog.
- Click OK to apply the filter.
- Select each cell individually by clicking cells while holding down the Ctrl key. Don’t use the Shift key or drag the mouse to select cells.
- Once the cells are selected, press Ctrl + -.
- Click OK on prompt dialog to delete rows.
Clear the filters to see the remaining rows.