Stop Wasting Time! 3 Easy Ways to Remove Blank Rows in Excel

I wrote a macro more than 20 years ago to do this and still use it as needed. Good to learn some other ways as well, though, especially with PQ.

#4 Ctrl-T, sort a column, move the end of table marker below the first non-empty row.

Like power query, thou the second method was equally fast just not dynamic.

You know the PwerQuery is the best, Leila. I like that you throw some additional tricks in there as well while demonstrating each method. Thanks 😊!

#2 please :)

till now I have been using GotoSpecial select blanks and remove rows ...... Power Query method is so cool

Love me some hot keys.

Great video :-) not a big addition to it, but, for the counta() method, instead of scrolling down to copy the formula, I usually type a random sign ("*" usually) at the bottom of the data set (I176) then starting from I2 where the formula is, CTRL + SHIFT + down to copy the formula.

I clicked away and was looking for the range and then you explained it. Amazing video!

Great video! Just wondering, does macbook have a home key?

HI Leila, in the vide o at 5.42 you mentioned when naming the table from A1-My Range not to add a space. Why is that? My preferred method is 3 especially when dealing with large amount of data

Second

Well, I do love food analogies. I'm an overly cautious person. So, my own inclination is to not use a tool until I've reached some threshold where I'm comfortable with what it's doing - until I trust it, and my facility with it. I have to practice or experiment with something for a while before I'm OK using it on meaningful data. So, I don't trust healthy, nutritious foods until I know I can make a complete meal with it.

Sem dúvida Power Query 💟 !!!

Thanks Leila. Three great examples but I like #3 the best. I am in the process of learning Power Query. I like its power, and I still have a lot to learn.

Another great lesson. Thank you for the wealth of knowledge you have and are continuing to share with me <3

I am addicted to power query. Sometimes I think I am using it too much.

I choose power query

I've always used the filter method, but when you have a really big dataset with many blank rows, processing this takes very long and sometimes Excel freezes! Curious to find out if go to special method would work beter in such cases.

Before watching: I often sort such that spaces go to the bottom.

Brilliant tip!

Hi Leila, genial... I have a question about power query method: What if some rows dont have data in date column but have in the other columns?

I think you missed showing an important step in #3. The named range will not auto-expand when you added the new data, unlike a table.

Number 3, no question!

Powerquery is my obvious choice. But, I just learnt how to create a named range and use it in the powerquery. Thank you

I want to learn Power query

Thanks for this useful video.

How can regular line breaks be added to data ranges in a full dataset?

Really cool. My favourite is power query ❤

PQ 🎉

Great content as always! my highlight has to be the ability to get data into Power Query without converting it to a Table by using a Named Range. This is really useful to know. Thanks!

Remove Duplicates on all columns, then just delete the remaining first instance of a blank row.

Remove duplicates

I have trust issues, so I always use the filter😂 then visible cells & delete.

Your Power query course is all good and chill until you start with the M language section 😅 it’s a great course highly recommended and seriously I achieved a lot from just the beginner section to anyone who wants to make their life easier and their boss happier invest in the power query course it will be an eye opener for you. Great video as always!

Thank you for the named range work around, not that power query from a table is ever too big of a problem but it is nice to have alternatives. I still vote 2!

I almost did not watch this, thinking it would not be useful. But I did, and the next day I imported a needed spreadsheet with dozens of blank lines. If not for the technique, I would still be at it manually. Thx.

I'm an advanced Excel user, have done 4 of your cources and thought there is no reason for me to watch a video on deleting blank rows.
🤯 I never knew the trick to create a Named Range and then import that into PQ! That is why I follow your content - Always something new to learn. Thank you.

Thanks a lot again you are amazing❤ Gby

Very informative tutorial, Leila.

Nice Mam😊

❤ power query

Hands down, #3. A no-brainer!!

I like 3 one... Thank you Genious

Thank you for this ... loved option 3 and your choice of Campaign Names & Responsible Person...

Great ! best way for me #3

This is super useful!

Hi, can you recommend or plan to create a crash course for Microsoft Office beginner to intermediate?

wow nice short cut keys i like, thanks

Power query, for sure 💪🏼