How to delete rows above active cell or specific text in Excel

There are several ways that an Excel user can delete rows if a cell contains a specific text, as explained in this article. At times, when you work with large data sets, you might find that you have to delete rows based on the values of the cells or based on conditions found within the data set.

Filter Rows based on Value/Condition and then Delete them

One of the best ways to delete rows that contain a specific value or fulfill a given condition is to filter these. Once you have the filtered data, you can delete all these rows and the remaining rows remain the same.

Excel filer can be filtered based on many criteria like text, numbers, dates, etc.

Delete Rows that contain a specific text

From the below data, you want to delete all the rows where the country is UAE in column B.

You can choose to delete rows manually, however, in a few cases, data will be huge where deleting rows manually won’t be the option. In that case, filter all records where the county is UAE and delete all their rows.

Below are the steps to delete rows based on the values.

Step 1

On the Excel sheet home page, you will find the “Sort & Filter” group, click on the filter icon. This will apply a filter to the header cells. Select the header before applying the filter.

Step 2

Click on the filter icon in the country header cell (downward pointing triangle icon).

Step 3

Select all the options and deselect UAE. This will filter and shows the UAE country.

Step 4

Click ok and select all filtered records, Right-click on any of the selected cells and click Delete Row.

Step 5

In the dialog box, click OK. You will see no records in the dataset.

Step 6

Click on the header and click on the filter icon. This will remove the filter and you will see all the records except the deleted ones.

In case you have a lot of categories, you can type the name in the field right above the box that has country names, Excel will show you only those records that match entered text. Once you have selected the required name hit the OK button.

NOTE

When you delete a row, anything that you may have in other cells in their rows will be lost. One way to get around this is to create a copy of the data in another worksheet and delete the rows in the copied data. Once done, copy it back in place of the original data.

Delete Rows Based on a Numeric Condition

In the same way, you can delete rows with sales less than 200 by selecting header and then filter icon, select down-ward button right to the sales then select Number Filter there you will find the option less than, select it a dialog box will pop up enter the value and click on OK.

It will show records of less than 200. Select the filtered records and rightclick, you will see the Delete Row option click on it, and then click on OK.

Below are the screenshots for reference