How To Improve The Speed of a Large Excel File?

[MattheusB]

Hello,

My company uses a large Excel file to report its financials. The file contains dozens of different tabs and it contains several formulas. The formulas it contains include: xlookups, offset, sumifs, etc. The problem is that the file takes a long time to both open and save. It is also painful to navigate through. Is there any way to make the file run faster? I think the only way is to hard-code the formulas, but I want to avoid that. The file is 12,157 KB.

Thanks!

[thenewkidd]

https://trumpexcel.com/suffering-fro...-spreadsheets/

This is a start.

There are other things you can do as well when getting into VBA, you can create Dictionaries to help speed up performance as well to reduce the number of sumifs.

[hrlngrv]

OFFSET could be the main culprit. OFFSET is a volatile function, which means EVERY formula calling it recalculates whenever ANYTHING triggers recalculation. For example, if cell B2 contained the formula =A2+1, Excel would only recalculate the B2 formula when the value in cell A2 changed; however, if C2 contained the formula =OFFSET(B2,0,-1)+1, Excel would recalculate the C2 formula whenever any cell's value changed, not just cell A2's. If OFFSET calls are arguments in XLOOKUP or SUMIFS calls, that's certainly slowing down recalculation.

In general, OFFSET(a!b,c,d,e,f) can be rewritten using 2 nonvolatile INDEX calls as INDEX(a!$1:$1048576,CELL("row",a!b)+c,CELL("col",a!b)+d):INDEX(a!$1:$1048576,CELL("row",a!b)+c+e-SIGN(e),CELL("col",a!b)+d+f-SIGN(f)). This could be improved by replacing entire worksheet references $1:$1048576 with smaller ranges which would include all the cells you'd want to reference. For example, if OFFSET's 2nd through 5th arguments would only ever reference columns D to Z and rows 5 to 1004, use a!$D$5:$Z$1004. Excel will recalculate formulas with INDEX(..):INDEX(..) calls whenever any cell in either INDEX call's 1st argument changes, but that's an improvement over recalculation when cells in OTHER WORKSHEETS change.

Next, if you're using A LOT of XLOOKUP calls, sort your ranges so you could use binary search. Even if you want EXACT matches, if you're using Office 365, you could use LET, as in

Please Login or Register  to view this content.

[hrlngrv]

Tangent: I agree with all the advice in that article except the blanket Avoid Array Formulas. That's too broad.

There are some things for which array formulas are ideal, and some for which array formulas are more efficient than using lots of supporting intermediate cell formulas. Indeed, if one wants the results from FREQUENCY, that function entered in multiple-cell array formulas is more efficient than alternatives using single cell formulas in the same cells. If you want something like =SUMPRODUCT(LARGE(x,{2;3})) or =SUMPRODUCT(SMALL(y,{2;3})), there's no efficiency gained from using =LARGE(x,2)+LARGE(x,3). That is, there's so much overhead from using LARGE(..) or SMALL(..) that calling them with array 2nd arguments is NBD.

I'd also quibble over using INDEX+MATCH rather than VLOOKUP. INDEX+MATCH is more flexible, but not necessarily more efficient. The article's mention of 100’s of columns of data indicates to me that the author doesn't understand that ranges are passed as references rather than as arrays of all values from all cells in the range.

[teb21]

I had a file which took between 6 and 10 minutes to process each time I ran it. The culprit was not offset but indirect. I replaced all of the indirect with index match and in my case helper rows and columns. The processing time dropped to 25 to 30 seconds. I do not know if yhis helps but is is another thing to look at.