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.
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!
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!
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.
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.
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!
This is my first time visit at here and i am actually pleassant
to read all at single place.
Helpful info. Lucky me I discovered your site unintentionally, and I am stunned why this coincidence didn’t happened earlier!
I bookmarked it.
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.
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.
Asking questions are actually fastidious thing
if you are not understanding something completely, except this paragraph offers nice understanding even.
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?
Thanks for sharing your info. I truly appreciate your efforts and I will be waiting for your further write ups thanks once again.
Good post however , I was wanting to know if you could write a litte more on this
subject? I’d be very grateful if you could elaborate a little bit more.
Bless you!
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!
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?.
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.
I like this internet site because so much useful material on here : D.
It is not my first time to go to see this site, i am visiting this web site
dailly and take good data from here every day.
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..
If you want to take a great deal from this article then you have
to apply such methods to your won webpage.
Excellent post. I used to be checking constantly this weblog and I’m impressed! Extremely useful information specifically the last phase 🙂 I maintain such information a lot. I was seeking this particular info for a long time. Thank you and best of luck.
Your mode of describing the whole thing in this post is truly fastidious, every one be
capable of effortlessly be aware of it, Thanks a lot.
Awesome! Its in fact amazing piece of writing, I have
got much clear idea regarding from this post.
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
I think this is among the most significant info for me.
And i’m glad reading your article. But want to
remark on some general things, The site style is great, the articles is really nice :
D. Good job, cheers
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.
배팅의성지먹튀검증 안전한메이저
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.
Fantastic website. Plenty of helpful information here. I am sending it to some friends ans additionally sharing in delicious. And obviously, thanks to your sweat!
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!
This is my first time visit at here and i am genuinely
pleassant to read all at single place.
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!!
It’s awesome to pay a visit this site and reading the
views of all mates concerning this post, while I am also eager of getting know-how.
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!
I used to be able to find good info from your blog posts.
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
Hi, its fastidious piece of writing about media print, we all be
aware of media is a impressive source of data.
Useful information. Fortunate me I found your web site unintentionally, and I am
shocked why this coincidence didn’t took place earlier!
I bookmarked it.
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!
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
Thanks for sharing your thoughts on my. Regards
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!
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.
I was suggested this blog by my cousin. I am not sure whether this post is written by him as no
one else know such detailed about my difficulty. You’re amazing!
Thanks!
As a Newbie, I am continuously exploring online for articles that can help me. Thank you
Keep functioning ,splendid job!
canadian pharmacy world
mexico pharmacy order online
online pharmacy no prescriptions
mexican pharmacy cialis
highest rated canadian pharmacy
canadian pharmacy online
no prescription pharmacy