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!!❤
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 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.
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'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.
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 :)
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. 👍
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!!! :)
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!
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.
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!
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 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!!
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 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.
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
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.
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.
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
Hello I have caught your channel on occasion, full of great info, may I ask if you have a formula for many Records down a sheet, I will insert your formula and drag it down to the last record. The formula can sit in COL E. If COL C has black text (default), and no CELL FILL color (default) && if COL D has black text, and no CELL FILL color, I wish to change COL D to CELL FILL YELLOW, from the RIBBON - Styles - Neutral Button (YELLOW) please. If you have a video to do something like this, can you link me to it or more than one, I would be happy to watch the video(s) and learn the function on my own, rather than simply ask for the code please. I have a very large library in Excel about Auromotive Repair UA-cam Videos with lots of notes and color coding, you can imagine over time I make some upgrade changes and this is the latest with thousands of Records to change. Thank you very much.
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)))"")
Never knew if you delete the query, that the query data will remain in place with no connection and you can use it as a stand-alone set of data!! Thanks!!
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! 🥰✨💎
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 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.
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'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!
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.
Leila, This is great. i like the simplicity of #2 the best.
Glad you like it!
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 like option 3 too, thanks Leila!
That's exactly what I have been looking for. Both #2 & #3 are my favorites. Love your clear and concise teaching! 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.
Simple, concise, relevant and substantial. You appeal to my way of learning. Thank you Leila!
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've been watching your videos for about 2 years now, and you've helped me SO MUCH! THANK YOU! But what if you needed to add rows between data...and I mean a bunch of rows where I need to add a blank row below each one. Is there an easy way to do that? Thanks again, and keep the good stuff coming.
I 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.
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 Leila - Exactly what I was looking for!
thank you!! Power Query is the best!! Maravilloso!! Awesome!
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. 👍
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!!! :)
Always make backup copy before starting.
Brilliant, thanks for sharing 🌹
I like the Power Query method
I apply all three tricks but the 3rd one is super for data integrity.
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!
Thank you, Leila👍
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.
Power query, for sure 💪🏼
You are simply the best.
Very cool! Thanks!
This is very, very cool. I'm embarrassed how long I've spent removing blank rows
Not anymore. 😁
Power Query is the best
👍
All methods are great, thanks for sharing your knowledge.
Our pleasure!
wow nice short cut keys i like, thanks
How about the lazy way? Sort the range and let the empty ones go to the end😂
Two things I loved about this video:
1. The Power Query method
2. The Breaking Bad references ♥️
Method #3. Very nice.
Thanks!
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.
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!
Hi, can you recommend or plan to create a crash course for Microsoft Office beginner to intermediate?
Thank you very much
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.
😁
Thanks!! That name range thing with Power Query is nice.
Glad you like it!
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!
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👍🏻👍🏻👍🏻
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 🧑🍳🚙💊
Hi Leila, in method 2, shouldn't we select the visible cells before deleting or does it get selected automatically?
I have some VBA to do this
Can you post the code
I personally do the one with counta a lot, but will definately checkout powerquery
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 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.
More I use power query the more I am loving it
That's what happens. 😊
I'm a big fan of power query option 3
Power query is my favorite, but the other two ways are fun.
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 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 😀
The Power Query option... the best option
👍
I love power query
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 agree
Power Query is quicker and adds more flex
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!
Love from Bangladesh 🎉
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.
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.
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 power Query
I like option 1. It doesn't require you to either create a helper column or rename your table range etc.
Hello I have caught your channel on occasion, full of great info, may I ask if you have a formula for many Records down a sheet, I will insert your formula and drag it down to the last record. The formula can sit in COL E.
If COL C has black text (default), and no CELL FILL color (default)
&& if COL D has black text, and no CELL FILL color,
I wish to change COL D to CELL FILL YELLOW, from the RIBBON - Styles - Neutral Button (YELLOW) please.
If you have a video to do something like this, can you link me to it or more than one, I would be happy to watch the video(s) and learn the function on my own, rather than simply ask for the code please.
I have a very large library in Excel about Auromotive Repair UA-cam Videos with lots of notes and color coding, you can imagine over time I make some upgrade changes and this is the latest with thousands of Records to change. Thank you very much.
LOL, I just noticed the Breaking Bad data...
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 would like to add more than one line at a time in the sheet when making separations. I KNOW there must be a way.
All methods are good depending on the need.
👍
Definitely 2 & 3 option...2nd is more useful when two or more cell are merged
Never knew if you delete the query, that the query data will remain in place with no connection and you can use it as a stand-alone set of data!! Thanks!!
Alternative: Sort the rows based on the unique keys and name of person and the blank lines can be selected and deleted at once.
I prefer method nr 3 :)
👍
Power query is the way forward
It sure is. :)
Just sort the dataset?
Power Query
PQ ofcourse. But on simple data set, method 1 just works faster 😊
👍
#2. I've used something like that before.
1:30
2:40
I am Doing Just Shorting data and Done..
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 would progbably use option 2 but i can see uses for option 3
I like option 3. I am forcing myself to use Power Query instead of Excel functions for data cleaning tasks.
Got here first
"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.
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.
What if my new data is inserted beyond the named range that we just created? Have to remodify the named range?
go to Formulas tab - Name Manager - edit the name and update the range 🙂