Improving Excel poor filtering performance

Question

I am having some serious performance issues with Excel when trying to filter relatively small data sets (50,000-250,000 rows).

Filtering a single column on a 100k row dataset to exclude a value may take in upwards of 10-15 minutes to filter. Even worse is removing duplicates, which may take 20-30 minutes. It utilizes ~24% of my CPU while doing so for the entire time

This seems unusually slow, I wrote a JavaScript script to filter some data on another system that has a run time of a few seconds for 500k rows or more. I would expect Excel to resolve these filters in a minute or two at most.

How can I improve Excels performance? Are there ways to monitor it's performance and identify where it's getting hung up?

Answer 1

Yes, remove all formulas (copy/paste values) if you have to. Reduce the number of COLUMNS you are dealing with, especially if you stuff way out to the RIGHT (I believe Excel allocates each row, and when it does, it allocates as many columns as it needs to handle the data, so storing things in Col A and Col ZZ creates tons of unused columns, versus only using Col A and Col B).

Also beware of Subtotal() functions that are referencing the data you are filtering.

Finally consider turning off Automatic Recalculation. See how that affects things. You might have some other formula out there you forgot about.

But it would help if you gave us more details. 100k Rows of data is Pretty large for Excel, but honestly, the number of columns matter. 2 Columns or 20? Huge differences when you multiply by 100k.