11,000+ rows of data and pulling formula slows file too much

[alessiaexcels]

Hi,

Any tips for using an excel formulas down the page for a sheet that has over 11,000 rows of data in it? I have turned off auto calculations and without this it is close to impossible to use the file but this is annoying as well.

Do I have to create a summary of data before applying a formula or is there another way?

Thanks,
Alessia

 

[Vogateer]

Some volatile formulas (for example, INDIRECT or OFFSET) will take a lot more time than others, array formulas and conditional formatting can also slow things down. I think we'd need to see a sample of the formulas in your sheet to figure out what's slowing things down. Any conditional formatting would be good to know about, too.

 

[DanteAmor]

One option is to review the formula and see if it can be changed to improve performance.
If the formula handles large ranges, reduce them.
And finally change the formulas for a macro. Every time you need to update the results you must run the macro.

For any alternative, you must put your formulas and a sample of your data here, to see how it works and find the best solution.

Note XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[felixstraube]

If you could share the file ( uploading it to a cloud storage and sharing the link here) that would be even better so we can have a look.

 

[alessiaexcels]

This is the formula I am using across 11,000 rows: =TAKE(FILTER('PAAF IDs'!$A$2:$A$100,('PAAF IDs'!$B$2:$B$100=A2)*('PAAF IDs'!$C$2:$C$100<=B2)),-1). I will share a sample later. Thanks!

 

[felixstraube]

With that formula and some random sample data I don't have a problem. I hit F9 and get the results without noticeable delay.
Even with 110,000 rows I don't have a delay.
Is that the only formula you have in the whole workbook?

 

[alessiaexcels]

With that formula and some random sample data I don't have a problem. I hit F9 and get the results without noticeable delay.
Even with 110,000 rows I don't have a delay.
Is that the only formula you have in the whole workbook?

It was too hard to remove confidential info so I am unable to share the workbook - there was a lot of other tabs and formulas. Nothing huge though to my knowledge and longest worksheet had 250 rows of data. I have decide to remove the data with this formula to a separate workbook and no issues now. Thank you.