Workbook takes too long to calcuate

[baleshst]

Hi,

I have a Profit and loss workbook.
Sheet 1 has the actual profit and loss which has roughly 700 rows and columns upto zz.
Sheet 2 is the TB which has roughly 6000 rows and columns upto U
Sheet 1 derives data from sheet 2. In sheet 1, there is a data validation drop down given which will calculate the P&L as the criteria from data validation is selected.

The problem comes here. First of all, when the sheet opens it takes a lot of time to open. (there are no external links to another workbook). And when the user selects criteria from data validation, it takes approx 5 mins for the Profit and loss to calculate. Most of the formulas used in the Profit and loss account are similar to this =+SUMIFS(TB!$J1:$J60000,TB!$C1:$C60000,$A452,TB!$E1:$E60000,F$3)*$B452


Please let me know if I could speed up the workbook opening speed and sheet calculation speed.

 

[jdellasala]

It would help if you added what version of Excel and on what Platform you're using in your Profile. Also, it couldn't hurt to post a small sample of the data using XL2BB which would also include the formulas in question.
If you have access to Excel on the Web, you can Check Performance there which could help.

 

[Joe4]

Sheet 2 is the TB which has roughly 6000 rows and columns upto U

=+SUMIFS(TB!$J1:$J60000,TB!$C1:$C60000,$A452,TB!$E1:$E60000,F$3)*$B452

If you have up to 6000 rows of data, why are you going up to row 60000 instead of 6000?
Also, do you have a lot of formulas in "blank" rows of data?

It may help to eliminate a lot of unnecessary formulas.

Also note that Excel does have a formula dependency limit. It can only track something like 1 million formula dependencies.
Once you exceed that limit, Excel cannot zero in on what cells need re-calculating, and has to re-calculate every single formula in the entire workbook!
If you run into this, it is usually a sign that you are using the wrong tool for the project! If you have that many formula dependencies, you probably are working with a relational database model, and as such, it would be far better to use a relational database program like Microsoft Access or SQL.

Other than that, you may want to shut off Automatic Calculation, and only Recalculate at specified times (or manually).
It will still take a long time to calculate, but then at least it is only happening at certain specified times, and not every time you make a data change.

 

[Jalize]

Sometimes there are thousands of blank rows at the bottom of a sheet, after the last row with data. They cause the sheet to be very slow. Here is how you can get rid of it: