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
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!
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.
When I have to remove blank rows or columns I use the 1st way but I was surprised when you used "counta" formula for removing blank rows, 3rd one is super fast and new for me, that is why I subscribed your channel for learning something new here. Thanks Leila mam. ❤
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. 👍
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!!! :)
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.
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!
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 :)
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? 😊
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
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!
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!
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!
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
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'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.
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.
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.
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 ...". 😁 ... 😂🤣😂... 😂🤣😂 ...😂🤣😂
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.
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!!
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
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.
All three method were effective, power query is amazing.
Leila, This is great. i like the simplicity of #2 the best.
Glad you like it!
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?
Simple, concise, relevant and substantial. You appeal to my way of learning. Thank you Leila!
Thanks for sharing your excel tips, there is always something new to learn.
You make everything seem so much simpler, thank you!
Very Helpful. Thank you!
That's exactly what I have been looking for. Both #2 & #3 are my favorites. Love your clear and concise teaching! Thank you.
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!
I like option 3 too, thanks Leila!
Loved the lesson Leila! I'm a fan of your content. My favorite is definitely the Power Query.
Awesome! Thank you!
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.
When I have to remove blank rows or columns I use the 1st way but I was surprised when you used "counta" formula for removing blank rows, 3rd one is super fast and new for me, that is why I subscribed your channel for learning something new here.
Thanks Leila mam. ❤
Hi Leila Jaan,
Thank you for the amazing video. I especially appreciate the second method for its simplicity and innovetiveness. 👍
Your excel tips always beautiful like you . cheers!!!!
These tutorials are so informative and concise! I've learned so much over the years! Keep up the good work
Glad you like them! 🙏
Thanks Leila - Exactly what I was looking for!
I definitely like option 3, thanks for the video!
thank you!! Power Query is the best!! Maravilloso!! Awesome!
Thank you for this ... loved option 3 and your choice of Campaign Names & Responsible Person...
You are always a lifesaver 😊
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. 👍
Thank you
Thanks. I always find something new to me when watching your videos. This time it was the named range. 👍
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 for sharing. Knowledgeable
Thank you Leila for these great ideas, I would use the 2nd way.
I wish if you could share more Power Bi vidoes.
Thanks 👍 I will try the last one 😊
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.
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.
Another great lesson. Thank you for the wealth of knowledge you have and are continuing to share with me
You are so welcome!
Hands down, #3. A no-brainer!!
All methods are great, thanks for sharing your knowledge.
Our pleasure!
Thanks
Really the 3rd way is great and learn the new way to use power query without turning the range into table
I like Option 3 ..... Thank you.
Brilliant tip!
Very informative tutorial, Leila.
Glad you think so!
As usual wonderful!
Thanks Leila, This is great job 👋👋👋
Thank you! 😃
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!
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 are simply the best.
Easier said than done! 🙏🤲.
Good luck!
Really cool. My favourite is power query ❤
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👍
Impressive, many thanks Leila..yup 3rd option
Power Query is the best
👍
This is super useful!
I clicked away and was looking for the range and then you explained it. Amazing video!
Thanks!! That name range thing with Power Query is nice.
Glad you like it!
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?
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
Filter + sort by column with data - 2 seconds :)
It did work. Haha thank you!😊
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!
👍
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!
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!
Never used Named Range with Power Query but great idea w/ #3!
Brilliant, thanks for sharing 🌹
I like the Power Query method
This was so helpful
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.
I apply all three tricks but the 3rd one is super for data integrity.
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
Using Power Query is great
Nice Mam😊
Method #3. Very nice.
wow nice short cut keys i like, thanks
Thanks!
Very cool! Thanks!
Power query, for sure 💪🏼
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'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.
Thank you very much
It’s great information
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.
I like 3 one... Thank you Genious
Thank you! Cheers!
Number 3, no question!
Great ! best way for me #3
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.
😁
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 😂🤣😂 ... 👍👍
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 🧑🍳🚙💊
Thanks a lot again you are amazing❤ Gby
you are the best
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 the Lord!...Perfect
Power query is my favorite, but the other two ways are fun.
Awesome, thank you. Love the Breaking Bad reference! 😊
Definitely no 3 if I’m going to have to do that a lot
I love power query
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!
This is very, very cool. I'm embarrassed how long I've spent removing blank rows
Not anymore. 😁
Powerquery is my obvious choice. But, I just learnt how to create a named range and use it in the powerquery. Thank you
This is so awesome, that’s what I need for my report now with just few clicks 😂
I personally do the one with counta a lot, but will definately checkout powerquery