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!
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. ❤
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.
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.
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 :)
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 ...". 😁 ... 😂🤣😂... 😂🤣😂 ...😂🤣😂
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. 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!!! :)
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? 😊
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
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!
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.
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.
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
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!
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'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.
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.
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!
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.
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
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!!
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).
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.
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.
You make everything seem so much simpler, thank you!
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!
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. ❤
That's exactly what I have been looking for. Both #2 & #3 are my favorites. Love your clear and concise teaching! Thank you.
I like option 3 too, thanks Leila!
Simple, concise, relevant and substantial. You appeal to my way of learning. Thank you Leila!
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.
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.
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 :)
Very Helpful. Thank you!
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 😂🤣😂 ... 👍👍
Hi Leila Jaan,
Thank you for the amazing video. I especially appreciate the second method for its simplicity and innovetiveness. 👍
Thanks. I always find something new to me when watching your videos. This time it was the named range. 👍
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 for sharing your excel tips, there is always something new to learn.
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 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. 👍
Loved the lesson Leila! I'm a fan of your content. My favorite is definitely the Power Query.
Awesome! Thank you!
I definitely like option 3, thanks for the video!
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.
Thanks Leila - Exactly what I was looking for!
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 will try the last one 😊
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? 😊
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
Hands down, #3. A no-brainer!!
You are always a lifesaver 😊
Easier said than done! 🙏🤲.
Good luck!
thank you!! Power Query is the best!! Maravilloso!! Awesome!
Thank you Leila for these great ideas, I would use the 2nd way.
I wish if you could share more Power Bi vidoes.
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!
Thank you, Leila👍
I like Option 3 ..... Thank you.
Thank you for this ... loved option 3 and your choice of Campaign Names & Responsible Person...
I clicked away and was looking for the range and then you explained it. Amazing video!
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 🧑🍳🚙💊
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 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?
Nice Mam😊
Thanks for sharing. Knowledgeable
You are simply the best.
As usual wonderful!
I like the Power Query method
Brilliant tip!
Power Query is the best
👍
Thanks
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.
Thanks Leila, This is great job 👋👋👋
Thank you! 😃
Really the 3rd way is great and learn the new way to use power query without turning the range into table
Really cool. My favourite is power query ❤
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
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!
Brilliant, thanks for sharing 🌹
This is super useful!
Power query, for sure 💪🏼
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.
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.
Using Power Query is great
This was so helpful
Impressive, many thanks Leila..yup 3rd option
All methods are great, thanks for sharing your knowledge.
Our pleasure!
Filter + sort by column with data - 2 seconds :)
It did work. Haha thank you!😊
I apply all three tricks but the 3rd one is super for data integrity.
Method #3. Very nice.
Very cool! Thanks!
Another great lesson. Thank you for the wealth of knowledge you have and are continuing to share with me
You are so welcome!
Thanks!
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.
Very informative tutorial, Leila.
Glad you think so!
Thank you very much
Number 3, no question!
Never used Named Range with Power Query but great idea w/ #3!
Great ! best way for me #3
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.
It’s great information
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!
👍
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.
😁
wow nice short cut keys i like, thanks
I love power query
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
I like 3 one... Thank you Genious
Thank you! Cheers!
This is very, very cool. I'm embarrassed how long I've spent removing blank rows
Not anymore. 😁
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!
Thanks for this useful video.
How can regular line breaks be added to data ranges in a full dataset?
Thanks!! That name range thing with Power Query is nice.
Glad you like it!
Thanks a lot again you are amazing❤ Gby
Awesome, thank you. Love the Breaking Bad reference! 😊
Great video! Just wondering, does macbook have a home key?
Thank the Lord!...Perfect
Always make backup copy before starting.
Two things I loved about this video:
1. The Power Query method
2. The Breaking Bad references ♥️
Please share videos to make learn excel convenient for chartered accountants
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).
you are the best
Powerquery is my obvious choice. But, I just learnt how to create a named range and use it in the powerquery. Thank you