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.
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
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 :)
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
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.
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. 👍
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 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 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 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.
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.
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!!! :)
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.
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!
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!
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!!
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 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!
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.
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!
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.
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 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.
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.
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.
You make everything seem so much simpler, thank you!
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
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 :)
Thanks 👍 I will try the last one 😊
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
I like option 3 too, thanks Leila!
Thank you Leila for these great ideas, I would use the 2nd way.
I wish if you could share more Power Bi vidoes.
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.
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.
Simple, concise, relevant and substantial. You appeal to my way of learning. Thank you Leila!
Thank you for this ... loved option 3 and your choice of Campaign Names & Responsible Person...
That's exactly what I have been looking for. Both #2 & #3 are my favorites. Love your clear and concise teaching! Thank you.
Hi Leila Jaan,
Thank you for the amazing video. I especially appreciate the second method for its simplicity and innovetiveness. 👍
Impressive, many thanks Leila..yup 3rd option
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. 👍
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? 😊
Easier said than done! 🙏🤲.
Good luck!
I definitely like option 3, thanks for the video!
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.
Loved the lesson Leila! I'm a fan of your content. My favorite is definitely the Power Query.
Awesome! 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 ..... Thank you.
thank you!! Power Query is the best!! Maravilloso!! Awesome!
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.
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.
You are always a lifesaver 😊
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 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 this useful video.
How can regular line breaks be added to data ranges in a full dataset?
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.
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!
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!
All methods are great, thanks for sharing your knowledge.
Our pleasure!
As usual wonderful!
Really cool. My favourite is power query ❤
Thanks Leila, This is great job 👋👋👋
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
Watching this in 2024 and Leila is the real GOAT. Thumbs up
Great video! Just wondering, does macbook have a home key?
Hands down, #3. A no-brainer!!
Always make backup copy before starting.
Another great lesson. Thank you for the wealth of knowledge you have and are continuing to share with me
You are so welcome!
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!! That name range thing with Power Query is nice.
Glad you like it!
Powerquery is my obvious choice. But, I just learnt how to create a named range and use it in the powerquery. Thank you
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 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!
👍
Awesome, thank you. Love the Breaking Bad reference! 😊
Very informative tutorial, Leila.
Glad you think so!
This was so helpful
I like the Power Query method
Using Power Query is great
I apply all three tricks but the 3rd one is super for data integrity.
Brilliant, thanks for sharing 🌹
wow nice short cut keys i like, thanks
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 personally do the one with counta a lot, but will definately checkout powerquery
You are simply the best.
How about the lazy way? Sort the range and let the empty ones go to the end😂
Sounds interesting, explain a bit
Please share videos to make learn excel convenient for chartered accountants
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!
I like 3 one... Thank you Genious
Thank you! Cheers!
Thank you, Leila👍
Brilliant tip!
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 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?
Two things I loved about this video:
1. The Power Query method
2. The Breaking Bad references ♥️
I clicked away and was looking for the range and then you explained it. Amazing video!
This is very, very cool. I'm embarrassed how long I've spent removing blank rows
Not anymore. 😁
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 🧑🍳🚙💊
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 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👍🏻👍🏻👍🏻
This is super useful!
Great ! best way for me #3
Very cool! Thanks!
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.
Definitely no 3 if I’m going to have to do that a lot
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!
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
Hi, can you recommend or plan to create a crash course for Microsoft Office beginner to intermediate?
Number 3, no question!
More I use power query the more I am loving it
That's what happens. 😊
Thanks a lot again you are amazing❤ Gby
Power Query is the best
👍
Method #3. Very nice.
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.
Thank the Lord!...Perfect
Power query is my favorite, but the other two ways are fun.
Thank you very much
Nice Mam😊
Hi Leila, in method 2, shouldn't we select the visible cells before deleting or does it get selected automatically?