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
- Filter
These methods are good for removing all rows containing a specific value, such as repeating headers.
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.
Warning: If found and selected items contain an Excel Table header, the Ctrl + - shortcut will not work.
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.