Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Delete Multiple Columns in Excel with Condition

In Microsoft Excel, we can delete the adjacent columns easily. But when it comes to deleting multiple columns based on condition, many of you may find it puzzling. Hence, in this article, I will discuss how to delete multiple columns in Excel with a condition.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Useful Methods to Delete Multiple Columns with Conditions in Excel

In this section, we will demonstrate 3 effective methods on how to delete Multiple Columns in Excel with condition. For illustration purposes, I have taken a data set that contains some information on employees of “X” company.

how to delete multiple columns in excel with condition

Now, our goal is to delete some columns based on a condition. For example, I will delete those columns which have Negative Rh Factor. To accomplish that, let’s look at the first method.


1. Use of Find & Replace Tool to Delete Multiple Columns with Condition

Here, we will use the built-in Find & Replace tool to look for cells that contain specific data (Negative) and later delete the entire columns that contain those cells. To do that, follow the steps below.

Steps:

  • First, select the row on which the condition will be checked. Here, I have selected the Rh Factor column as I will find the condition (Negative) here.

Use of Find & Replace Tool to Delete Multiple Columns with Condition

  • After that, press Ctrl+F to open up the Find & Replace Dialogue box.

  • Now, on the Find what box, write down the conditional word (“Negative”) and click Find All.

  • Consequently, you will see the list of cells that contains this word. Now, press Ctrl+A to select all those cells and then close the window.

Use of Find & Replace Tool to Delete Multiple Columns with Condition

  • Now, you should see that every cell that satisfies the condition has been selected and highlighted.
  • Then, Hold the Ctrl key and press the key to open the Delete dialogue box.

  • Here, you have to select the Entire column and then click OK to delete the columns that contain those cells.

Read More: How to Delete Extra Columns in Excel (7 Methods)


2. Deletion of Multiple Columns in Excel with Blank Cells

If your data set contains blank cells like the example below and you want to delete those entire columns that contain blank cells, then this method is dedicated to dealing with this problem.

Deletion of Multiple Columns in Excel with Blank Cells

Here, we will use the Go to Special feature to accomplish our goal. To know more, follow the steps below.

Steps:

  • First, select the data set and then click the F5 key to open the Go to dialogue box. From there, click on Special.

Deletion of Multiple Columns in Excel with Blank Cells

  • As a result, the Go to Special window will open up. From there, select Blanks and then click OK.

  • Consequently, you will see that the cells that are blank have been highlighted.

Deletion of Multiple Columns in Excel with Blank Cells

  • Now, similar to the previous method, press Ctrl and keys to open the Delete And then select Entire Column. Eventually, you will have the final result.

Read More: Delete Blank Columns in Excel (3 Ways)


Similar Readings


3. Utilize Sort & Filter Feature to Delete Multiple Columns in Excel Based on Condition

A useful alternative can be the use of the Sort option to delete multiple columns based on conditions. Here, we will first sort out the data and put the condition-fulfilled cells in a group, and then manually delete those. To know more, follow the steps below.

Steps:

  • Before using the Sort feature, we have to create a helper row to store the serial number of the cells.

  • Now, first, select the data set ( I am selecting excluding header).

Utilize Sort & Filter Feature to Delete Multiple Columns in Excel Based on Condition

  • Now, go to the Data tab and select Sort from Sort & Filter group.

  • After that, on the Sort window, click on Options.

Utilize Sort & Filter Feature to Delete Multiple Columns in Excel Based on Condition

  • Afterward, a new window will open. From here, chose Sort left to right and click OK.

Utilize Sort & Filter Feature to Delete Multiple Columns in Excel Based on Condition

  • Now, from the Sort by drop-down menu, select the row number based on which the data will be sorted. Here, I have selected row 7(the Rh factor is in row 7). Then click OK.

Utilize Sort & Filter Feature to Delete Multiple Columns in Excel Based on Condition

  • As a result, you will see that the data table is now sorted out and the Negative Rh factor-containing cells are on one side. Now, select those cells and delete the Entire column as discussed in the previous methods.

Utilize Sort & Filter Feature to Delete Multiple Columns in Excel Based on Condition

  • The final result will be like this.

Utilize Sort & Filter Feature to Delete Multiple Columns in Excel Based on Condition

  • As we can see, the serial number is okay here. If the serial number is not okay, we have re-sort the data table according to the Serial Number. After that, you may delete the row.

Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)


How to Delete Rows Based on Conditions

The process of deleting rows based on condition is very much similar to deleting columns mentioned above. To illustrate, I have taken another dataset below which is essentially transposed version of the previous data set.

How to Delete Rows Based on Conditions

Here, the employees are divided into 3 groups(A, B & C). Now, our task is to delete the rows that contain group C. To do that, we can use the Filter option. Follow the steps below to know more.

Steps:

  • First, select the entire data set and select the Filter from the Data tab.

How to Delete Rows Based on Conditions

  • As a result, you will see that the header column will have a filter icon. Now, click on the filter icon of Group and then select Sort A to Z. After that, click OK.

  • As a result, you will see that the list is now sorted and all the rows containing Group C are at the bottom.

How to Delete Rows Based on Conditions

  • Now, select those three cells and press ctrl then to open the Delete

How to Delete Rows Based on Conditions

  • From there, select the Entire row and click OK. Consequently, those entire 3 rows will be deleted.

How to Delete Rows Based on Conditions

Read More: How to Delete Columns Based on Header Using VBA in Excel


Things to Remember

  • If you have large data set, use the 1st and 2nd methods as in the 3rd method, manual selection can be cumbersome.
  • In the 3rd method, after rearranging the data set, you can delete the helper row which stores the serial number.

Conclusion

That is the end of this article. If you find this article helpful in understanding how to delete multiple columns in Excel with condition, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.


Related Articles

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, and explore new things. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo