Ready to remove those pesky empty cells in Excel? Here’s how to do it.
Open your spreadsheet and press [Ctrl] [A] to highlight the entire document. Or highlight just the section of the spreadsheet where you want to remove empty cells.
Now, press F5 on your keyboard. The Go To menu will appear. Click the Special button.
The Go To Special window comes up. Select Blanks and click OK.
Now, Excel will select all blank cells in your document. To get rid of these hold [Ctrl] [-] on your keyboard. The delete window opens. Select Entire Row or Entire Column to shift cells around and remove the empty ones.
25 Comments
Leave a Reply
Leave a Reply
HankD
Hi! This is exactly what I was looking for – and when I try to run this my Excel 2010 finds the empty rows but when I try to delete them the program does not respond and I can’t do the deletions. I seem to have 65000 rows and been trying this several time s now, with same result. Hmmm…any idea for me?
Best /Hank
Dr D
try doing it in smaller sections, that way your program won’t freeze up. If that doesn’t work you can always do a seek and find for blank rows only. after you separate them you can just delete them all.
Miguel
marvellous mate: 3Es4this Easy, Elegant and Effiecient !
Jaison
The above deletes all the cells, but you can try filter & sort – blank cells and delete all the blank cells.
Jaison
The above deletes all the cells, but you can try Sort & Filter – un-check Select All box, then click the blank cell check boxe. this will sort all your blank rows and then delete the rows.
Fahad Gilani
Thank you Austin, this is what I was looking, it helps me a lot.
Sal
Worked perfectly 1st time around, exactly what I was looking for- thanks!!
Rach
Thank you so much you just saved me about 3 hours of deleting individual blank cells.
GVLNSRK
THANK YOU SAVED ME TO DEPENT ON OTHERS
Geekgurl
Thanks!!
Thiery
Thumbs up bro
mohammad hosein
salaam
very usefull
so great
thanks a lot
Julius
Nothing works for me. After I select Blanks and click ok, I get a box that pop up and says “No cells were found”. What other options do I have
Sue H
Fantastic. Worked a treat.
(You have to remember to highlight all of the data that you want to clear the blank cells from).
Thank you so much.
surya
hi. hello Austin..
i need help from you.
i tried to delete all blanks cells without leaving one blank cell in a row , but it delete all cells on sheet , i want leave some blank cells in different rows and columns. please give me way..thankyou
Dear Austin
Superb solution to delete blank rows. Thanks a lot, but what if we want to add multiple rows in the sheet when the value change by considering a column.
Phil
Thanks, Worked great
atul bhardwaj
thanks very useful.
Karen
Really helpful and has saved me loads of time.
Lali Lingfa
Thank you very much for excellent information.
dolapoking
Thanks! it really helped.
Watergrrl
I love you.
Morad Hassan
You’re my F*&%ing HERO! Thanks!!!!!
Vernon H
This is good for data in rows with no formatted headings, but my data is in hundreds of sections with headings that are merged and centred over say 2 rows and five columns. This method also deletes the heading rows (which are definitely not blank!). Using Excel 2007
Felix
But this will delete all data in adjacent cells!