Large data....slow excel performance..!!

Reduce the number of calculations going on. Lots of array formulas and SUMPRODUCT formulas, lots of IF(ISERROR(formula),x,formula) ...these things all take a toll on performance. They're all excellent in small doses, but large data sheets need a better solution.

To increase speed, you might have to give up on fancier formulas and make the sheet larger by adding helper cells or helper sheets to do some calculations in a more straightforward manner.

Post up some examples of the heavy-lifting formulas you're using, and I'm sure we can offer some simplification.

Whilst I agree with all of prior post I would also add that in big models reducing the prevalence of Volatile functions is of vital importance... common examples being the likes of: TODAY(), NOW(), OFFSET, INDIRECT and even INDEX (when used to return RANGE as opposed to VALUE). Conditional Formats etc are also Volatile.

For more info. have a read here: http://www.decisionmodels.com/calcsecretsi.htm
(read the other pages too if performance is vital to you)

Hello Aryaa,

We request you to attach a sample file so that we can help you in this.

I can already tell you...that ONE formula you posted registers just under 10,000 calculations. That's ONE cell. How many cells have that formula in it?

And there's a ton of duplication, the same range being parsed over and over, and then you go back and parse it AGAIN for the same date. Look at the underlined parts here:

=COUNTIF('Activity Report-Active'!J2:J803, "25-Nov-08")+COUNTIF('Activity Report-Active'!J2:J803, "26-Nov-08")+COUNTIF('Activity Report-Active'!J2:J803, "27-Nov-08")+COUNTIF('Activity Report-Active'!J2:J803, "28-Nov-08")+COUNTIF('Activity Report-Active'!J2:J803, "01-Dec-08")+COUNTIF('Activity Report-Inactive'!J2:J940, "25-Nov-08")+COUNTIF('Activity Report-Inactive'!J2:J940, "26-Nov-08")+COUNTIF('Activity Report-Inactive'!J2:J940, "27-Nov-08")+COUNTIF('Activity Report-Inactive'!J2:J940, "28-Nov-08")+COUNTIF('Activity Report-Inactive'!J2:J940, "01-Dec-08")

You may need to reconsider some redesign. What if your Activity Report-Active sheet had a column of dates that had already collected the COUNTIF data? Then you could just add those cells in a straight

=AA1+AA2+AA3, etc...

You potentially save millions of calculations with this one change.

Different sheets actually.

I would agree that the COUNTIF you have can be streamlined but we need to see a sample as has already been suggested. Your countifs can be replaced but we'd need to understand the logic of the dates selected - and why for ex. 29th & 30th Nov are excluded -- presumably because they are non-workdays ?

Originally Posted by DonkeyOte

Different sheets actually.

Duh....there goes my points for attention to detail.

Anyway, the number of calcs are still monstrous, and if there are hundreds of these cells, my goodness!

Size of the spreadsheet actually isn't the problem. A sheet can be 20mb in size and be structured in a way that that calculations happen quickly. That ONE formula has upwards of 10,000 calculations. Spreadsheet optimization would be to find a way to do those 10,000 calculations ONCE, then let other cells benefit from that completed calculation.

Putting the same references into cell after cell is the redundancy that you need to try and eliminate now that you're already experiencing the slow down.