Excel workbook unbearably slow

[atatawy]

good morning,

i have recently built an excel workbook containing 14 sheets, all sheets read data from one main sheet. three of the 14 sheets are big tables streaching out from row 2 till row 250 and going across from column A till column CJ

all formulas colculate from row 2 till row 1,000 in anticipation that the amount of information would grow.

i have already reduced the number of rows from 5,000 till 1,000

my problem is that the workbook is tarting to become unberably slow to work on, any adjustments done we must wait a good 10 minuets for the sheet to finish calculating, even when adding simple things such as a new title that has no inputs in any formula!!

can any one sugest ways to make the sheet faster, or to stop the sheet from calculating every time i do anything on the sheet

thanks

 

[atatawy]

sorry just to add, also the workbook turnes to a white screen if i click any where on it and it gives me a "Not Responding" msg.

 

[Richard Schollar]

Hi Atatawy

Have you tried turning calculation to Manual? Do this by Tools>Options>Calculation tab and select the Manual calculation option. Then the formulas should only recalculate when you hit F9. This will save the sheets recalculating whenever you make any adjustments to the sheets.

Best regards

Richard

 

[SydneyGeek]

Further to Richard's suggestion, Shift+F9 will calculate only the active sheet. That is a big time-saver.

Also, if you have so many columns, consider limiting the number of unused formula rows or removing them altogether.
Say, for example:
A150:CJ150 is your last row with data.
All columns in A:CJ are formulas.

You could remove formulas from B151:CJ1000, then set up a routine to fill in the additional row every time another record was added (you would see it in column A). Example:
Select B151
Ctrl + Right Arrow will take yo to CJ if there are no breaks
Shift + Down Arrow will extend the selection down one row
Control + D will fill the formulas down to the next row.

Denis

 

[atatawy]

workes great, thanks!