How to Use Find and Delete in Excel

Did you just spend a whole lot of time entering a piece of information, only to realize you no longer need the data? Unfortunately, we cannot bring you back the time you spent entering the data but, what we can do is help you save time in deleting them.

Excel has three features – Find & Select, Sort & Filter, and the Find & Replace command that will help you in the process of locating and deleting values. You can use these tools to first select the data, and then use Ctrl + – to delete them. If you’re not a big fan of shortcuts, you can use the Delete option in the Home ribbon to delete the selected values.

Find & Select

The Find & Select tool comes in handy when you’re looking for more than just data on your spreadsheet. The utility helps you select almost everything including formulas, comments, conditional formatting, constant values, data validation, and objects.

Open your worksheet.
On the Home tab, select Find & Select.
From the drop-down, select one of these:

Formulas: To select cells with formulas.
Comments: Selects cells with embedded comments.
Conditional Formatting: To select cells with conditional formatting rules.
Constant: Selects cells with static values.
Data Validation: To select cells with data validation.
Select Objects: Selects objects in your sheet.

On your keyboard, select Ctrl + – to delete the selected cells.

Sort & Filter

Sort and Filter is the perfect tool if you’ve entered your data under a table. Even if you haven’t inserted a table, you can still use the tool if your data is under a range.

You will not have to insert Filter if your data is under the table. If your information is however in a range, select the first cell of the range and head to Home > Sort & Filter > Filter.

Select the drop-down icon on the column header.
Deselect the box next to Select All.
On the search bar, type the data you wish to delete and hit Enter.
The tool will show only the data you wish to delete. Select the data.
Use Ctrl + – to delete the values.
Click on the drop-down menu again and select Select all, and hit Enter.

Find and Replace Command

You can use the Find and Replace command to not only select the cells to delete them but, you can also swap the existing value with a new value.

To better explain this utility, we have a sales sheet as an example. We’re looking to filter out all records with the “Complete” status.

On your keyboard, hit Ctrl + H.
Select the Options >> button.
Next to Find what, we entered Complete.
Leave the Replace with section empty. (If we were looking to swap “Completed” with another value, we’d enter our replacement value in this section and then click Replace All.)
Click Find All.
The tool will display all the cell items with the value in a pop-up below the window. Select the first item and scroll down to the last item.
Hold down the Shift key and click on the last item.
Close the Find and Replace Window.
On your keyboard, use Ctrl + –.
Select Entire row > OK on the pop-up.