It’s finally summer, and you need to stay cool, even when you’re using Excel. Here’s an energy-efficient and fast way to find and delete Excel rows. You can select several rows that contain similar data, and delete them all at the same time.
Find All the Data
In the worksheet shown below, there is a list of products sales, and a few of the records are for paper sales.
I’d like to delete those paper sales rows, without having to sort the worksheet, or spend a long time manually selecting the rows.
Find the Paper Rows
To find all the Paper sales rows, I can use the Excel Find command. Here are the steps to do that:
-
- On the Ribbon’s Home tab, click Find & Select, and then click Find.
- In the Find and Replace dialog box, type “paper” in the Find What box.
- Click Find All, to see a list of all the cells that contain the text, “paper”
- Select an item in the list, and then press Ctrl+A, to select the entire list. That will also select all the “paper” cells on the worksheet.
Delete the Selected Rows
To delete the entire row for each “paper” cell that was found, follow these steps:
- On the Ribbon’s Home tab, click Delete, and then click Delete Sheet Rows.
All the selected rows will be deleted, and the other product orders remain on the worksheet.
Video: Find and Delete Excel Rows
To see the steps to find all the instances of a word, and delete the selected rows, watch this short Excel video tutorial.
More Find and Replace Examples
See more ways to use the Find and Replace commands in Excel, on my Contextures website.
Also, see how to select rows based on their conditional formatting colour, and delete the filtered rows. This example uses a list in a named Excel table.
___________
This saved me an incredible amount of time; thank you!
So simple, so effective…THANK YOU!
Is there a way to do the opposite? Find rows with a certain word, and purge everything else?
Did anyone find an answer to this question… how to delete all the unselected rows??
Copy the selected rows you care about to a new spreadsheet. That’s the simpler solution.
That will be a problem if theres about tens and thousands of data and you have to filter them 1 by 1…
thans! it was amazing
HUGE time saver!! Thank you for posting!
Saved me hours of time and this was the first website I goodled on the subject.
Can’t thank you enough, you are a genius!
You’re welcome! Thanks for letting me know that it helped you.
Thank you! This saved me a lot of time (more than 3 hours perhaps)!
This cut my work time in half. Thank you very much!
Thank you for sharing. it helps me a lot!!
Fantastic
Thank you
Great tip Debra! I’m working on a similar problem except I’m trying to delete rows containing any one of about 200 keywords/phrases from a sheet with +250,000 rows in total. I tried recording your tip and it gave me the following code but when I replay it, it only deletes the first row containing the keyword. Any suggestions on how I can get it to work using VBA? Merry Christmas!
Sub Macro1()
Cells.Find(What:=”KEYWORD TO BE DELETED”, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireRow.Delete
End Sub
Jeff,
The VBA you recorded is not searching for all instances in the active worksheet. The code you posted above, searches for one instance of “KEYWORD TO BE DELETED”, activates that cell, then deletes only that Row. You could try:
Sub Macro1()
Last = Cells(Rows.Count, “COLUMN”).End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, “COLUMN”).Value) = “KEYWORD TO BE DELETED” Then
Cells(i, “A”).EntireRow.Delete
End If
Next i
End Sub
Replace COLUMN with the letter of the column “KEYWORD TO BE DELETED” is in.
Krystal, I would be for ever grateful if you could explain a very similar problem to me. I have 7000 patents in an Exel 2010 worksheet. I have a list of 1000 of those patents to delete. The list of patents that I want to delete references the Publication Number. So, I want to some how look up those 1000 Publication Numbers in the worksheet of 7000 and delete the whole Row of information associated with that Publication Number. I can send you the Excel Workbook, I am sure that it would be clear to you what I want to do after seeing it. Thank you Thank you Thank you!
did you find an answer?
This was the only search result that gave me the simple solution. Thank you!!!
How can this be done with newer versions of Excel? My version doesn’t show me all cells with the target word I am looking to delete.
Thanks.
Debra, you are now my favorite person ever. Hours of work saved with your instructions. Thank you.
@Maury Thanks! I’m glad the tip helped you save time.
THanks
Thanks great tip. It saved a lot of my precious time.
Thanks Debra … tip for Excel moron greatly appreciated 🙂
Thank you, who knew there were so many choices to get to doing something so basic!
Awesome. So simple – using only toolbar/ribbon commands. All the other solutions VBA. You saved me hours. Thumbs up and five stars to you.
Brilliant Solution! So simple without having to use VBA – Thanks!
Thank you! Very helpful!
Debra,
Doing a new search in the “find and replace” dialog, I find myself needing to clear the previous search terms, but there is no ctl-all (or triple click) function in these fields so I may highlight all and then clear the fields for new search terms.
Is there a quick way to clear these fields?
Hi Debra!
Would you happen to know how this is done in Excel for Mac?
Thanks!
Really Nice.Helped me lot.Thanks For the post
Thank you very much, as others have said, you saved me an incredible amount of time!
This is very helpful! Thanks.
Wow, that sure worked. Now I need to remove all items priced at 30 dollars or less. Tried same method, but when I use find: 25 for example…it also included any price that had a 25 in it (125,425, etc) Tried changing number format but still highlights items I don’t want included. Any ideas? Thanks.
Debra – somewhat related, you can find excel files from the web by using http://www.findthatexcel.com search engine
Cool Solution but lacks something!
What if you have a “Lists” sheet, where you have more lists/”tables” from left to right with spaces betveen them.
In this case, if you delete total rowa, you destroy more lists/tables data!
This tip ROCKS! Thank you!
Thx, its very useful for my works.
Why not just put a filter on paper and delete the rows. I think that’s quicker.
Really, hats off to you; I save a lot of time with using this technique. Debra Dalgleish 🙂
Thank you very much :)!
This is fantastic for word searches, but what if you want to delete rows of cells containing dollar amounts below $600.00? I was able to conditionally format the spreadsheet to highlight figures under $600.00 but can’t find a way to mass delete those that have been highlighted. Your help is appreciated.
@Mrs. B, instead of using this method, you can apply a filter, then delete the filtered rows
Assuming that your list is in a named Excel Table (http://www.contextures.com/xlExcelTable01.html), follow these steps
Make a backup copy of your file first — just to be safe.
1. Click the arrow in the heading for the column where you applied the conditional formatting
2. In the drop down, click Filter by Color, and select the color that you used.
3. Select the colored cells, and on the Ribbon’s Home tab, click the arrow under the Delete command
4. Click on Delete Table Rows.
5. Remove the filter, and check that all the other rows are still okay, and the colored cells have been deleted.
If it doesn’t look right, click the Undo button, or press Ctrl + Z to undo the deletion.
it is quite simple. It saved my lot time. Thanks for sharing this.
Thank u so much.
Champion!
Thanks
Thank you so much!! it was very helpful.
Does anyone know how to move search rows into a new sheet instead of deleting them.?
This process is exactly what I have been looking for except……. the string I’m looking to delete starts with *** which is the excel default for wildcard. When I do a find for *** it selects all the rows. Is there a way to find the characters ***?
Jeff, you can use the tilde character before the asterisk: ~*~*~*
That works perfect. Thank you.
VERY NICE
Thank you very much for sharing this technique, i really appreciate and it helped me and saved my time. thanks once again
Great tip…didn’t know it was possible to “bulk delete” like that! Instead of deleting, is it possible to copy the selected rows to the clipboard, or extract the selected record to another worksheet? If not, what about the ability to highlight the selected rows in some fashion? (thus making it easier to identify the rows in a large spreadsheet)
Thank you !!!
This tip saved me a lot of time. Thank you!
When I attempt to select all then delete entire row, if de-selects my selecions in the find and replace tool. any Idea?
Thank you for sharing the tip.
Its amazing. Saved my time. I am very thankful to You.
Thank you!!!
I’ve used this technique to find, select and format all the ‘found’ cells, but never used it to delete rows. 🙂
Perhaps because since Excel 2010, I have used the Data > Remove duplicates command, which gives the same results — except if you want to
(a) find and select the cells in a case-sensitive manner, or
(b) find and select partial matches — in which case you need to be *extremely* careful, because Excel can find matching text in cells in ways that you may not have thought of!
Thank you!
Is it just me or is anyone else having trouble selecting All from the find list using Ctrl + A ?
First select one of the entries inside the search box. And then do Ctrl+A. (I hope this was it.)
Thank you!
Thanks
Hi
Im using the online version of excel and when i do a ctrl f , it doesn’t have the same look and feel as what it used to have and it doesn’t also show the number of records that it retrieves. The dialog box that pops up doesnt show anything except to look down or up etc.. which is so pathetic. I cant do a ctrl a in the dialog box because there is nothing of that sort so I cant seem to collectively delete the rows that contain a certain word I dont want.
Pls help. 16.0.9222.5051 is my online build version for excel.
Fantastic information, still valid for 0365. THANK YOU!!!
this does not work in excel for mac.
there is not “find all” command in the excel mac version.
Thank you so much! You saved me from manually deleting about 3,000 lines. Woohoo!