Excel Workbook Lags

[Hyperdude]

Hi,

I am working with a large excel workbook about 22 mb with around 40 sheets. The largest sheet contains around 25000+ rows of data and around 13 pivot charts and pivot tables. There are no macros used on the workbook.

The issue is whenever I input a single set of data into a cell. It takes a long time for the process to complete.

What I have tried:

-Deleting the Pivot charts and table
-getting rid of unwanted conditional formatting
-turning off automatic calculations

The above solutions did not work at all. However when i copied the workbook on to my smart phone using polaris office there is no lag at all. Does anyone has any idea on how i can solve this issue?

[:) Sixthsense :)]

Set the calculation as Manual and calculate it manually

Shift+F9 - To calculate the active sheet
F9 - To calculate the active workbook

[FDibbins]

also, check for any formulas that use entire columns (B:B) or rows (1:1) in them - reduce them to the minimum needed for the calcs.

and try to avoid any array functions (like sumproduct) if at all possible, they can be resource-hungry

[Hyperdude]

hi,

Thanks for the quick reply Sixthsense and FDibbins. FDibbins, here are no formula that check for entire columns and rows and also there are no array functions not array formulas. however i have quite a number of dynamic ranges does that affect the performance of excel? The thing I do not understand if why did the workbook run so smoothly on my phone and not on my workstation.

[FDibbins]

dynamic ranges could slow the system down, and maybe you're phone restricts the size of the sheets...which makes me think. have you checked see if you have unused rows and columns on you're sheets?

use end-home to go to the last row/column on the sheet (you may also see a very tiny scroll bar). when you scroll left and up, if that cell is further out than the last actual cell/row/column, delete ALL the excess rows/columns

[Hyperdude]

oh I just realized all my dynamic name ranges uses entire column reference maybe that's what causing the lag. is there another way to create dynamic ranges?

I am currently using this

Please Login or Register  to view this content.