How to Delete Multiple Columns in Excel with Condition

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Delete Multiple Columns in Excel with Condition: 3 Methods

In this section, we will demonstrate 3 effective methods for how to delete multiple columns in Excel with conditions. 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 with a 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 and Replace tool to look for cells that contain specific data (negative) and later delete the entire columns that contain those cells.

Steps:

  • First, select the row on which the condition will be checked. Here, I have selected the Rh Factor row 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 and Replace dialogue box.

  • On the Find what box, write down the conditional word Negative and click Find All.

  • You will see the list of cells that contain 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 Every Other Column in Excel


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

  • The Go to Special window will open up. From there, select Blanks and then click OK.

  • 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. You will get the final result.


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

The Sort option is a useful alternative 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.

Steps:

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

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

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

  • Go to the Data tab and select Sort from the Sort & Filter group.

  • On the Sort window, click on Options.

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

  • A new window will open. From here, choose Sort left to right and click OK.

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

  • 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 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 to re-sort the data table according to the serial number. After that, you may delete the row.

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 a 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

  • Select those three cells and press Ctrl then to open the Delete.

How to Delete Rows Based on Conditions

  • Select the Entire row and click OK. Those entire 3 rows will be deleted.

How to Delete Rows Based on Conditions


Things to Remember

  • If you have a 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.

Download Practice Workbook


Conclusion

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


Related Articles


<< Go Back to Delete Columns | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo