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.
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.
- 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.
- 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.
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.
- 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.
- 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).
- Go to the Data tab and select Sort from the Sort & Filter group.
- On the Sort window, click on Options.
- A new window will open. From here, choose Sort left to right and click OK.
- 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.
- 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.
- The final result will be like this.
- 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.
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.
- 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.
- Select those three cells and press Ctrl then – to open the Delete.
- Select the Entire row and click OK. Those entire 3 rows will be deleted.
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
- How to Delete Blank Columns in Excel
- How to Delete Column in Excel Without Affecting Formula
- How to Delete Multiple Columns in Excel
- How to Delete Column in Excel Without Affecting Formula
- How to Delete Unused Columns in Excel
- How to Delete Unused Columns in Excel
- How to Delete Infinite Columns in Excel
- How to Delete Empty Columns with Header in Excel
- How to Delete Hidden Columns in Excel
- How to Delete Columns with Specific Text in Excel
- [Solved!] Can’t Delete Extra Columns in Excel
<< Go Back to Delete Columns | Columns in Excel | Learn Excel