You just saved me hours of time!! I have a spreadsheet that contains over 1 million rows, many of which I don’t need, and spent a long time on a Saturday afternoon deleting them in chunks. I was going to spend more time today, a Sunday, finishing it. Thank you so much! I’ve taken at least three Excel courses, two in college, and I have never been shown this trick.
I cannot thank you enough. My Excel would freeze (not responding) for 10 to 15 minutes (yes MINUTES !) when I would try to delete multiple rows. Now using this technique, on that same sheet as before, Excel freezes for barely 30-40 seconds in deleting the same number of rows. Thank you 🙂
Excellent video. In my experience, in working with close to 200,000 rows. I find it easier to sort and just delete in place rather than then filter on the color or whatever you're using.
Many thanks, Jon. Whenever I Select All on a range with hidden/filtered-out rows or columns, I always hit + (select visible cells only) before deleting. This is my belt-and-braces way of ensuring that I've only selected visible cells. I know (from watching you here) that this is not actually required, but I'll still do it! I don't always trust Excel...
Great tip! Thanks for sharing Ian. Microsoft has done a good job of improving this functionality on newer versions of Excel so you don't always have to select visible cells on filtered ranges. However, it is still required on older versions of Excel. Here is an old video we have on the topic for anyone else that wants to learn the select/paste visible techniques. ua-cam.com/video/9k5Zngv_ulE/v-deo.html Thanks again! 🙂
Bro its actually crazy how recent this vid is and how it helped me so much with data i was having with work. TY so much Jon! Was dealing with 600k rows. Life saver man!
Good tips. I've been struggling sometimes with this issue, and today I figured out the importance of the sorting first, but here you explain it plain and simple.
Thanks Don! I'm happy to hear you are still learning new tricks. I too am learning new things everyday with Excel. It is such a vast application with so many possibilities that we will never run out of things to learn.
Jon, i was waiting hours while excel was deleting blank rows of 100 000 rows. It kept crashing, then i watched your video and read some comments and its done in minutes. THANK YOU!
Thank you for the explanation - clear, precise and easy to follow. Even though I still dont understand or am afraid of that VBA thingy, I am humming along. Thank you so much.
Good lord I was selecting only visible rows and deleting and it kept crashing/not responding. Now sort thru A-Z and boom, it does it instantly for the rows I want deleted after a certain letter. Thank you!!! :)
Thanks Jon. This was super helpful and I was looking for this. I was struggling with the same problem and instead of deleting the row, I was copying and pasting the necessary information in the next sheet after applying the filter by hiding the unwanted rows. To copy and past the visible rows or columns, we need to use< Alt >+ . This video of yours is a life saver. I appreciate all your hard work in teaching us new things. Keep Going ;)
You helped very much thanks. I still a problem with a spreadsheet. When I delete a row, it automatically closes and opens. Sometimes it states it's corrupt and did repairs. On the left side I get a list of spreadsheets that closed and were repaired. I click to see what were the repairs it states there aren't any needed. How do I fix this? I copied and pasted into a blank spreadsheet and have the same problem.
Good video. Am wondering if you have an explanation for my excel file with around 150000 rows always freezing and giving not responding each time I want to remove a filter. It can end up taking around 20 minutes to start responding again
as you mentioned if I sort with red and select line and ctrl+minus some of the other lines (hide) lines always delete from my side. kindly tell me how to freeze those red selected lines with keyboard shortcut before ctrl+ minus
Hi Jon! Is there a way to delete just rows without deleting the cells that are blank? I have a huge sheet where some of the data is blank in certain cells but when I use the find and select it includes the rows that have data but there are a few sells that are blank
Hello John, I created a dynamic array function to eleminate all wanted rows from the list so much quickly. However, I need your sample file and I can send you my function
Ok, I tried it, but I have a problem. Ctrl A grabbed my header row and Ctrl - deleted it. I need to repeat these steps several times, so I’d need to copy my header row to a different tab, then insert a blank row and copy and paste my headers back in each time. In your example, the header row didn’t disappear. How did you do that?
Odd that the option to delete just the table row doesn’t exist in a filtered table. Any data in columns to the left or right of the table will also be deleted (on the visible rows) when deleting an Entire Sheet Row.
Yes, I agree it's a bit odd. I believe it has to do with the complex background process of restructuring the sheet when deleting rows from a filtered range/table. I'm not 100% sure on that though. You can still see the Delete Table Rows button in the Delete drop-down on the Home tab of the ribbon when the table is filtered. However, pressing that button will still delete the entire sheet rows.
@@Ddub1083 It’s difficult to give a general recommendation. If rows are never deleted in a way described in the video, then having multiple tables on the same sheet (not reserving one section per table) would be perfectly fine, especially if the column count is static. The same goes for combining a table with other data. This could, for instance, be useful when presenting data (think: dashboard). It all depends on how the table is used and how data is loaded.
@@TSSC You're totally right, if we just pretend that one will never need to delete rows... ever.... then it works perfectly fine to have other things on the table tab.
... but, but, but, is there a way to stop a process when it's taking too long and the message "excel not responding" shows up, time wind going non-stop?
I have a simple table with 2 columns and one column is a countif, trying to delete this column completely locks excel..why? Why does it need to do any calculation when I am completely deleting the formulas? So stupid
You just saved me hours of time!! I have a spreadsheet that contains over 1 million rows, many of which I don’t need, and spent a long time on a Saturday afternoon deleting them in chunks. I was going to spend more time today, a Sunday, finishing it. Thank you so much! I’ve taken at least three Excel courses, two in college, and I have never been shown this trick.
Glad it helped! 😀
I cannot thank you enough. My Excel would freeze (not responding) for 10 to 15 minutes (yes MINUTES !) when I would try to delete multiple rows. Now using this technique, on that same sheet as before, Excel freezes for barely 30-40 seconds in deleting the same number of rows. Thank you 🙂
This happens to me too... needing a way to stop the process when it freezes - desperately needing a method to do so without losing work done.
Excellent video. In my experience, in working with close to 200,000 rows. I find it easier to sort and just delete in place rather than then filter on the color or whatever you're using.
Many thanks, Jon. Whenever I Select All on a range with hidden/filtered-out rows or columns, I always hit + (select visible cells only) before deleting. This is my belt-and-braces way of ensuring that I've only selected visible cells. I know (from watching you here) that this is not actually required, but I'll still do it! I don't always trust Excel...
Great tip! Thanks for sharing Ian. Microsoft has done a good job of improving this functionality on newer versions of Excel so you don't always have to select visible cells on filtered ranges. However, it is still required on older versions of Excel. Here is an old video we have on the topic for anyone else that wants to learn the select/paste visible techniques. ua-cam.com/video/9k5Zngv_ulE/v-deo.html
Thanks again! 🙂
Bro its actually crazy how recent this vid is and how it helped me so much with data i was having with work. TY so much Jon! Was dealing with 600k rows. Life saver man!
Good tips. I've been struggling sometimes with this issue, and today I figured out the importance of the sorting first, but here you explain it plain and simple.
Great tips. I have been using Excel since ver. 5(1993) & am still learning new tricks every day thanks to you & other MVPs on UA-cam.
Thanks Don! I'm happy to hear you are still learning new tricks. I too am learning new things everyday with Excel. It is such a vast application with so many possibilities that we will never run out of things to learn.
Jon, i was waiting hours while excel was deleting blank rows of 100 000 rows. It kept crashing, then i watched your video and read some comments and its done in minutes. THANK YOU!
You're welcome, Andrés! :)
Amazing simple trick, saved me tons of time. Thanks Jon
Glad it helped, @SalmanKhan-qv1uq! 😀
Thank you for the explanation - clear, precise and easy to follow.
Even though I still dont understand or am afraid of that VBA thingy, I am humming along.
Thank you so much.
This is great, i have been having so many cases of excel freezing now sorted. Thanks sos much
Thank you very much. I have successfully applied it to my work.
Thankyou so much; after three days of search finally something worked. Thanks again!
You're welcome, Zubair! :)
Thank you Jon. A great technique the explanation about the Immediate Window. That's awesome.
Good lord I was selecting only visible rows and deleting and it kept crashing/not responding. Now sort thru A-Z and boom, it does it instantly for the rows I want deleted after a certain letter. Thank you!!! :)
Glad I could help! 😀
Many thanks for this, really helps a lot, always struggling in cleaning my excel with big data
You're welcome, staticbystandermark1! :)
Thanks & nice tips. for data more than 100k rows. I usually just copy + paste after filter data which i want to take to a new sheet
Thanks for sharing an alternate solution, Sonny! 👍
Thanks Jon. This was super helpful and I was looking for this. I was struggling with the same problem and instead of deleting the row, I was copying and pasting the necessary information in the next sheet after applying the filter by hiding the unwanted rows. To copy and past the visible rows or columns, we need to use< Alt >+ . This video of yours is a life saver. I appreciate all your hard work in teaching us new things. Keep Going ;)
Thanks Jon, good, easy to follow video as usual.
Thanks Paul! I appreciate your support. 🙏
very useful.. saved me a lot of time . thanks
Thank for your feedback, Naveen! :)
Life saved. Thanks!
Great tip Jon.
Thanks Sergio! 🙂
You always bring the coolest tips out there. Thank u Jon for your hard work 👍
Thanks Nader! I appreciate the nice feedback. 🙌
Loved this video Jon…especially the geeky tip at the end! 😀
Haha awesome! Thanks Karen! 😊
That is wondering ! Thank you for sharing!
Happy to hear it, J D! 🙂
Very helpful 👌 thanks
Wow. Now I finally know what the lock up of death is! Thanks so much. Wish I knew this long ago!!!
OMG!!! Are you telling me that this is a thing with other ppl too? 😱🤣🤣🤣. Thank you so much for this! 👏🏽👏🏽
Excellent! Thank you😊
Cool tips as always.
Thank you 🙏🏽
Thanks Orel! 🙂
You helped very much thanks. I still a problem with a spreadsheet. When I delete a row, it automatically closes and opens. Sometimes it states it's corrupt and did repairs. On the left side I get a list of spreadsheets that closed and were repaired. I click to see what were the repairs it states there aren't any needed. How do I fix this? I copied and pasted into a blank spreadsheet and have the same problem.
Been 1.5 HOURS now. (I have 70k rows. Didnt think it was thattt much) May have to force shut down Excel and sort and try again. Thanks for the tips.
Thank you very much for this video!
Really Great Tips..Thank You Jon :)
Thanks Darryl! 🙂
Thank you, my way is to filter the colors i want to keep then cut..> Paste in new sheet... >delete the original sheet
Hello Jon, do you offer tutorial sessions? If so, what are your rates?
Great tip.
Thanks!
Hi, appreciate this but I still have to wait for a long time to sort? Is there a trick for that too?
Good video. Am wondering if you have an explanation for my excel file with around 150000 rows always freezing and giving not responding each time I want to remove a filter. It can end up taking around 20 minutes to start responding again
Thanks it worked
You're welcome, Nishad! :)
Awesome!
as you mentioned if I sort with red and select line and ctrl+minus some of the other lines (hide) lines always delete from my side. kindly tell me how to freeze those red selected lines with keyboard shortcut before ctrl+ minus
Amazing
for me ... I don't delete rows anymore ... i just use filter function
Awesome 👏👏👏
Jon, would the "sort" trick help reduce the lag time in Power Query also (I'm working with approximately 2 million rows)?
So great ides
thanks you
sir
i delete my 25000 rows in a second.
Great
sir
Hi Jon! Is there a way to delete just rows without deleting the cells that are blank? I have a huge sheet where some of the data is blank in certain cells but when I use the find and select it includes the rows that have data but there are a few sells that are blank
Hello John,
I created a dynamic array function to eleminate all wanted rows from the list so much quickly.
However, I need your sample file and I can send you my function
Ok, I tried it, but I have a problem. Ctrl A grabbed my header row and Ctrl - deleted it. I need to repeat these steps several times, so I’d need to copy my header row to a different tab, then insert a blank row and copy and paste my headers back in each time. In your example, the header row didn’t disappear. How did you do that?
Odd that the option to delete just the table row doesn’t exist in a filtered table. Any data in columns to the left or right of the table will also be deleted (on the visible rows) when deleting an Entire Sheet Row.
Yes, I agree it's a bit odd. I believe it has to do with the complex background process of restructuring the sheet when deleting rows from a filtered range/table. I'm not 100% sure on that though.
You can still see the Delete Table Rows button in the Delete drop-down on the Home tab of the ribbon when the table is filtered. However, pressing that button will still delete the entire sheet rows.
thats why you always put a table alone in its own tab.
@@Ddub1083 It’s difficult to give a general recommendation. If rows are never deleted in a way described in the video, then having multiple tables on the same sheet (not reserving one section per table) would be perfectly fine, especially if the column count is static. The same goes for combining a table with other data. This could, for instance, be useful when presenting data (think: dashboard). It all depends on how the table is used and how data is loaded.
@@TSSC You're totally right, if we just pretend that one will never need to delete rows... ever.... then it works perfectly fine to have other things on the table tab.
I'm VBA geek hi Jon
Hi, this doesn't seem to work if you are trying to sort and delete by the 3rd or 4th column. Can you confirm?
Hello my data doesn’t have same name for cell so I cannot sort them what can i do ??
What if Excel freezes when you are trying to sort data? I have thousands of entries and need to remove blanks. Always crashes my Excel
... but, but, but, is there a way to stop a process when it's taking too long and the message "excel not responding" shows up, time wind going non-stop?
I have a simple table with 2 columns and one column is a countif, trying to delete this column completely locks excel..why? Why does it need to do any calculation when I am completely deleting the formulas? So stupid