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

[@LeilaGharani]

Want to save hours on Excel tasks? Learn how to automate with Power Query 👉 www.xelplus.com/course/excel-power-query/ Download the practice file I used in this video here: xelplus.ck.page/remove-blank-rows-file

[@WaqasAli-dq9wg]

Hmmmmm 😁

[@Killer_smile.45]

My sir teach this trick

[@sarienvanschalkwyk6318]

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.

[@LeilaGharani]

Thank you for sharing this! There is always more to learn. 😊

[@KStew_UM]

Same. :D I watched because I love Leila. I'm learning more than just tricks. I'm learning how to do fantastic training sessions for my team. My usual way is to apply a filter and remove rows with no date (which is business critical for me). But Power Query is better in that you don't "mess" with the raw data.

[@karenmackstewart]

Same. :D I watched because I love Leila. I'm learning more than just tricks. I'm learning how to do fantastic training sessions for my team. My usual way is to apply a filter and remove rows with no date (which is business critical for me). But Power Query is better in that you don't "mess" with the raw data. What I would do though is to make the entire columns the named range. It is bad practice to add non-data rows below a data set - or anywhere on the data sheet. One of my pet corns.

[@ruthngmj]

I have been using method 1 and 2. even I complete your power query course (money well spent!) method 3 still blows my mind! I always think we have to use table for power query. thanks again!!❤

[@LeilaGharani]

Our pleasure! Glad you liked our Power Query course.

[@vishalagrawal8717]

How was the course? What were the fees? If it's available offline?

[@patrickpointer8380]

Leila, This is great. i like the simplicity of #2 the best.

[@LeilaGharani]

Glad you like it!

[@pauljackson2436]

I'm up to section 18 in your PQ course. The skills I've learnt so far have already saved my team a heap of time. Awesome!

[@vishalagrawal8717]

How was the course? What were the fees? If it's available offline?

[@LeilaGharani]

Thanks so much for your feedback! I'm so happy to hear you're already using these skills at work-that's what it's all about.

[@StephanBenne]

@@vishalagrawal8717 The course was "Excel Power Query Beginner to Advanced (including M)" I found it on Udemy, but is not available any more. It is a great course. Leila takes you step by step to the course.

[@JeffreyLawrence-w6o]

You make everything seem so much simpler, thank you!

[@moncacas]

What a fantastic surprise Leila! While I was familiar with removing blank rows in Excel, your video taught me two amazing features I never knew about. Loading data into Power Query using a Range and sending it directly to a PivotTable are absolute game-changers! It's wonderful how even familiar tools can still surprise us with hidden gems. These new shortcuts will definitely streamline my workflow. Thank you for sharing this valuable knowledge!

[@LeilaGharani]

Glad it was helpful!

[@mramsch]

Option 4: Add an index helper column (1, 2, 3, ...) and then use sorting to get all the rows you want to do something with (e.g. empty rows for deletion) into a block of consecutive rows. Now it is easy to edit (e.g. delete) these rows. When you have finished manual editing, restore the original order by sorting according to the index column. It is not updated automatically, as is the case with option 3. However, it is practical if you only want to edit your data once and want to include several different criteria for editing that may be difficult to automate but are easy to do manually. But usually option 3 is my favorite too :)

[@keishrich]

Simple, concise, relevant and substantial. You appeal to my way of learning. Thank you Leila!

[@rosalindchen7935]

That's exactly what I have been looking for. Both #2 & #3 are my favorites. Love your clear and concise teaching! Thank you.

[@chiwilanjabulosikwila3487]

Great video! At 1:51 another option is to press the 'F5' key instead of Ctrl+G to get to the Go To dialogue box.

[@stuartmalevich8093]

Your my new goto to learn how to make my work managable. I also watch because you have such a patient voice it makes it easier to pay attention and follow along.

[@kayvankoohestani1889]

Hi Leila Jaan, Thank you for the amazing video. I especially appreciate the second method for its simplicity and innovetiveness. 👍

[@francisaranha]

Loved the lesson Leila! I'm a fan of your content. My favorite is definitely the Power Query.

[@LeilaGharani]

Awesome! Thank you!

[@daleanderson5258]

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.

[@shawnpaiva2347]

These tutorials are so informative and concise! I've learned so much over the years! Keep up the good work

[@LeilaGharani]

Glad you like them! 🙏

[@chrism9037]

I like option 3 too, thanks Leila!

[@johncouture3890]

Thanks. I always find something new to me when watching your videos. This time it was the named range. 👍

[@abdanomer]

Thank you Leila for these great ideas, I would use the 2nd way. I wish if you could share more Power Bi vidoes.

[@kurtjohnson1341]

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.

[@BB0015]

Thanks Leila - Exactly what I was looking for!

[@weightmn]

I definitely like option 3, thanks for the video!

[@smitabidnur4401]

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

[@leerv.]

Leila, thank you!! I can't believe that all this time I didn't realize you could import a named range into PQ! I always thought it had to be a table..... yes, even though the action is named From Table/Range. Thank you!!! :)

[@chh8860]

ALWAYS a treat when I receive a notification of another 'Leila' instructional video (caught me a bit by surprise this morning ... I had black coffee in hand, but no pound cake ... 😞). I don't deal with such large data sets. But once again, I find myself smiling during Lelia's delivery and thinking " ... oh, that's pretty cool ...". I had no idea these options were available. I would likely use #1 as I am still a 'Not-Ready-For-Primetime' Nija when it comes to Power Query and Pivot Tables. But I am moving closer. Leila's ideas and delivery are still SO good. Thank you ... thank you ... thank you ... 😍😍😍. PS At the 6:42 mark, " ... I am not a fan of these table styles, I am just going to go and remove this ...". 😁 ... 😂🤣😂... 😂🤣😂 ...😂🤣😂

[@LeilaGharani]

Thank you so much for tuning in again! Next time 2 slices. 😁

[@chh8860]

@@LeilaGharani 😂🤣😂 ... 👍👍

[@MrSparkefrostie]

Your advice has helped, although I have move far along from excel, what has helped me to this day is the arrays, if I can add, I would use the filter formula, that way you can apply it accross a certain amount of columns but all rows, will likely need aome trickery to see if the specific colum is not blank, even more trickery to check all columns

[@jackennis9258]

Excellent video Leila! I’ve purchased many of your courses, including your first Power Query course - I’d love a comprehensive course on M and using M functions - have you any plans to develop a course on advanced M? 😊

[@andraveda]

Hi Master Leila. Thank You! I love the power query method (#3) too.. I used to work with a lot of data source, and i use power query to gather these files all. It's realible and so fast. 👍

[@melissabrown1068]

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

[@LeilaGharani]

You are so welcome!

[@apraghavendra]

Hello Leila. I keep watching all your videos. It's really great. Thank you for these simple teachings. I have a question: when power query is used on large data, it's very slow. How can this be avoided? Please help

[@StephanBenne]

I finished your Power Query course. (Like some said before "Money well spent") And I'm a big fan of Power Query , but in this case I like option 1. It is fast and simple and it prevents making a duplicate data set.

[@vishalagrawal8717]

How was the course? What were the fees? If it's available offline?

[@fareedbadar8793]

As usual wonderful!

[@arslanm83]

You are always a lifesaver 😊

[@lftr_react]

Ever want to get rid of blank rows in your dataset? Here's a feature that gives you vast data manipulation and can automate tasks and functions until the report is practically a living being... oh yea, and remove blank rows! Great stuff as always!

[@shoboyjr]

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!

[@LeilaGharani]

Glad you found something helpful!

[@mrdennischapman]

I really love your great video's along with the gorgeous smile. I have another way to do this, which I think is the most simple. To delete blank rows I sort the first column by whatever is in there and the blank rows appear at the bottom. Then I select all of those blank rows and delete. It only takes me about 20 seconds to do this.

[@leorc564]

thank you!! Power Query is the best!! Maravilloso!! Awesome!

[@Ticky66MN]

I just right click in a blank and filter by selection and I keep delete rows in my custom ribbon to remove them quickly. Thanks Leila! I do need to use PQ more and more though.

[@gukejing]

Really the 3rd way is great and learn the new way to use power query without turning the range into table

[@extraktAI]

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

[@karhukivi]

As always, Leila you're a star! I deal with very large data sets and when I sort them there are large numbers of blank rows which can be treated as "zero" values by some statistical operations in other software, so I need to remove blank rows. I found a fix using other software, but then the dataset has to be transferred back to Excel and this means another file name/extension etc. So your tutorial arrived at a good time and as I added my "like" I saw it was a nice binary number of 2^8 - must be a sign 🙂 Thank you!

[@LeilaGharani]

Happy to help!

[@karhukivi]

@@LeilaGharani My data sets of geophysical data often have more than 1 million rows and 20 to 40 columns. To use them I usually have to filter them by the coordinates to a smaller size for the area of interest, so perhaps 5000 rows and 10 columns. Very unwieldy stuff!

[@brentinokinawa]

I've been watching your videos for about 2 years now, and you've helped me SO MUCH! THANK YOU! But what if you needed to add rows between data...and I mean a bunch of rows where I need to add a blank row below each one. Is there an easy way to do that? Thanks again, and keep the good stuff coming.

[@tamer9898]

Thanks Leila, This is great job 👋👋👋

[@LeilaGharani]

Thank you! 😃

[@BobG-eh5fc]

All methods are great, thanks for sharing your knowledge.

[@LeilaGharani]

Our pleasure!

[@sunitha5467]

Thanks 👍 I will try the last one 😊

[@jasonipfatsiong2062]

Impressive, many thanks Leila..yup 3rd option

[@svenwagner4270]

THANK YOU! i started as a BDM this week and i spend so much time on removing them by hand. And i love your Breaking Bad Data 🧑‍🍳🚙💊

[@kenmcmillan2637]

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

[@francesdobbins2964]

Very informative tutorial, Leila.

[@LeilaGharani]

Glad you think so!

[@neciwillis2055]

This was so helpful

[@humansizedaperture]

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!

[@LeilaGharani]

👍

[@davida6955]

Go to special has long been my favorite, but the CountA helper column is very cool. Sometimes I get bogged down in Power Query efforts and they don't give me the results I want or it introduces complications that annoy...Still a great way to do it. So easy.

[@prashantk.a.6849]

Really cool. My favourite is power query ❤

[@zro.tolerance]

Thanks!! That name range thing with Power Query is nice.

[@LeilaGharani]

Glad you like it!

[@CB-gb7uq]

Awesome, thank you. Love the Breaking Bad reference! 😊

[@cherifcherif5652]

Hi ! Thanks for your videos. Could you please make a simple video to show us how we can combine multiple sheets of an excel file dynamically so if I add another sheet ,my new file will be updated,thank you in advance

[@RTWRTW-RTWRTW]

Easier said than done! 🙏🤲. Good luck!

[@RABacct]

All of them are good. The most useful for me is #3, as I'm often having to deal with changing data. Also, was so busy following steps, I just noticed the responsible people and their activities lol.

[@LeilaGharani]

😁

[@brucepeaslee3952]

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.

[@LeilaGharani]

Fantastic! Glad the video was helpful.

[@teoxengineer]

Thanks for this useful video. How can regular line breaks be added to data ranges in a full dataset?

[@isalmankhan1]

WoW Amazing Thanks for sharing, you're the Best in the World👍🏻👍🏻👍🏻 I hope someday I will be a pro learning from your channel, it's a blessing😍😍😍 Love your work, huge fan👍🏻👍🏻👍🏻

[@Aftab_Ahmad_ajk]

I apply all three tricks but the 3rd one is super for data integrity.

[@karhukivi]

Hi Leila, method #1 works for me as the X and Y coordinates of data are essential identifiers - no coordinates and the data in that row is worthless. I don't think I can use method #3 power query and it seems rather complicated for me anyway. Method #2 using the "count entry cells" looks very useful for other operations where some data is missing. Thank you again!!

[@LeilaGharani]

No way, basic Power Query is easy. You should give it a try.

[@karhukivi]

@@LeilaGharani Not sure it is in my version of Excel, I'll have a look - thanks!

[@ayanray88]

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

[@albertokusmic9239]

Brilliant, thanks for sharing 🌹

[@vartikcsaba]

This is super useful!

[@remco2777]

Brilliant tip!

[@jignasajoshi27]

I like Option 3 ..... Thank you.

[@adanudave886]

You are simply the best.

[@sevetp]

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.

[@Labeleverypart]

wow nice short cut keys i like, thanks

[@NicolasChanCSY]

The first and second methods are great most of the time, unless the range is referenced by many formula, and in such case Excel will take a long time to update all references for each row deletion. Then, creating a copy based on COUNTA or PQ would be more efficient.

[@khokhar4931]

Thank you, Leila👍

[@JosephFallon]

This is very, very cool. I'm embarrassed how long I've spent removing blank rows

[@LeilaGharani]

Not anymore. 😁

[@muraliramachandran1064]

Using Power Query is great

[@AnbarasuAnnamalai]

I like the Power Query method

[@aymenptg7246]

Hey, thanks for all the efforts you do, to make Excel Easy for us. I just wanted to ask about TopN option in Pivot Table Lesson, i don't know why (Sales Man, Quarters, and Years) in row field, are collapsed for me in just one column, they don't appear to me like they do to you(in separated columns).

[@SAKE42able]

Power Query is the best

[@LeilaGharani]

👍

[@asmrindia]

Nice Mam😊

[@samshoes1]

Number 3, no question!

[@GeertDelmulle]

All of the above. BTW: I like to use Excel Tables, too, so I have that dynamic aspect as well. For me I like to go with the dynamic route (and the data would be in a separate file, therefore PQ-M for the win, here.

[@LeilaGharani]

Thanks for sharing, Geert!

[@jorgelara3116]

Thanks a lot again you are amazing❤ Gby

[@junejaundoo3827]

Power query, for sure 💪🏼

[@reyneriolanzas71]

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?

[@flaviogarlatticosta]

Great ! best way for me #3

[@extraktAI]

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

[@deliabee]

Method #3. Very nice.

[@MrSupernova111]

Very cool! Thanks!

[@johnphilippatos]

I sometimes like to practice the old ways. I would add a column before column A and then I would create a numerical sequence (1,2,3...etc.) from cell A1 all the way downwards to the last row of my dataset. I would create another column before A, and from cell A1 and downwards, I would use, either the COUNTA function the way you used it (more failsafe and quick), or, if I would like to punish myself some more, I would use an OR function featuring inside every cell in the row like this: OR(C1"",D1"",......) and so on, nested inside an IF function that would convert TRUE and FALSE to 1 and 0 respectively. The complete function would look like this: =IF(OR(C1"",D1""......),1,0). I would autofill the function downwards, so that if a row is completely blank I'd get a 0, or an 1, if any of the cells was not blank. Then, I would sort the whole dataset ascending, by column A firstly and column B secondly. This would put all the zeros a.k.a the blank rows together. Then, I would delete all the rows for which that the function gave back 0 and then I would remove column A, sort data again by the new column A (not a necessary step, as they will have been already sorted correctly, but just in case) and finally I would remove (the new) column A and job is done. COUNTA would work in the same way, but instead of 0 and 1, it would give me back all kinds of positive integers, depending on how many cells in a row are not blank. Nevertheless, I would still delete the rows with the zeros. From the three solutions, my vote goes to the second, but only because I'm not familiar with power query. Thanks Leila for everything.

[@gandhisunil3]

Method 3 is most certain method when we are not certain about identifying column I love Power query as i learnt from a mentor none other than you

[@LeilaGharani]

oh love that! Thank you.

[@bienvinidoquiawan9206]

It’s great information

[@dattmuffy]

@0:30 SELECT ALL COLUMN A, CONTROL G, SELECT SPECIAL, SELECT BLANKS, WHILE BLANKS ARE SELECTED GO TO HOME TAB AND DELETE BLANKS.LASTLY SHIFT CELLS UP DOWN LEFT RIGHT ETC. THANKS

[@sushilexcel4966]

I like 3 one... Thank you Genious

[@LeilaGharani]

Thank you! Cheers!

[@vinayanna]

Two things I loved about this video: 1. The Power Query method 2. The Breaking Bad references ♥️

[@michaelnewtown]

Power query is my favorite, but the other two ways are fun.

[@kamranqureshi9142]

Please share videos to make learn excel convenient for chartered accountants

[@reigngrifth]

I think method 2 is my go to since I don't have power user colleagues and its really frustrating when they break functions by doing the uninformed way

[@chintandholakiya]

I personally do the one with counta a lot, but will definately checkout powerquery

[@robertdon777]

Thank the Lord!...Perfect

[@anantamurti4346]

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

[@LeilaGharani]

So in the name box, you can't have "My Range" you can have "MyRange" or "My_Range" but the name box, doesn't accept names that are separated with a space character.

[@anantamurti4346]

​@@LeilaGharaniThank you for the clarification