How to Find and Delete Rows Based on a Column Value in Excel

To find a column and delete rows below it in Excel, you need to use two main functions: MATCH and OFFSET. The MATCH function returns the relative position of a value in a range or array. The OFFSET function returns a reference to a range that is offset from a given reference by a specified number of rows and columns. By combining these two functions, you can find the column number that matches a certain value, and then delete the rows below that column.

Procedures

To find a column and delete rows below it in Excel using formulas, follow these steps:

1. Enter the value you want to find in a cell, for example, in A1.
2. In another cell, enter the formula =MATCH(A1,B1:Z1,0), where B1:Z1 is the range of column headers that contains the value you want to find. This formula will return the column number that matches the value in A1. For example, if A1 contains “Sales” and B1:Z1 contains “Product”, “Price”, “Sales”, and “Profit”, the formula will return 3, because “Sales” is in the third column of B1:Z1.
3. In another cell, enter the formula =OFFSET(B2,0,MATCH(A1,B1:Z1,0)-1,ROWS(B2:Z100),1), where B2:Z100 is the range of data that you want to delete rows from. This formula will return a reference to the column that matches the value in A1, starting from the second row. For example, if A1 contains “Sales” and B2:Z100 contains the sales data for different products, the formula will return a reference to C2:C100, which is the sales column.
4. Copy the formula in step 3 and paste it as values in another column, for example, in AA2. To do this, right-click on the cell where you want to paste the formula, and choose Paste Special > Values. This will convert the formula into values, so that you can delete the rows without affecting the reference.
5. Select the column where you pasted the values, for example, AA2:AA100. Right-click on the selection and choose Filter > Filter by Selected Cell’s Value. This will filter the data based on the values in the column, and hide the rows that have blank or zero values.
6. Select the visible rows that you want to delete, for example, AA3:AA100. Right-click on the selection and choose Delete Row. This will delete the rows that have non-blank or non-zero values in the column.
7. Turn off the filter by clicking on the Filter icon in the Data tab. This will show all the rows again, but the rows that you deleted will be gone.

Explanation

To understand how the formulas work, let’s look at an example. Suppose you have the following data in Excel, and you want to find the column that contains “Sales” and delete the rows below it that have sales less than 100.

First, you need to enter the value you want to find in a cell, for example, in A1. In this case, A1 contains “Sales”.

Next, you need to use the MATCH function to find the column number that matches the value in A1. The syntax of the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

where:

• lookup_value is the value you want to find.
• lookup_array is the range or array that contains the value you want to find.
• match_type is an optional argument that specifies how Excel matches the value. It can be 0, 1, or -1. 0 means exact match, 1 means less than or equal to, and -1 means greater than or equal to. If you omit this argument, Excel assumes 1 by default.

In this example, the formula in B2 is =MATCH(A1,B1:Z1,0), where:

• A1 is the value you want to find, which is “Sales”.
• B1:Z1 is the range of column headers that contains the value you want to find.
• 0 is the match type, which means exact match.

The formula will return the column number that matches the value in A1, which is 3, because “Sales” is in the third column of B1:Z1.

Then, you need to use the OFFSET function to return a reference to the column that matches the value in A1, starting from the second row. The syntax of the OFFSET function is:

=OFFSET(reference, rows, cols, [height], [width])

where:

• reference is the starting point of the reference.
• rows is the number of rows to move up or down from the reference.
• cols is the number of columns to move left or right from the reference.
• height is an optional argument that specifies the height of the reference, in number of rows.
• width is an optional argument that specifies the width of the reference, in number of columns.

In this example, the formula in C2 is =OFFSET(B2,0,MATCH(A1,B1:Z1,0)-1,ROWS(B2:Z100),1), where:

• B2 is the reference, which is the starting point of the data range.
• 0 is the number of rows to move up or down from the reference, which means no change.
• MATCH(A1,B1:Z1,0)-1 is the number of columns to move left or right from the reference, which is the column number that matches the value in A1 minus 1. This is because the reference is in column B, and we want to move to the column that contains “Sales”, which is in column C. So we need to move one column to the right, which is equivalent to subtracting 1 from the column number.
• ROWS(B2:Z100) is the height of the reference, which is the number of rows in the data range. This is because we want to include all the rows below the column header in the reference.
• 1 is the width of the reference, which is one column. This is because we want to return a reference to a single column.

The formula will return a reference to the column that matches the value in A1, starting from the second row, which is C2:C100.

Next, you need to copy the formula in C2 and paste it as values in another column, for example, in AA2. To do this, right-click on the cell where you want to paste the formula, and choose Paste Special > Values. This will convert the formula into values, so that you can delete the rows without affecting the reference.

Then, you need to select the column where you pasted the values, for example, AA2:AA100. Right-click on the selection and choose Filter > Filter by Selected Cell’s Value. This will filter the data based on the values in the column, and hide the rows that have blank or zero values. This is because the OFFSET function returns a blank value if the reference is outside the worksheet, and a zero value if the reference is not a number.

After that, you need to select the visible rows that you want to delete, for example, AA3:AA100. Right-click on the selection and choose Delete Row. This will delete the rows that have non-blank or non-zero values in the column.

Finally, you need to turn off the filter by clicking on the Filter icon in the Data tab. This will show all the rows again, but the rows that you deleted will be gone.

Example

To illustrate the result of the above steps, let’s use the following scenario. Suppose you have the following data in Excel, and you want to find the column that contains “Sales” and delete the rows below it that have sales less than 100.

After applying the steps above, the data will look like this:

As you can see, the rows that have sales less than 100 have been deleted.