Performance Issues on Large Spreadsheet

I suggest that you go to each sheet and press Ctrl-End. You have a lot of rows on several sheets but, on a few of them, Excel thinks the last used row is much further down than it should be.

Where that is the case, you need to delete all rows below the last "real" row and all columns to the right of the last "real" column using the Delete icon on the ribbon, NOT the delete key on the keyboard.


You use INDIRECT a lot. That is a volatile function and will recalculate whenever a change is made.

It might be better t define the data as Tables then Excel will manage the ranges for you.


Regards, TMS

These formulae evaluate to SUMPRODUCT across full column references:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

As, with Excel 2007 and above, there are over a million rows in a worksheet. So, in each case, you are making a making a matrix of 1,000,000+ times however many columns. Guaranteed to be slow.

I think you would be better making those references fixed and use an arbitrary high number of rows, say 30,000, 50,000 or 100,000 depending on how many transactions you are likely to need. Bit of a risk but a calculated one.

As you are already preparing helper columns with row numbers, it would make sense if you did that for the transactions sheets. And better to do those calculations once and "build" the range as you have done and then refer to it as required. Then you could accurately calculate the range to use in your INDIRECTs.

Regards, TMS

Is it possible to have the calculations made on each worksheet in designated cells (the same on each worksheet for the same value calculated) then on the 14May worksheet have simple formulae addressing those pre-calculated cells.

Example:

On worksheet 1W14May!AA1 have a formula like =COUNTIF(A:A,">0") Then just reference this cell in 14May!D7 with =INDIRECT(B7&"!AA1")

If you could do this kind of thing, then the calculations on 14May worksheet would be greatly simplified.

@newdoverman

so it's impractical to manually change every scorecard during the import process

If I understand correctly, calculation on the imported sheets would be good but maybe not possible

@TMS

If the worksheets imported were created from templates with the formulae in place the formulae on the 14May worksheet would be dealing with retrieving constants for the most part. I think that what we don't see is probably more important than what we do see. With nearly 20K rows on some worksheets, it makes me wonder how many worksheets there are that have this amount or more data. Combine that with the full column references and that will "kill" most computer's performance.

Maybe Excel isn't the best tool for this.

Maybe Excel isn't the best tool for this.

I think we need some feedback from the OP.

I think it is possible that, if those column references are adjusted, either to a fixed range or to a calculated range (somewhat less than a million rows), then performance might improve.

If there are a lot of suppliers, it might be a pain to get them all to start using an updated template ... but f=good thought.

Regards, TMS

I have taken some time to add tables to each of the worksheets and then used table nomenclature to make the calculations as much as I could. The formulae on the 14May worksheet have been changed to reference the calculated cells on the worksheets. I also ran an excess formats cleaner to rid the worksheet of cells not involved in the calculations.

This was done using my slowest computer and I notice an increase in performance. This workbook is never going to be fast due to the massive number of calculations involved.

To shrink the ranges, I used the Clear Excess Formatting tool from Microsoft which is an add-on for Excel.
http://xsformatcleaner.codeplex.com/releases/view/98007


Did you try the method that I created for you using tables and calculations on the worksheets themselves and then just addressing the cells with the results. This takes the "load of calculation" off of the 14May worksheet?

A database solution I think is the key for you especially when the number of rows being calculated must be over 100k.

Good luck with the project.