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

Why Deleting Rows Take A Long Time in Excel

Download the Sample File

Download the sample Excel file to follow along.

Sort Before Deleting Rows.xlsx (504.4 KB)

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.


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!


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

  • 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,

  • 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..

  • 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:-)

  • 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!!

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

  • 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?

  • 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.

  • 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.

  • 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.

      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.

  • 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.

    Kevin Lehrbass

  • 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

  • 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

  • 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

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...