Excel Advanced Filter Painfully Slow

Today, working on my Excel file was like riding a lazy snail through molasses in January — but slower!

Usually an Excel Advanced Filter is a speedy way to extract data from a table, but things weren’t working right in a sample file that I got last week.

And despite what my high school English teachers might think, you can’t mix too many similes, when trying to describe excruciating slowness.

Advanced Filter Macro Problem

The sample file had code that ran an Advanced Filter in Excel 2007.

The code ran quickly in Excel 2003, but screeched to a near halt in Excel 2007. What was the problem?

In the sections below, I detailed all the things I tried, while troubleshooting the slow macro problem.

  • Tip: You can skip to the end, to see what the unexpected problem was, and how I finally fixed the Advanced Filter macro.

There’s a video at the end of this post too, where I show the problem and the solution.

The Slow Filter Symptoms

When the code ran in Excel 2007, it looked like the extracted rows were being pasted in the second worksheet, one row at a time.

Aha! I should turn off the screen updating — a simple solution. You’d think.

Nope! Even with the screen updating turned off, the code barely crawled along.

It took almost 3 minutes to extract 1500 rows — maybe a millisecond faster than it ran with screen updating turned on. Who has that kind of time?

Guess Again

In the next round of solution guessing, I got rid of the few formulas in the worksheet and criteria range.

There wasn’t anything too complex, but maybe that was slowing things down.

I also changed calculation to manual at the start of the code, then set it to automatic at the end of the code.

Neither of those changes had any effect on the code’s speed.

Strip the Data Clean

In round 12 of testing (I’ve lost track of the test count), I copied the data, and pasted it as values into a new workbook.

The code ran like lightning. In July. With jet engines. Hmmm.

Maybe it was the formatting and styles in the original file that were slowing things down.

To test that theory, I formatted the original table with Normal style, which removed all the borders and fill colour.

That didn’t improve things, but when I removed the red fill from the heading cells, I noticed a red comment marker in one of the cells.

Whip Things Into Shape

Could a comment be the problem? That didn’t seem likely, but:

  • As soon as I deleted the comment, the code ran perfectly.
  • When I put the comment back, the macro slowed to a crawl again.

AdvancedFilterSlow01

Curiouser and Curiouser

When I tried to create a sample file to demonstrate this problem, things got even stranger.

I created a table with a comment in the heading, and ran the code, expecting it to be slow. It ran quickly, in several tests.

Timing the Advanced Filter Macro
Timing the Advanced Filter Macro

Add Shape to Worksheet

Next, I added a shape to the worksheet, and assigned a macro, to make it easier to run the code.

The code slowed down again!

AdvancedFilterSlow03

Next, I deleted the shape, and the code was still slow, so I had to delete the comment to speed it up again.

The Verdict on Slow Advanced Filter Macro

If your Advanced Filters are running slowly in Excel 2007, try removing any comments in the table heading cells.

You could delete them at the start of a VBA procedure, run the filter, then add the comments at the end of the code.

Shapes + Comments = Trouble

The problem seems to occur if there are heading comments, and a shape is added later, as you can see in the short video demonstration below.

Fortunately, this problem appears to be fixed in Excel 2010, so if you upgrade, you should be able to have comments and shapes, without slowing down the Advanced Filters.

Another Solution

Update: In the comments, PDLobster suggests the following solution, to speed up the filters — thanks!

  1. Turn off all filters
  2. Select cell A1
  3. Turn Wrap Text ON
  4. Select the entire worksheet
  5. Turn Wrap Text OFF

Watch the Video

To see the steps for reproducing and solving the Advanced Filter speed problem, you can watch this short Excel video.

____________

22 thoughts on “Excel Advanced Filter Painfully Slow”

  1. I’ve had this problem in 07 a few times before. Almost always, it was the result of ‘noise’ in the data and was correct by copy/paste special/values.

    The worst performance I’ve ever had during filtering though, was because there was that grouping feature turned on elsewhere in the sheet. Not like I’d ever use the grouping feature though…..

  2. Thank you! This was very helpful. In my case, I had 2 buttons located in the header rows of the data that I was extracting from – I removed these and the filtering FLIES!

  3. Here’s what worked for me in Excel 2007, regardless of code or conditional formats or comments:
    Turn off all filters
    Select cell A1
    Turn Wrap Text ON
    Select the entire worksheet
    Turn Wrap Text OFF

    that’s it!

  4. Had the same symptoms in my 2010 workbook: painfully slow advanced filter. Found your post, removed my comment, and it´s now much faster! Thanks for the info.

  5. PDLobster…..thanks so much, I followed your suggestion and the spreadsheet flew just like it did in Excel 2003. Then the more cells I turned on Wrap Text, it slowed somewhat, but still much faster than when I first opened it up in 2010. Again, thanks for the great tip!!!!!

  6. Pingback: advanced filter
  7. I had the same symptoms.
    What I discovered was there was conditional formatting in some columns which made everything very slow. I cleared all the conditional formatting from the sheet, and…
    …VOILA!!!
    Excel is back to normal speed.

  8. > PDLobster
    It works like a charm !
    I have many multiline cells.
    I put the filter code between the code disable wrap-text and enable wrap-text and now it flies ~

  9. I had a similar problem, in Excel 2013 – Advanced Filter running approx. 150 times slower than normal. There were no comments in the sheet – I tried resetting all formatting ( no fill for cell interiors, removing Wrap text and so on ) – nothing worked.
    So, that left one remaining object in the sheet, a Forms button. I deleted that, and hey presto, all starts working as expected!!!
    I replaced the Forms button with a Textbox, formatted to look like a button, and assigned my macro to that … everything still works at top speed.
    This article was my inspiration, so I thought I’d share ( and I’ve been a Debra fan for years!!! ).

    1. It turned out the forms button was the culprit in my case too, replacing it with a text box restored the speed. Thanks very much Glenn. And thank you Debra, for this discussion.

  10. This article is still saving me time and helping me solve a problem with Excel 2013 and as I write it is 2016. Thanks Debra!

  11. Thanks. I had just added an active X button to a sheet (Excel 2007) and developed the slowness issue.
    I probably should have figured it out , but it’s such a lame problem, it didn’t dawn on me.
    Removing the button cured all slowness.
    Thanks for the heads up.

  12. Today I discovered another reason that can make the filter “painfully slow” in Excel 2010.
    I formatted my header row with bold border lines. Once I had any thick border around my cell, the filter was suddenly really slow (only when you hit “show all”). It doesnt help by the way to just put the format back. Once you had a bold border at any time, you will have this problem with huge tables. So for me it only helped to create a new worksheet and avoid this kind of formatting. If I format the table without the borders, I can now even have really big tables WITH word wrap that filter as fast as any small list. Both of these formats in combination by the way contributed to the problem.
    Hope this helps anyone else.

  13. Thanks muchly Debra for your research and publication, for me it was the comments !!!
    Watching molasses drip was faster than watching Excel extract from my database which is over 315,000 rows and almost 200 columns though no formulae, the criteria 8 cells and the extract headers the same number as the database, and i am using the extinct Excel 2007
    After i deleted the comments, (i must have had about 30 but only in the headers not in the data), it was back to the old days, extraction was like the proverbial flash in the pan. The 275 results came out in one second as opposed to a half hour drip feed
    So again, thanks for providing a cure for my frustrations
    Tony

  14. Also i was able to fix the sluggishness of advanced filter method by macro by removing a line cord that had a role of clearing content in another sheet so that the data can be copeid there. On removing the line cord it worked like lightening

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.