I have an excel sheet with about 2000 rows of data. When I filter the data the sheet grinds to a halt and is slow, even after I let it catch up. When I unfilter it everything is lightning quick.
What would cause the data to be slow when filtered but OK when not?
Hello Proudcdn1 & Welcome to the Forum,
Not sure, but it the sheet is about 2000 rows, what is the actual end of the sheet?
What happens when you select >> Ctrl + End
If the end of your data is say column H and row 2045, does your cursor go to H2045 when selecting Ctrl + End?
Have your tried to copy your range of data and pasting it in another new worksheet? Any change?
HTH
Regards, Jeff
Actual end is 2000. Yes, tried the copy paste in new book. Same issue.
Is there any code running on the sheet?
Are there a lot of formula bogging the sheet down?
No macros etc. Few formulas and conditional formatting.
But when unfiltered it works smooth. It is only when filtered that it bogs down.
I don't have any other suggestions, but I will put a call into the other forum members for some possible ideas.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
What is the size of that workbook?
Save the file in .xlsb (Binary) format and check
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
I've had workbooks slow down if I had a lot of objects on the sheet. Do you have a lot of pictures, controls (like check boxes) or other things on the sheet? Sometimes these can be so small they don't show up.
You can use "Find and Select" on the home tab to select all objects and delete them, to see if this speeds your work up.
One test is worth a thousand opinions.
Only 2000 rows can't bother you for filtering data, How many columns are there in your workbook. You can do following things.
1- Remove other rows and columns where data is not present.
2- See how many conditional formatting rule added if you are doing many conditional formatting into your database, excel become slow.
3- What is size of your excel workbook if it is containing 2000 rows then file size matter.
4- If you thing anything is not possible then your can simple create a new workbook and copy paste the same data.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
hi Proudcdn1,
its affected due your formula calculation.
some data you have taken from another workbook or sheets by using formula (like vlookup, sumif, countif and other)
when you filter the data it starts calculation for showing exact result so make all formulas as value then filtered it.
Ok, I have scrubbed sensitive info (names, phone numbers, etc.). The first tab is the full master file. The second tab is filtered to just a couple of PO#s. As you can see the Master tab is fine to scroll through etc. without any slow downs. The filtered tab is chunky and slow.
Thank you in advance for the help !!!
Strange since I don't see any slow down at all.
What hardware are you using?
Are you opening this directly from an email or from a server elsewhere on your network?
What happens if you set calculation to manual? Does that change anything?
When I encounter stuff like this I always try a binary approach to working out what might be the problem. For instance delete half the rows and see if that makes a difference. If not delete half the remaining rows and repeat until you end end up with a single row which is still slow. Then repeat the process with the columns and end up with a single cell. Hopefully you'll find a group of rows or columns that cause the problem and you can concentrate your attention on them.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I am experiencing the issue on multiple computers (home and office) with Win 7 and Win 10. My home computer is smoking fast (i7 6700 processor with 64G of RAM). I am running the file direct from the computer (saved on desktop).
When you arrow through the cells on the Filtered tab it is smooth?
Sometimes you just need to cut your loses and rebuild the sheet. I would strip the copy down to values only, then test the filtering. If it now is running smoothly, start adding all the necessary details from there.
Maybe not always the solution you want to hear, but could be the answer.
If that was indeed a question rather thana statement, then Yes, I fnd arrowing through the cell is perfectly smooth and no different to what I would expect.Originally Posted by Proudcdn1
Are you running Win 10 on a Mac with Parallels or Boot Camp by any chance?
Like Richard I experienced no difference.
In B12 instead of using:
why not make it:
Please Login or Register to view this content.
What happens to the speed if you remove all your Conditional Formatting? Does that make it faster?Please Login or Register to view this content.
Last edited by MarvinP; 12-09-2017 at 02:20 PM.
So the sheet is also working OK for you too jefferybrown? This is very odd. OK, I may need to re-build
Yes Marvin, with the conditional formatting gone it is smoother.
Good suggestion on the formula too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks