1

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?

2
  • 1
    Basing your expectations of Excel on your experience with JavaScript is not a valid comparison. Excel's performance will be directly related to your computer's performance, which you have told us nothing about.
    – CharlieRB
    Aug 18, 2016 at 19:54
  • Also, what is the nature of what you're filtering? Are you filtering data or the results of a formula? How long are the values? Are there dependencies involved (in either direction)?
    – fixer1234
    Aug 19, 2016 at 21:31

1 Answer 1

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.

Not the answer you're looking for? Browse other questions tagged .