Excel Sheet is not dynamic but uses the whole area of a sheet

[wit4r7]

Hi everyone,

I have created an Excel file, which consists of several sheets and innumerable, partly very complex formulas, which are interlinked with each other.

One of the sheets (the one where all the calculations are done) uses the whole area of the worksheet for a reason I can't explain. So the scroll bars do not grow dynamically but are on the smallest size.

The Excel also needs about 3-4 min to open and is 4MB in size.

As soon as I try to delete a row or column, I get the message that there is not enough memory.

I am using O365 ProPlus 64 bit (version 1808). The laptop has 16GB Ram, but Excel uses a maximum of 3GB.

Do I have a way to save my tool or do I have no choice but to rebuild it? (The effort for the new creation would be about 1 working day).

Can someone explain to me why the error occurs, so that I can avoid it in the future?

Thanks a lot!

wit4r7

[AliGW]

Welcome to the forum.

First thing to check: are you using full column references in your formulae (e.g. A:A)? If so, limit them to what is necessary.

Are you using volatile functions such as INDIRECT and OFFSET?

Share some of these complex formulae with us.

[wit4r7]

Thank you very much

I crosschecked the formulas and the name manager and I didn't find a full column reference.

The formulas as such are not complicated. They are mostly just IF, TOTAL formulas (In KombinatioN) and some LINEST formulas.

The problem is the number (~1200 formulas) and the logical linking within the whole file.

I have a coversheet where you can select the language and various factors (complexity, budget, etc.). Can be customized. Then on the next sheet there is a kind of questionnaire that calculates different ratios based on the selection on the coversheet. The numbers from the questionnaire are then evaluated on my "Problemsheet". With the numbers some KPIs are calculated and diagrams are created.

Please Login or Register  to view this content.

best regards