Download the practice file I used in the video: xelplus.ck.page/remove-blank-rows-file. And if you're ready to make Excel even easier, check out my Power Query course for automating tasks 👉 www.xelplus.com/course/excel-power-query/ or learn about Pivot Tables to analyze data faster 👉www.xelplus.com/course/excel-pivot-tables/. These tools will save you tons of time!
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!!❤
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 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.
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.
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!!! :)
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. 👍
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.
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 :)
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!
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👍🏻👍🏻👍🏻
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!!
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.
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.
The 3rd method seems best. My only question would be can it handle the rows where say the date is blank, but there is data in the rest of the row, like you showed in example 2.
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 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
Or you could use the FILTER function. With the data you used for Power Query the function would be: =FILTER(A2:H196,A2:A196"") ETA: For the second data set, it would be =FILTER(A2:H392,BYROW(A2:H392,LAMBDA(t,TEXTJOIN("",,t)))"")
Tbh, firstly agreed with you. But if cell with date is blank (like example date is given once per day in upper cell), but all other cells of row are filled with the same values - row will be removed as duplicated. Example: john washed 3 cars per whole day, and earned 60 euros. And this repeated in three days. But dates were different 15/10 and 18/10. And as described higher on example if cell with date is blank. Then instead of two rows you will get one, as duplicate will think that all data is the same and will remove duplicated row.
@@keylanoslokj1806 not you, me, if i couldn't describe well) Shortly, you have 5 columns like: 1 date (can be blank) , 2nd (Mika/John), 3rd (car wash/waxing), 4th (1/3/73 cars per day), 5th (how much money was earned). And example John washed 3 cars an got 60 euros on 15/10 and the same on 18/10. So in this case you will have same data in two rows. But the first column where the date is blank for these rows. So it will be duplicated, right? And John washed them 15/10 and 18/10. So if you remove one row as a duplicate - you will lose some data.
@@keylanoslokj1806 so, the main idea is: iIf you want to remove duplicates based on 5 columns, you should be sure that Excel will not understand some rows as duplicated. Or you may lose some rows as removed by duplicate. And on example instead of 100 rows - you will get 98 rows. As two had the same information and were removed. But examples shown by leila will not remove such rows, as at least there is some info given.
@@keylanoslokj1806my additional comment disappeared) So, if you want to use remove duplicate by 5 columns - you should be sure that some of rows will not have the same information in each of columns. As instead of 100 rows you can get 98, as two were removed as fully had the same information. And examples given by Leila avoid this, as if there are at least some info - rows will not be deleted.
I wish there was a quick way to get to the end of a row, bypassing blank cells. ctrl+shift+end gets me to the end of everything, then I can go to the row I want, but wish there was a hot key...
Why not just ask the AI of Excel to delete all blank rows, that should be simpler and faster, right? Thanks, cool time saving when deleting multiple blank rows! 🥰✨💎
I like way 1. But it can be easier that you just choose the whole first column, choose the blank cells and delete the whole rows. Key point is to choose the whole column.
![COLOMBIA vs. CHILE [4-0] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 10](http://i.ytimg.com/vi/TDjN4W3yy1k/mqdefault.jpg)
Download the practice file I used in the video: xelplus.ck.page/remove-blank-rows-file. And if you're ready to make Excel even easier, check out my Power Query course for automating tasks 👉 www.xelplus.com/course/excel-power-query/
or learn about Pivot Tables to analyze data faster 👉www.xelplus.com/course/excel-pivot-tables/.
These tools will save you tons of time!
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.
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 like option 3 too, thanks Leila!
Leila, This is great. i like the simplicity of #2 the best.
Glad you like it!
thank you!! Power Query is the best!! Maravilloso!! Awesome!
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.
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.
That's exactly what I have been looking for. Both #2 & #3 are my favorites. Love your clear and concise teaching! Thank you.
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!!! :)
Simple, concise, relevant and substantial. You appeal to my way of learning. Thank you Leila!
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 😂🤣😂 ... 👍👍
I definitely like option 3, thanks for the video!
Thank you Leila for these great ideas, I would use the 2nd way.
I wish if you could share more Power Bi vidoes.
I like the Power Query method
Brilliant, thanks for sharing 🌹
Power Query is the best
👍
Very cool! Thanks!
You are simply the best.
Thank you, Leila👍
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.
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 :)
Method #3. Very nice.
I apply all three tricks but the 3rd one is super for data integrity.
Thanks!! That name range thing with Power Query is nice.
Glad you like it!
This is very, very cool. I'm embarrassed how long I've spent removing blank rows
Not anymore. 😁
All methods are great, thanks for sharing your knowledge.
Our pleasure!
Thanks!
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 very much
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!
More I use power query the more I am loving it
That's what happens. 😊
Hi Leila, in method 2, shouldn't we select the visible cells before deleting or does it get selected automatically?
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.
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👍🏻👍🏻👍🏻
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!
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! 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 🧑🍳🚙💊
The Power Query option... the best option
👍
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
I love power query
I'm a big fan of power query option 3
Power query is my favorite, but the other two ways are fun.
Love from Bangladesh 🎉
I liked some of the shortcuts best, now how about inserting blank rows at given intervals or change in value ?
Power Query is great for that 😀
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!
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.
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 have some VBA to do this
Can you post the code
The 3rd method seems best. My only question would be can it handle the rows where say the date is blank, but there is data in the rest of the row, like you showed in example 2.
That and much more. Power Query is amazing.
Two things I loved about this video:
1. The Power Query method
2. The Breaking Bad references ♥️
Leila what if the case is like- in same data set two cells are merged but one has content & another not..how can I demerge them in one time.
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 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
👍
I like option 1. It doesn't require you to either create a helper column or rename your table range etc.
All methods are good depending on the need.
👍
Definitely 2 & 3 option...2nd is more useful when two or more cell are merged
Or you could use the FILTER function. With the data you used for Power Query the function would be: =FILTER(A2:H196,A2:A196"")
ETA: For the second data set, it would be =FILTER(A2:H392,BYROW(A2:H392,LAMBDA(t,TEXTJOIN("",,t)))"")
I prefer method nr 3 :)
👍
"Remove Duplicates" will also leave you with a single blank row to delete
Tbh, firstly agreed with you. But if cell with date is blank (like example date is given once per day in upper cell), but all other cells of row are filled with the same values - row will be removed as duplicated.
Example: john washed 3 cars per whole day, and earned 60 euros. And this repeated in three days. But dates were different 15/10 and 18/10. And as described higher on example if cell with date is blank. Then instead of two rows you will get one, as duplicate will think that all data is the same and will remove duplicated row.
@@doomed1389damn I'm too dumb to understand that😂
@@keylanoslokj1806 not you, me, if i couldn't describe well)
Shortly, you have 5 columns like: 1 date (can be blank) , 2nd (Mika/John), 3rd (car wash/waxing), 4th (1/3/73 cars per day), 5th (how much money was earned). And example John washed 3 cars an got 60 euros on 15/10 and the same on 18/10. So in this case you will have same data in two rows. But the first column where the date is blank for these rows. So it will be duplicated, right? And John washed them 15/10 and 18/10. So if you remove one row as a duplicate - you will lose some data.
@@keylanoslokj1806 so, the main idea is: iIf you want to remove duplicates based on 5 columns, you should be sure that Excel will not understand some rows as duplicated. Or you may lose some rows as removed by duplicate.
And on example instead of 100 rows - you will get 98 rows. As two had the same information and were removed. But examples shown by leila will not remove such rows, as at least there is some info given.
@@keylanoslokj1806my additional comment disappeared)
So, if you want to use remove duplicate by 5 columns - you should be sure that some of rows will not have the same information in each of columns. As instead of 100 rows you can get 98, as two were removed as fully had the same information. And examples given by Leila avoid this, as if there are at least some info - rows will not be deleted.
Power Query
Power query is the way forward
It sure is. :)
#2. I've used something like that before.
Alternative: Sort the rows based on the unique keys and name of person and the blank lines can be selected and deleted at once.
PQ ofcourse. But on simple data set, method 1 just works faster 😊
👍
How about the lazy way? Sort the range and let the empty ones go to the end😂
Got here first
Just sort the dataset?
I wish there was a quick way to get to the end of a row, bypassing blank cells. ctrl+shift+end gets me to the end of everything, then I can go to the row I want, but wish there was a hot key...
I am Doing Just Shorting data and Done..
Always make backup copy before starting.
Why not just ask the AI of Excel to delete all blank rows, that should be simpler and faster, right? Thanks, cool time saving when deleting multiple blank rows! 🥰✨💎
I would progbably use option 2 but i can see uses for option 3
LOL, I just noticed the Breaking Bad data...
I like option 3. I am forcing myself to use Power Query instead of Excel functions for data cleaning tasks.
👍👍😃😃
I filter, select only the blanks, then delete them all at once.
2137 👀
🇻🇦
U r cute 🥰
A girl that knows more than plugging in a PC? Impossibru!
I like way 1. But it can be easier that you just choose the whole first column, choose the blank cells and delete the whole rows. Key point is to choose the whole column.