Is there a way to make a 100k Workbook Faster?

[uAnonimo]

I have a 100k lines workbook, that 36 columns are used.
It has 25mb, every line has formulas, I've also use color rules on columns.
It takes long to open and it seems pretty heavy to edit, modify.
Is there a way to make it much lighter? Or Excel can't be light with so many lines?

 

[Oaktree]

Welcome to the board.

The answer to your question is: it depends.

Without knowing what the file is used for, the types of formulas you're using, and the specific contents of the columns, we can't give you specific answers... but, some thing to consider are:

1) Separate data from analysis... Perhaps your situation would allow you to use Power Query (or similar) to link to the raw data and create the summaries you need instead of storing both the data and the analysis in the same file.

2) Tweak your formulas to use more efficient functions (e.g. replace exact match VLOOKUPS)

3) Use VBA instead of formulas. As one example, scripting.dictionary can be *significantly* faster than index/match or VLOOKUP type formulas.

4) Remove unnecessary columns

5) Keep two versions of your file: one that has live formulas that you update with manual calculation on and a different, hardcoded file that you distribute/use for downstream decisions.

 

[MARK858]

I've also use color rules on columns.

If you strip out the color rules (on a copy of the workbook) how does the performance change?