Our Black Friday Sale is here! 🎉 link.xelplus.com/yt-bf-savings 👉 Grab exclusive discounts today. Master data analysis, Excel, Power BI, and more... This offer won’t last long-don’t miss it!
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.
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.
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.
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!!❤
@@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.
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!
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 :)
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.
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
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.
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.
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!!! :)
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 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!
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!
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!
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? 😊
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. 👍
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.
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!!
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.
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.
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.
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 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.
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.
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
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.
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👍🏻👍🏻👍🏻
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.
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 ...". 😁 ... 😂🤣😂... 😂🤣😂 ...😂🤣😂
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.
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.
i think each method has its merits. if it's a spreadsheet that is not going to be updated much, or is just temporary, then methods 1 and 2 are nice. method 3 is great if its a spreadsheet that is part of a workflow
What I usually do in this case is to use the sort function: if I have an identifier column, like the date in this case, I would select the entire column, and then simply sort it either in ascending or descending order (depending on the situation), and I make sure that I expand the selection to the entire table, not just the column. This automatically puts all the empty rows at the bottom, without the need to delete anything. And if there's data missing in my identifier column they will also all be grouped at the end, so that I can check them manually if I have to
Our Black Friday Sale is here! 🎉 link.xelplus.com/yt-bf-savings
👉 Grab exclusive discounts today. Master data analysis, Excel, Power BI, and more...
This offer won’t last long-don’t miss it!
Hmmmmm 😁
My sir teach this trick
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.
Thank you for sharing this! There is always more to learn. 😊
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.
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.
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!!❤
Our pleasure! Glad you liked our Power Query course.
How was the course? What were the fees? If it's available offline?
Leila, This is great. i like the simplicity of #2 the best.
Glad you like it!
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!
How was the course? What were the fees? If it's available offline?
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.
@@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.
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!
Glad it was helpful!
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 :)
You make everything seem so much simpler, thank you!
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.
How was the course? What were the fees? If it's available offline?
I like option 3 too, thanks Leila!
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
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.
Loved the lesson Leila! I'm a fan of your content. My favorite is definitely the Power Query.
Awesome! Thank you!
These tutorials are so informative and concise! I've learned so much over the years! Keep up the good work
Glad you like them! 🙏
Simple, concise, relevant and substantial. You appeal to my way of learning. Thank you Leila!
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.
Hi Leila Jaan,
Thank you for the amazing video. I especially appreciate the second method for its simplicity and innovetiveness. 👍
That's exactly what I have been looking for. Both #2 & #3 are my favorites. Love your clear and concise teaching! Thank you.
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.
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!!! :)
Thanks. I always find something new to me when watching your videos. This time it was the named range. 👍
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!
Happy to help!
@@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!
I definitely like option 3, thanks for the video!
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!
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!
Glad you found something helpful!
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? 😊
Thank you Leila for these great ideas, I would use the 2nd way.
I wish if you could share more Power Bi vidoes.
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. 👍
Thanks Leila - Exactly what I was looking for!
Thank you for this ... loved option 3 and your choice of Campaign Names & Responsible Person...
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.
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 🧑🍳🚙💊
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.
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!!
No way, basic Power Query is easy. You should give it a try.
@@LeilaGharani Not sure it is in my version of Excel, I'll have a look - thanks!
I clicked away and was looking for the range and then you explained it. Amazing video!
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.
Thanks 👍 I will try the last one 😊
Easier said than done! 🙏🤲.
Good luck!
Another great lesson. Thank you for the wealth of knowledge you have and are continuing to share with me
You are so welcome!
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.
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.
😁
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!
👍
Watching this in 2024 and Leila is the real GOAT. Thumbs up
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.
thank you!! Power Query is the best!! Maravilloso!! Awesome!
This is very, very cool. I'm embarrassed how long I've spent removing blank rows
Not anymore. 😁
Hands down, #3. A no-brainer!!
All methods are great, thanks for sharing your knowledge.
Our pleasure!
As usual wonderful!
Thanks!! That name range thing with Power Query is nice.
Glad you like it!
Really cool. My favourite is power query ❤
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.
Thanks Leila, This is great job 👋👋👋
Thank you! 😃
Impressive, many thanks Leila..yup 3rd option
Very informative tutorial, Leila.
Glad you think so!
This was so helpful
I like Option 3 ..... Thank you.
Powerquery is my obvious choice. But, I just learnt how to create a named range and use it in the powerquery. Thank you
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
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 like the Power Query method
I apply all three tricks but the 3rd one is super for data integrity.
Two things I loved about this video:
1. The Power Query method
2. The Breaking Bad references ♥️
Always make backup copy before starting.
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👍🏻👍🏻👍🏻
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.
Fantastic! Glad the video was helpful.
Power Query is the best
👍
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 ...". 😁 ... 😂🤣😂... 😂🤣😂 ...😂🤣😂
Thank you so much for tuning in again! Next time 2 slices. 😁
@@LeilaGharani 😂🤣😂 ... 👍👍
wow nice short cut keys i like, thanks
Nice Mam😊
Awesome, thank you. Love the Breaking Bad reference! 😊
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.
Thanks for sharing, Geert!
Using Power Query is great
Brilliant, thanks for sharing 🌹
This is super useful!
You are simply the best.
Brilliant tip!
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.
Thank the Lord!...Perfect
More I use power query the more I am loving it
That's what happens. 😊
Power query, for sure 💪🏼
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
oh love that! Thank you.
Power query is my favorite, but the other two ways are fun.
Number 3, no question!
Very cool! Thanks!
I personally do the one with counta a lot, but will definately checkout powerquery
Thank you, Leila👍
Great ! best way for me #3
Thanks for this useful video.
How can regular line breaks be added to data ranges in a full dataset?
Definitely no 3 if I’m going to have to do that a lot
I like 3 one... Thank you Genious
Thank you! Cheers!
i think each method has its merits. if it's a spreadsheet that is not going to be updated much, or is just temporary, then methods 1 and 2 are nice. method 3 is great if its a spreadsheet that is part of a workflow
👍
Method #3. Very nice.
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
Thanks a lot again you are amazing❤ Gby
What I usually do in this case is to use the sort function: if I have an identifier column, like the date in this case, I would select the entire column, and then simply sort it either in ascending or descending order (depending on the situation), and I make sure that I expand the selection to the entire table, not just the column. This automatically puts all the empty rows at the bottom, without the need to delete anything. And if there's data missing in my identifier column they will also all be grouped at the end, so that I can check them manually if I have to
I love power query
Great video! Just wondering, does macbook have a home key?
Please share videos to make learn excel convenient for chartered accountants
How about the lazy way? Sort the range and let the empty ones go to the end😂
Sounds interesting, explain a bit