When you have a worksheet filled with blank rows or cells, it can make it look really messy. Apart from that, it also prevents the built-in tools such as sort, subtotals, e.t.c from performing its functions correctly.  Therefore, you will need to do some cleaning up and remove blank rows in your worksheet.

Although, it may seem like a huge task, especially when you have a large dataset, the blank spaces and rows can however be removed easily using the ‘Find & Select’ option in Excel.

On the other hand, much care should be taken when deleting blank cells and rows this way. This is because a blank cell may be located in a row that has other useful data and by selecting delete ‘Entire rows’, you lose all all the other data in the rows that have blank cells.

Again, if you select ‘Shift Cells Up’, it will move the data below to the next cell up thereby giving that row another person’s data.

For example, if you have this table below with 11 rows and you want to delete the blank rows in7 and 8.

Using the Find & Select option, this what you will get:

What happened here is that the blank rows in row 7 and 8 have been deleted. Also, by choosing ‘Shift Cells Up’, row 5 and 11 that had blank cells are now having other distributors data. The Distributor Name ‘Deacon Craig’ now has Finland as Country and ‘Brynne Mcgowan’ now has Vanuatu and no longer Finland as it’s Country.

Also, if you select delete ‘Entire rows’, the entire row of ‘Zahir Fields’, ‘Decon Craig’ and ‘Levi Douglas’ would be deleted from the dataset.

So, in this tutorial, we will see how to delete/remove blank cells using ‘Find & Select’ option, as well as another easy method of deleting blank cell and rows without deleting useful data or mixing up the data in the dataset.

Remove or Delete Blank Rows or Cells in a Worksheet

To do this:

#1. Select the entire data table in the worksheet

#2. Go to HOME tab and click on the ‘Find & Select’ option

#3. Select ‘Go to Special’ option

                 Or

Press Ctrl + G to bring up ‘Go To’ dialog box, then click on ‘Special’

#4. In the ‘Go to Special’ dialog box, select ‘Blanks’

#5. Click ok

The blank cells will be highlighted. Check to confirm that it is only the Blank cells or rows that have been highlighted.

#6. Press Ctrl + – to pop up the ‘Delete’ dialog box

                          Or

In Home tab, select ‘Delete’ under the Cells option in the ribbon, and click on Delete

#7. Select ‘Shift cells up’ to remove blank spaces and move data up to close the spaces or select delete ‘Entire Row’ to remove all the blank rows.

Now you have a clean dataset.

Remove Blank Rows Without Losing Any Data in Excel

Here, we will see a quick way to delete blank rows and cells without having to lose any data or mix up the data in the Excel sheet.

You can only notice any loss of data when you have a small dataset. But where you have very large dataset, you would have lost dozens of rows with good data without even realizing it or would have mixed up the data and end up having wrong information.

 So, if you have blank cells across different columns, it means that you will only need to delete only the rows that have no single data in any column at all.

And do this, you will need to use a Key Column in the dataset to determine if row is empty or not. You can either use an existing column in the dataset or you create a new ‘Blank’ column. Here we will create a new blank column.

#1. Create a Key Column as the last column in the dataset.

#2. Insert the formula in the first cell of the new column:

                      =COUNTBLANK(A4:F4)

This will count blank cells in the specified range which is the first to last column in the data table.

#3. Copy the formula to the entire column.

Now the key column has been added to the table. Next, you add an AutoFilter to the data table

#4. Go to Data tab and click on ‘Filter’ option

Next is to apply filter to the new Blank column.

#5. Click the arrow in the column header, uncheck ‘Select All’ checkbox, and check 11 checkbox.

11 is the total number of columns with blank cells which means that there are 11 columns in the dataset. So you select all the rows with no data in it.

#6. Click ok.

#7. Press Ctrl + Home to select and press down-arrow key to go to first data row

#8. Then press Ctrl + Shift + End to select the entire data table

#9. Right-click on selected cell and select ‘Entire Row’ or Ctrl + –

#10. In ‘Delete entire sheet row’ dialog box, click ok.

#11. Then go to Data tab and select ‘Clear’ option to clear the applied filter

Now, all the blank rows have been removed and rows with blank cells will still be there. There will be no mix-up of data or losing any useful data.

Have you tried this method yet? Kindly share in the comment section.

53 Replies to “How to Delete or Remove Blank Rows in Excel”

  1. This design is wicked! You most certainly know how to
    keep a reader amused. Between your wit and your
    videos, I was almost moved to start my own blog (well, almost…HaHa!)
    Excellent job. I really loved what you had to say, and more than that, how you presented it.
    Too cool!

  2. Wow that was strange. I just wrote an really long comment but
    after I clicked submit my comment didn’t appear.
    Grrrr… well I’m not writing all that over again. Anyhow, just wanted to say excellent blog!

  3. Have you ever thought about publishing an e-book or guest authoring on other sites?
    I have a blog based upon on the same subjects you discuss and would love to have you share some stories/information. I know my visitors would enjoy your work.

    If you are even remotely interested, feel free to shoot
    me an email.

  4. Hi, i read your blog from time to time and i own a similar one
    and i was just wondering if you get a lot of spam responses?
    If so how do you stop it, any plugin or anything you can recommend?
    I get so much lately it’s driving me mad so any assistance is very much appreciated.

  5. It is the best time to make a few plans for the future and it is time to be happy.
    I’ve read this publish and if I could I desire to counsel
    you some fascinating things or tips. Perhaps you could write subsequent
    articles relating to this article. I wish
    to read more things about it!

  6. Link exchange is nothing else however it is simply placing the other person’s webpage link on your page at proper place and other person will also do same for you.

  7. We are a gaggle of volunteers and opening a new scheme in our
    community. Your website offered us with valuable information to work
    on. You’ve done a formidable activity and our whole neighborhood will probably be thankful to you.

  8. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point.
    You definitely know what youre talking about, why waste your intelligence on just posting videos to your site when you could be giving us something informative to read?

  9. When I originally left a comment I appear to
    have clicked on the -Notify me when new comments are added- checkbox and from now on whenever a comment is added I recieve four emails with the
    exact same comment. There has to be an easy method you can remove me from that service?
    Thanks!

  10. I want to start a personal blog.. an online journal. I have no idea which blog website to start it with.. . anything I should know about having a blog and who to have one with?.

  11. I’m not sure why but this site is loading extremely slow for me.
    Is anyone else having this problem or is it a problem on my end?
    I’ll check back later and see if the problem still exists.

  12. I would like to start a free WordPress blog on the WordPress site (not my own domain) but I don’t like the themes they provide. Can I use a downloaded theme on there? I have seen some WordPress hosted blogs with different themes, so it looks like it’s possible..

  13. I just couldn’t leave your website before suggesting that I extremely loved the standard information an individual
    provide on your visitors? Is gonna be back frequently in order to inspect new
    posts

  14. I blog often and I seriously thank you for your
    information. The article has truly peaked my interest.
    I will book mark your blog and keep checking for new details about once a week.
    I subscribed to your Feed as well.

  15. Howdy! Someone in my Myspace group shared this site with us so I came to check it out.
    I’m definitely enjoying the information.
    I’m book-marking and will be tweeting this to my followers!
    Wonderful blog and terrific design.

  16. What i don’t understood is in reality how you’re not actually a lot more well-preferred than you might be now. You are so intelligent. You understand therefore considerably in the case of this subject, made me for my part believe it from a lot of varied angles. Its like women and men don’t seem to be interested unless it?¦s something to do with Girl gaga! Your individual stuffs nice. At all times deal with it up!

  17. Great site you have got here.. It’s difficult to find high quality writing like yours nowadays.
    I seriously appreciate individuals like you! Take care!!

  18. It’s perfect time to make some plans for the long run and it is time to be happy.

    I’ve read this submit and if I could I wish to suggest you some interesting issues or advice.
    Perhaps you could write subsequent articles referring
    to this article. I desire to learn even more things approximately
    it!

  19. Thank you for the good writeup. It in fact used to be a entertainment account it.
    Look advanced to far added agreeable from you! However, how can we keep up a correspondence?

    Look at my web-site … 2022

  20. Hello! I’ve been following your website for a long time now and finally got the courage
    to go ahead and give you a shout out from Huffman Tx!
    Just wanted to tell you keep up the good job!

  21. Howdy! I’m at work browsing your blog from my new iphone 3gs!
    Just wanted to say I love reading your blog and look forward to
    all your posts! Carry on the excellent work!

    Feel free to surf to my web site; special

  22. Hi there, I discovered your website via Google while looking for a similar matter, your web site came
    up, it appears to be like good. I have bookmarked it in my
    google bookmarks.
    Hi there, just changed into aware of your blog through Google, and located that it is truly informative.
    I’m gonna be careful for brussels. I will be grateful when you proceed
    this in future. Numerous other folks might be benefited from your
    writing. Cheers!

  23. Howdy I am so delighted I found your blog, I really found you by mistake, while I was
    searching on Digg for something else, Anyways I am here now and would just like to say thank you for a remarkable post and a all round exciting blog (I also love the theme/design),
    I don’t have time to go through it all at the minute but I have saved it and also added in your RSS feeds, so when I have time I will be back to read more, Please do keep up the great job.

Leave a Reply

Your email address will not be published.