How to Prevent Excel from Freezing or Taking A Long Time when Deleting Rows

Bottom line: This quick tip will save you a lot of time when deleting rows from a filtered range or table in Excel.  Learn how to prevent Excel from freezing or crashing when deleting rows.

Skill level: Beginner

Watch on YouTube & Subscribe to our Channel

Download the Sample File

Download the sample Excel file to follow along.

Deleting Filtered Rows Can Take A Long Time!

Has your computer ever started smoking after you attempted to delete rows from a large filtered range?  ðŸ™‚

Or maybe you have to sit around and watch the wait cursor spin, not sure if Excel is going to come out of “Not Responding” mode, while the rows are deleted…

Even worse, sometimes Excel freezes or crashes when you attempt to delete rows.

Why does this happen?

Well, deleting rows on a filtered range can be a very labor intensive process for Excel.  If the data is not sorted then Excel has to go through each set of visible rows and delete the row sections one by one.

Deleting Unsorted Rows Can Take A Long Time in Excel

This process takes longer with larger data sets that contain more rows, columns, and formulas.  Excel will sometimes enter Not Responding mode.  We refer to this as “freezing”, but it actually causes the computer to heat up and work harder…

Why Does Unsorted Data Perform Slow?  It's all about the Areas!

For this example I created a very simple data set that has two columns.  The first column contains a color (Red, Blue, or Green), and the second column contains a random number.

There are 10,000 rows of data and the Color column is NOT sorted.

Sample Data 10000 Rows of Unsorted Data for Delete Rows

Let's say we want to delete the rows that contain “Red” in the Color column.

If we filter the range for Red only, then delete the rows, the process takes about…

8-10 seconds!

Excel does enter “Not Responding” (freezing) mode for a few seconds too, which is always scary.  It may be faster or slower on your computer, but this is a very simple data set with no formulas.

Each Group of Contiguous Visible Cells in a Area in Excel VBA

The unsorted data creates a series of non-contiguous ranges for the visible rows.  These non-contiguous ranges are known as areas in VBA.  We can determine how many areas we have in the filtered range by selecting all the cells in the first column of the data set, then running the following line of code in the Immediate Window of the VB Editor.

?Selection.SpecialCells(xlCellTypeVisible).Areas.Count

The result is 2,197. That means Excel has to perform the delete rows command 2,197 times to delete all the filtered rows. If there are formulas that are dependent on that range then it will have to update the range references as well.  Imagine how long it would take if we had to do that manually… 🙂

Checkout my article on the 5 Ways to use the VBA Immediate Window for tips on how to work with this great tool.

Sort to the Rescue!

Now if we sort the data first by the color column, then filter for Red only, then delete, the process takes…

less than 1 second!

I timed it with a macro and it took about 0.012 seconds to delete the sorted rows.

Sort Column Before Filtering to Delete Rows

Unless you need an excuse for a coffee break, it's best to sort the column before deleting the rows. We went from 10 seconds to less than 1 second with a simple sort!  Woohoo!  Don't forget to alert the media (aka boss) that you saved a bunch of time!

The sort puts the visible rows to be deleted in one contiguous range (area).  Excel is able to delete the range all at one time, instead of looping through all of the areas in the unsorted range.

Sorted Rows are in one Contiguous Range or Area and Faster to Delete

Checkout my articles on 7 keyboard shortcuts for the filter drop-down menus and 5 keyboard shortcuts for working with rows and columns to save some time with these sorting, filtering, and deleting tasks.

What If I Don't Want to Change the Sort Order?

If the current sort order of the data is important and you don't want to lose it, then we can add an index column to the data table.

An index column is just a column that contains the row number for each row.  This is a hard-coded value.

We can type a 1 in the first row of the data table, then fill the sequence down.

Add an Index Column Before Sorting Filtering and Deleting Rows to Retain Sort Order

Now with the index column in place, we can sort, filter, and delete the rows.  After the deletion, sort the index column in ascending order to get the original sort order back.

Sorting Prevents Computer Warming

It probably won't solve global warming, but sorting your data can help prevent your computer from heating up (freezing). 🙂

What other performance tips do you have for sorting data?  Please leave a comment below with your suggestions, or any questions.  Thanks!

49 comments

Your email address will not be published. Required fields are marked *

  • Timely post. I run a macro that pastes data at the end of a table. I paste it with headers but note the row number where I start the paste. I then run an action to delete that specific row number. The table is not sorted. It does have filters on but there isn’t a specie filter criteria when I execute this macro. This still takes a long time. Could simply having the filter turned on slow down a delete function? This table has aprox 40k rows and 35 columns

  • Thanks for this tip. I had already figured out sorting on colour but had not thought of the index column which is a great help. Cheers

  • Jon Sir
    I am on doing a small data entry job (post retirement)and It is interesting to read
    Many Many thanks for doing this valuable help to students of all walks of life

  • Hi Jon,

    I didn’t know about the ‘area’ and using the immediate window to count them! Thanks for that!! …I also use the counter method to sort back into order after deleting the rows.

    Cheers,
    Kevin Lehrbass

  • Hi, Jon.

    I have a huge data table (20K+ rows / 50 columns) with “tons” of formulas, and even if I sort the information, when I try to delete a number of rows, Excel sends a message about the lack of resources to perform the action. Any suggestion to solve it, please?

    Best regards.

    • Hi Arturo,
      It’s difficult to tell, but the issue is likely due to the number of formulas in the sheet. Here is an article that has some suggestions on what might be causing the error and how to fix it.

      https://support.microsoft.com/en-us/kb/2779852

      You might want to try reducing the number of formulas if possible, or converting the formulas to values, deleting, then restoring the formulas. You could do that with a macro. I hope that helps.

    • Install the 64bit version of Excel. I ran into this regularly until I did so. The 32bit version is limited to something like 2GB, and a large table takes up most of that once loaded. Search the web for the actual limit and compare that to what you see Excel.exe using in the task manager. If they are close this is almost certainly the issue.

  • Hello Jon,
    My excel file is 249 mb and has 300,000 rows of data. The quality of data is not great. When I apply filter for blank cells in one of my columns, it shows about 700,000 cells as blank and part of selection and am not able to delete these rows in one go or by breaking them into three parts. Excel goes non-responsive. My processor is intel core i3 with 8 gb RAM, so would a processor upgrade help? Also, my excel is 64 bit.

  • A colleague of mine had an unusual thing happen on her spreadsheet and I can’t figure out why it happened. She sorted the data, and several rows of data disappeared! I can’t seem to figure out why it happened and I can’t find anything via Google, so I thought you might be able to help?

    • Sometimes this happens because a prior filter wasn’t cleared or rows were hidden somewhere in there that weren’t important, but after resorting, different relevant data is now in those hidden rows. Here’s a way to test that hypothesis:
      1) In your first column, press Ctrl+Down and it should take you to the bottom of your range (as long as there are no blanks)
      2) Highlight your first column. The lower mid-right of Excel should show the count and sometimes the sum and average. That count should equal the last row number minus any known blanks. If it doesn’t match, that means you have hidden rows.

      If you have hidden rows, but don’t know where they are, be sure to:
      1) Select your range or table by clicking any one cell in the range or table
      2) Click the data tab and next to the filter button, there’s a button that says clear and it will clear all of your filters, if any. If the button is disabled, you don’t have any filters.
      3) Scroll horizontally so the first row in your range is in the first viewable column
      Select the first row in your range and press Ctrl+Shift+Down. This highlights every row from the top to the last value in the left most column.
      4) Right click one of the highlighted row numbers and click “Unhide”. This will unhide any hidden rows. Alternatively, while the rows are highlighted, you can go to the ribbon, click “Home”, then Format>Hide & Unhide>Unhide Rows.

  • I don’t know if this is a related issue… but excel crashes when I try to delete an EMPTY column. There’s no formula in the workbook that references any of the cells in that column, and deleting the column shouldn’t trigger any code I have in the macro. Does this sound familiar?

  • Thank you for this tip! my worksheet crashed several times yesterday and I found this tip this morning and it worked seamlessly!!

  • This tip is awesome!! I was trying to delete about 300k rows, it took hours the first time I did it but with sorting it took a couple of minutes. Thank you!!

  • Tip of the year,thanks!!

    My table has 50000 rows and 18 columns, no formulas, raw data. Deleting unsorted 27000 rows takes 25 minuts.

    After sorting the table on the selection column, the process takes 5 secs:-)

  • Jon Acampora
    You know, you are a wonderful wonderful man! 🙂
    My excel dataset had over 200,000 rows and had five – six different files like these.. I spent an entire day just looking at the computer screen to the ‘not responding’ excel screen..
    i dont know why i didn’t ‘duckduckgo’ it earlier.. i read your article and followed the ‘apply index – sort your column – delete – sort column on index again’ method.. barely took 4-5 secs.. amazing!!!
    thanks a ton Jon.. much appreciated..

  • Hi,

    I am currently watching my mouse cursor spin and my excel window displaying Not Responding. I decided to search and see if there was a way to stop this. I will use this tip. Most of my reports contain 100,000+ rows and some hit the 1,000,000+ mark and then populate a second tab in the spreadsheet once the max rows are hit.

    I do have one question, I regularly use Remove Duplicates, will the same sort first process work for that?

    Kind Regards,
    Tim

  • Thank you. I was deleting hundreds of thousands of rows and even with a monster computer it was taking forever.

  • Thank you for this article! I had been wondering why it took so much effort to delete mass amount of filtered data, and this article helped confirm my suspicion and provided a simple solution!

  • Wow!! Thanks..
    Cursing myself why I didn’t checked excelcampus.. 50k rows deleted in few seconds!!! While I hv already wasted hours before knowing this trick..
    THANKS AGAIN

  • Awesome tip Jon! This saved me so much time and also saved wear and tear on my computer (did not overheat)!

  • Thanks for the sorting tip. I tend to approach the problem from the “other end” – copy the rows you want to keep into a new tab – Excel does this much much quicker, then copy the whole contents of your new tab into the original worksheet and the rows you want to delete will have gone.

  • For years I have been deleting data the traditional way- i.e. filtering and deleting rows from data (>500000 rows) and have spent hours waiting for excel to process the task.
    This simple and insightful technique has helped me save precious time in all my tasks. This also works when trying to categorise some filtered data with a helper column- values can be copied down exponentially faster!
    Many thanks to you Jon!

  • thanks, i have been wasting hours to remove rows from files that contains over 100 or 200k rows with 10/15 columns . Now it takes 30 seconds

  • This just saved me so much time and frustration- I needed to delete 185,000+ lines of a worksheet with almost 300,000 lines in it. My computer was not having it… LOL Thanks for the tip and information!

  • This was incredibly helpful. I had a dataset of 72K rows of which 20K were flagged for deletion. when I filtered on the flag, selected to rows to be deleted and let it go, it ran it went for 12 hours without completing with high resource utilization. When I sorted on the deletion flag and selected the same rows for deletion in finished in UNDER 3 SECONDS. I can’t tell you how many times I’ve run into this issue. Thank you.

  • I often use Remove Duplicates to quickly delete many rows. Sometimes I first concatenate a few columns into a temporary column with an IF statement (if my conditions for deletion are met, show “”). Removing duplicates removes all but one row with that blank cell, which I delete manually.

  • Awesome, awesome, awesome! For me, I had to turn calculating formulas to manual for this to be as fast as everyone says. Maybe that was obvious to everyone but me, but just in case there is someone else that may have made the same mistake. Thanks again!

  • Great video… I figured out most of those techniques through trial and error (and lots of frustration) but good to know that I’m not the only one. 🙂

  • Is the same true in Power Query? Would sorting before filtering out data speed up the processing time in Power Query data cleaning?

  • Also syntax or formuale in data validation for displaying the alphatically only those values with starting from that alphabet when an alphabet is punched

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter