Filtering rows upon rows of information in Excel is a fantastically effective way of narrowing down the information present in any given sheet to only that which you actually need to work on. Similarly, it’s a great way of locating all of the information which you need to delete from the document.
To learn how to delete filtered rows in Excel, simply follow our step-by-step instructions below, which will show you first how to filter an Excel document, and then how to delete those filtered rows.
For any additional information, or if you have any further questions, please direct your attention to our FAQ section at the end of this article.
Table of Contents
How to filter rows in Excel
- Open the Excel app on your computer, and then load a document with data on which you can test this process (a few rows with headed columns and data entered into them, will do)
- Click the arrow in the top-left corner of the sheet to highlight the entire document
- Next, scroll to the ‘Data’ tab of the menu toolbar along the top of the screen, and then click the ‘Filter’ tool, under ‘Sort & Filter’
- You’ll see a small drop-down arrow appear next to the heading atop each of the columns in your Excel sheet, locate the column with the information in it you wish to filter your spreadsheet by, and click the arrow in this column’s header
- From the drop-down menu, you can now choose how to filter the document. All current information in that column should have a black check-mark/tick next to it, indicating it is currently displayed. Untick all of the categories you don’t want to filter by, leaving only the one (or two or more) which you do wish to filter by.
- For example: if your spreadsheet consists of company employees, and you wish to filter by the column pertaining to employee department, you could untick boxes ‘Sales’, ‘Accountancy’, and ‘Publicity’, and leave only ‘Marketing’ checked, in order to filter out all employees except those working in marketing
- Click ‘OK’ and your spreadsheet will filter out all those rows without your chosen field
How to delete filtered rows in Excel
Having filtered your rows as per our instructions above, it’s time to delete your filtered rows.
- Simply click the number of the first row displayed after the filter has been applied, and drag whilst clicking to highlight all of the filtered rows you wish to delete
- Next, simply right-click anywhere within the highlighted area and select ‘Delete rows’ from the pop-up menu
- Lastly, you can return to the full, unfiltered view of your document by clicking the ‘Filter’ tool in the ‘Data’ tab again, thus turning it off
- You should see your spreadsheet unchanged, except for the fact that all of the rows you filtered have now been erased from the document, leaving only those rows you did not filter
And that’s all there is to it! Knowing how to filter rows and then delete them on Excel is going to significantly speed up your rate of work whilst using the software, and save you from having to scour a document for individual rows which you need to erase.
For all other information, or if you have any further questions, please see our FAQs below.
Frequently asked questions
What are filtered rows in Excel?
Excel is one of the world’s premier data entry, accountancy, and data manipulation programs. Created by Microsoft, and included in Microsoft’s acclaimed ‘Office’ package, Excel has become the industry standard for corporate finance and accountancy, HR workers, salespeople, and more. In fact, so critical to the day-to-day running of most businesses is Excel, that it has become synonymous with the virtual spreadsheets it employs as the foundational elements of its software.
One of the most useful tools available to you in Excel is the ability to ‘filter’ your spreadsheet, full of information, leaving you with only those rows you actually need to view.
Let’s say for example you’ve got a spreadsheet with all of the information pertaining to all of the employees under your employ. Maybe you’ve got a column for the departments they all work in, but today you only want to edit or view those employees who work in the Marketing department. You can filter your document to filter out all those rows not pertaining to people in said department, leaving you with only those rows with the details of your Marketing department employees.
You can employ a wide range of overlapping filters, too, to filter down even the largest, least digestible document into just a few rows of genuinely relevant information. And once you’re done with your work for the day, you can reset the filters so as to see the document as a whole.
Why would I want to delete filtered rows in Excel?
Whilst it may seem prudent to use the Filter function in Excel to view only that information which you need to, it is equally useful to use the same filter to view only that information that you wish to delete.
For example, using the example given above, it may be that of your employees, all of those who are over the age of 65 are set to retire this year. As such, you may no longer need their information and wish to delete their rows. You could use the Filter tool in Excel to filter out all employees except those over the age of 65 (provided you have a column with a record of everyone’s age). You can then delete all of the rows the Filter tool shows you, before returning to look at the full document, which would no longer have those employees included in it.
Can I recover accidentally deleted rows in Excel?
You can, but you’ll have to be quick. Once you’ve taken any action in Excel (including deletion), you can ‘undo’ it by either clicking the ‘undo’ arrow, or by pressing CTRL + Z on your keyboard. Each click of the icon or press of this keyboard combination will undo one action and one action only. Thus, if you complete many actions after the accidental deletion and before realizing your mistake, you would have to ‘undo’ all of the actions you have since made until you reach the point at which you can undo the accidental deletion.
Microsoft Excel only keeps a record of so many actions, however, and so after a certain point, you will no longer be able to ‘rewind’ far enough to undo the accidental deletion. Better than that you make absolutely sure of your decision to delete those rows before you do so.
