10

At the company I work at we have a Excel spreadsheet to keep track of the purchases the company made in the last year. This sheet has aproximately 1500 rows and 30 columns.

A few days ago filtering in this sheet has started to become very slow. It takes around 5 seconds to filter any column for any keyword. Which is pretty long because this sheet gets filtered a lot and it gets annoying. The sheet doesn't have any formulas or references to other sheets or files. There are two macros running on cell changes, but disabling both macros doesn't make it any faster.

I also tried to delete every row except one, but applying the filter to the remaining row still takes 5 seconds to complete.

Does anyone have a idea of why this could be so slow?

5
  • 2
    Copy the data only over to new workbook and see if the problem still exists.
    – CharlieRB
    Sep 21, 2016 at 15:24
  • How much RAM do you have? how fast is your CPU? Defragment your hard drive. Open it from a thumb drive to eliminate the hard drive or not.
    – cybernard
    Sep 22, 2016 at 4:28
  • 1
    Okay I tried to copy the data to a new workbook and something interesting happened. When I copy the data by selecting it with CTRL+A the problem remains and filtering is very slow. If however I copy the data by manually selecting all the rows, it's as fast as it should be again. I guess this fixes my problem, but does anyone know why this could happen? Thanks anyways
    – mius
    Sep 22, 2016 at 5:52
  • Very interesting indeed. Try this on for size. Copy the file from it's folder and paste it as a copy. I'm interested in that test. Also, where is your file located? C-drive? USB thumb drive? Server? NAS? Also has meaning.
    – ejbytes
    Sep 22, 2016 at 9:19
  • Moving the file doesn't change anything. Originally the file is located on a server in the company, but moving it to the local ssd of my computer doesn't change anything. I also tried it on multiple computers with different hard drives.
    – mius
    Sep 22, 2016 at 9:39

7 Answers 7

9

This usually happens because Excel thinks the data set is much larger than it actually is. For instance, if someone had navigated to cell A1048576 and formatted it, even though it might not look different, now Excel thinks you have over a million rows. Test this by selecting cell A1 and then hitting CTRL + End. This will take you to what Excel believes is the last cell in the worksheet. If this is the issue you can delete all the rows and/or columns after where your actual data ends, save the file, close it, and reopen it. This should stop Excel from seeing all those empty cells as part of the data. If this doesn't work, selecting your data and pasting it into a new file should fix it.

4
  1. Select All (Ctrl+A)
  2. Go to Home -> Editing -> Clear -> Clear Formats.
  3. Save file and re-apply filter.

You're done. It works for me every time; please let me know if it works for you!

1
  • 1
    I actually logged in to upvote this answer. Saved my life! We have a 80MB file which is an output of all the user-role profiles on one of our Oracle ERP systems. It's a beast with 800k rows and so far it has killed all of our PC's - just filtering it on a simple Y/N column will freeze my machine. This solution worked however, so thank you so much for that! Jun 5, 2019 at 18:49
1

It's probably too late, but turn off your automatic calculations. In VBA, I use these two turn off the automatic calcs, apply filters, then turn back on auto calcs.

Sub automatic_update_on()

   Application.Calculation = xlAutomatic

End Sub

Sub automatic_update_off()

   Application.Calculation = xlManual

End Sub
1

Here is something I just found that immediately solved this problem. I recently added conditional formatting to one column of my spreadsheet (approx 30K rows) and it would take forever for filter box to appear.

I just removed the conditional formatting from that column and the filter box came up immediately.

0

In my case it helped to remove all comments. Filter time came down from 1.5 minutes to less than a second for a table with 100,000+ entries.

1
  • Indeed... Comments and formulas tend to slow down filtering.
    – Unicco
    Jan 4, 2022 at 11:47
0

For those that have multiple workbooks referenced to and experience slow filtering, inserting etc. if you leave the referenced workbooks closed, filtering will take ages. Easiest way is to open all the linked workbooks and then do your thing.

-1

@Rakesh Dhedhi Thanks for sharing such a valuable solution. I was hunting to get the right solutions to use the filter option faster on the excel sheet. Did too many experiments but nothing works. Finally, I followed your shared steps and cleared all the formatting and it's working too fast now.

2
  • 1
    As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Feb 9, 2022 at 16:55
  • Please don't add "thank you" as an answer. Once you have sufficient reputation, you will be able to vote up questions and answers that you found helpful. - From Review Feb 9, 2022 at 17:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.