This guide will explain how to delete filtered rows in Excel.
Since it has several built-in functions and tools, Excel is a popular tool to use for different situations and purposes. Furthermore, we can easily manipulate and organize our data set in Excel. For example, we can filter out our data set to only display certain data we want.
So the filter tool is one of the most amazing features Excel has. Since it’s a very versatile tool, it allows us to perform many things on our data set. Although, the filter tool is mainly used to hide unwanted data or isolate specific data in the data set.
When we apply the filter tool to our data set, we are able to focus on specific data values. Then, we may determine the data values we do not need in our data set. Luckily, we can also delete those filtered data from our data set. In this guide, we will focus on learning how to delete filtered rows in Excel.
Let’s take a sample scenario wherein we need to delete filtered rows in Excel.
Suppose you have an employee directory containing the employee name and the department they belong to. Since one department has been dissolved, you are tasked to remove the employees that belong to the department from the directory.
To do this, you applied a filter in the directory. Then, you filtered the rows to only display the employees belonging to the dissolved department. Finally, you deleted those rows.
Great! Now we can move on and dive into a real example of deleting filtered rows in Excel.
A Real Example of Deleting Filtered Rows in Excel
Let’s say we have a data set containing the names of the employees and the department they belong to. So there will be two columns in the data set. So our initial data set would look like this:
Our main goal is to delete some rows in the data set that belong to the Customer Support department. Of course, we can just go ahead and immediately delete those rows. However, this becomes a difficult task when working with a large data set that has hundreds of rows.
To make it easier for us, we first need to filter our data set. So the filter tool in Excel is one of the most versatile and amazing tools to use. Furthermore, it allows us to easily organize, arrange, and manipulate our data set.
Essentially, we would first filter our data set to only display the rows containing the Customer Support department. And this would easily group the rows we need to delete. Then, we can simply delete the filtered rows from our data set.
Firstly, we will first apply a filter to our data set. To do this, we can simply select the filter icon in the Data tab. Then, we can filter out the data. Since our criteria are to filter out rows with the Customer Support department, we will click the filter of the Department column.
Next, we will uncheck all the boxes next to all the departments except for Customer Support. Afterward, our data set will only display the rows containing the Customer Support department. And all that is left for us to do is simply delete these rows.
Next, we will remove the filter and display the remaining rows of our data set. So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can proceed and explain the steps of how to delete filtered rows in Excel.
How to Delete Filtered Rows in Excel
In this section, we will explain the step-by-step process of how to delete filtered rows in Excel. Furthermore, each step contains detailed instructions and pictures to guide you through the process.
To apply this method to your work, we can simply follow the steps below.
1. Firstly, we will select our entire data set. Then, we will go to the Data tab and select the Filter icon found in the Sort & Filter section.
2. Secondly, we can now find a small dropdown arrow in the right corner of each header column. So these arrows will allow us to apply the filter tool on the respective column. In this case, we will click the dropdown arrow found in the Department column.
Since we want to filter the rows that belong to the Customer Support department, we will uncheck all the other departments except that. Otherwise, we can simply uncheck the Select All box. Lastly, we will click Apply to filter the rows in the data set.
3. Thirdly, we have now successfully filtered the rows we want to delete. And now we can simply delete them. To do this, we need to first select all the filtered rows and right-click. Next, we will select Delete and Sheet Rows from the pop-up menu.
4. And tada! We have successfully deleted filtered rows in Excel.
5. To display the remaining rows of the data set, we can simply go to the Data tab and click again the Filter icon found in the Sort & Filter section.
And that’s pretty much it! We have successfully explained how to delete filtered rows in Excel. Now you can go ahead and apply this method to your own work whenever you are working with a large data set and need to delete irrelevant rows from the data set.
Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.