Excel workbook unbearably slow

atatawy

New Member
Joined
Jul 9, 2006
Messages
21
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

atatawy

New Member
Joined
Jul 9, 2006
Messages
21
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,141,019
Messages
5,703,771
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top