A guide to deleting filtered rows in Excel

Excel has a wide range of capabilities, many of which most users are unaware of. Data management necessitates the use of a spreadsheet, and it’s ideal if each of your Excel rows is distinct. You may come across numerous identical rows with different data and information in some circumstances. You should be able to filter your worksheet and delete some of the problematic rows in order to have a good working environment in Excel. You can concentrate on certain data without being distracted by the clutter. You may feel compelled to delete some of these non-relevant rows while using one of these filters, perhaps because you no longer require them. This method can be completed in a few simple steps; follow through for more information on how you can do this.

Step by step process – A guide to deleting filtered rows in Excel

1. Firstly, open your Excel document.
2. If you haven’t already, apply a filter by clicking on “Sort & Filter” > “Filter”.
3. After that click on the dropdown arrows on the preliminary column.
4. Ensure only the rows you want to delete are checked.
5. Close the filter panel.
6. Now select the rows to delete, they are filtered so you can select everything within the record section.
7. Now press control + C or command + C.
8. Click the dropdown under delete cells.
9. Now click on “Delete sheet rows”.
10. Finally, clear the filter “Sort & Filter” > “Filter”.

That’s it, once you have unchecked the filter, you will be left with the rows you want — all unwanted rows will be deleted from the sheet. However ensure you delete the rows; if you don’t delete the rows, you will be left with gaps where the rows of information were. Similar to the illustration shown below. You want to completely remove the rows therefore you need to follow the step by step process above and prevent taking shortcuts, as Excel has very minimal shortcuts for this particular area.

The method described above may not work flawlessly in all versions of Excel. Some Excel versions may show an empty screen right before you remove the data, leaving you with nothing afterward.

Some users, however, might choose to exclude the filtered bits by copying and pasting the filtered parts into a new spreadsheet. This solution has the disadvantage of requiring you to work in a distinct window/spreadsheet, which may not be ideal.

Another flaw with this strategy is that it fails to keep the row and column sizes consistent. There is, however, a way out for just the column width. You can paste into a new spreadsheet using a unique keyboard shortcut that preserves the column widths. The “W” key on the keyboard is the shortcut to use.

Using colours is the finest of all. Colors can be used to highlight the rows and columns that you want to delete, and then you can filter by colour.

The following is a synopsis of the full procedure:

• Filter the items you want to keep after you’ve completed the filtering procedure.
• Choose a column and fill it with the colours you choose.
• Filter should be removed.
• Repeat the filtering process. Color should be used to filter the column this time. Choose “No Fill.”
• Delete the highlighted rows and clear all filters.

Filters can be used to reduce the size of your worksheet’s data and hide parts of it. While filtering may sound similar to grouping, it differs in that it allows you to qualify and display just the material that is relevant to you. You may, for example, limit a list of survey participants to show just those who are 25 to 34 years old, or use the method above to delete items that are no longer in stock.

Let’s look at a real world example – A guide to deleting filtered rows in Excel

Our company sells a selection of BBQs and grills over the summer months and now that the closing of summer is complete, it’s time to alter the inventory and remove all that is no longer in stock. As you can see below the listings for the BBQ are mapped in a CSV format, we need to remove the products out of stock. How can we achieve this?

We have 10 products with some that are sold and some that aren’t, we need to remove the ones that aren’t, follow through to see how you can achieve this.

Step by step breakdown

• Firstly, open your Excel document.

• If you haven’t already, apply a filter by clicking on “Sort & Filter” > “Filter”.

• After that click on the dropdown arrows on the preliminary column.

In our example, the preliminary column is the inventory column, since we are removing the products that are out of stock (inventory record=0).

• Ensure only the rows you want to delete are checked.

Here as we are geleing the records marked 0 ensure you have the checkbox next to 0 selected.

• Close the filter panel.

Do this by clicking on the cross.

You will now only be left with products that are out of stock, you can use other filters such as the name to further refine the selection, perhaps out company have found some return stock for the “Charcoal fused BBQ grill”, therefore you can click on the little dropdown and unselect that, continue to refine until you have the rows you need deleting.

• Now select the rows to delete, they are filtered so you can select everything within the record section.

To do this click on the row number at the start of the record section, in this example its row number 2, click on where it says 2, now click shift and the end row which in this case is row 11. This will select all the rows in the middle. If you have thousands of rows of products to delete, you may be better off selecting the whole sheet to the edde, click on command or control followed by the down arrow, this will take you to the bottom edge of the Excel sheet. Shift and select that row.

• Now press control + C or command + C.

Notice how there is more than one copy box indicated by the dashed lines, well they are the products that are not going to be deleted, they still occupy the space but have been condensed in a non visible format. This is more evident when you look ar the rows, as you can see the missing numbers are rows that are not going to be deleted.

• Click the dropdown under delete cells.
• Now click on “Delete sheet rows”.

You may be assuming where are the products that are not meant to be deleted, well don’t worry they are still there just condensed,

• Finally, clear the filter “Sort & Filter” > “Filter”

That clears the filter and leaves only the products that are still in stock. 

This is what happens if you don’t delete the rows and just delete the selection, you will be left with gaps where the products were, which is fine if you want to replace the products, but most likely you will want them to be removed ready to put in records, or add to a website. Just export as CSV to add into a website.

That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.