Overload in workbook due to heavy formulas

[espenskeie]

Hi excel experts

I have a workbook that is filled up with lots of data tables, sumif and indexing formulas.

I know that these are heavy for my workbook. It takes about 15-20 seconds just to save the workbook.

1. Is there any better way of making those kind of lookups to avvoid killing the RAM and slowing down my computer?
2. Can I somehow measure the size of the load that I have in my workbook?

The workbook is only 12MB big, but the amount of heavy formulas in thousands of cells is what slows it down.


Best regards
Espen

 

[igold]

On the bottom of the workbook with any worksheet active is the Status Bar. In the Status Bar on the far left hand side is a "Function" (for lack of a better description) that is named "Workbook Statistics". Could you post a picture of what your workbook shows when you use this function. If you do not see it, you may have to right-click the status bar and put a check mark next to it to enable it. I would be curious to see the "stats" of your workbook.

 

[igold]

As an afterthought, the "Workbook Statistics" option became available on 365, so you may not have it. That said, you may want to update your profile to indicate your Office Versions and your Platform.

 

[espenskeie]

On the bottom of the workbook with any worksheet active is the Status Bar. In the Status Bar on the far left hand side is a "Function" (for lack of a better description) that is named "Workbook Statistics". Could you post a picture of what your workbook shows when you use this function. If you do not see it, you may have to right-click the status bar and put a check mark next to it to enable it. I would be curious to see the "stats" of your workbook.

Thank you for the tip, but it seems to me that this function is not available, all i can find when googling is that it is connected to Office 365.... I have Office 2016..

Br Espen

 

[Alex Blakenburg]

Without seeing the workbook it is a bit hard to tell, but some possibilities are:-

• General housekeeping - if you go ctrl+end on each sheet does it go way past where the data finishes ?
  Clean that up
• Are your formulas using whole column references eg A:A ?
  Ideally reduce these to range you need. Using tables can help with this, since they autoexpand so you don't have formula maintenance issues.
• Are you prepared to use helper columns and rows ?
  • If your index match is looking up the column.
    Have a helper row at the top where you do the match once and on every row you then just reference the number in that helper row and you don't have to do the match each time.
  • If you have multiple columns doing a row lookup using index match. You can have a helper column that returns the result of the match and the other column can just reference that column so you only have to find the row once and not each time in every column.
  • Do you have multiple column doing the same If (index match), do the if once in a column and have the other column access the results of that column. So it becomes an if on the same row rather than each column doing the index match just to evaluate the if.
• Are you using a lot of conditional formatting ?
  These formulas are volatile and are quite a heavy load.
• For that matter are you using volatile functions eg Offset & Indirect
• Do you have links to external files ?
  They also really slow your spreadsheet down.

You could even consider a Power Query solution, and have it do a lot of the Lookup, data transformations and possibly summarizations for you.

 

[espenskeie]

As an afterthought, the "Workbook Statistics" option became available on 365, so you may not have it. That said, you may want to update your profile to indicate your Office Versions and your Platform.

I found an 365 subscription as well.... But currently it is in Norwegian, can you make som sense out of it anyway:

 

[espenskeie]

Without seeing the workbook it is a bit hard to tell, but some possibilities are:-

• General housekeeping - if you go ctrl+end on each sheet does it go way past where the data finishes ?
  Clean that up
• Are your formulas using whole column references eg A:A ?
  Ideally reduce these to range you need. Using tables can help with this, since they autoexpand so you don't have formula maintenance issues.
• Are you prepared to use helper columns and rows ?
  • If your index match is looking up the column.
    Have a helper row at the top where you do the match once and on every row you then just reference the number in that helper row and you don't have to do the match each time.
  • If you have multiple columns doing a row lookup using index match. You can have a helper column that returns the result of the match and the other column can just reference that column so you only have to find the row once and not each time in every column.
  • Do you have multiple column doing the same If (index match), do the if once in a column and have the other column access the results of that column. So it becomes an if on the same row rather than each column doing the index match just to evaluate the if.
• Are you using a lot of conditional formatting ?
  These formulas are volatile and are quite a heavy load.
• For that matter are you using volatile functions eg Offset & Indirect
• Do you have links to external files ?
  They also really slow your spreadsheet down.

You could even consider a Power Query solution, and have it do a lot of the Lookup, data transformations and possibly summarizations for you.

Thanks for the input, I will look into those tips.. I have considered most of those stuff in my workbook, but I will see if there are any chances to make it even better...

For many other purposes I use QlikView, but on this workbook I am not the only one using it, and that's why I have to keep it in Excel...


Best regards Espen

 

[igold]

From what I see, and I am not an expert, but 15mb and an extensive load time as you suggest does seem out of place. The first thing I would check as Alex suggested, would be to look for phantom ranges. That is to make sure that your last cell with data on every sheet is where it should be and not some cell that it either way out there as far as the column or rows or both. In the picture of the statistics you provided, the top line shows that Excel thinks the last cell with data on the active sheet is Cell DX340. That's a lot of columns. You may or may not have data in all those columns, but it is something to think about. You can check for the last cell with data either using the Workbook Statistics for every sheet or by using the keystroke combination of CTRL + END. As suggested if your last cell with data is not where it should be, you should clean that up. Phantom ranges will slow your workbook load times and performance immensely.