How to speed up sluggish worksheet???

Big Blue

Board Regular
Joined
Mar 24, 2002
Messages
52
I have a 2000 row spreadsheet with some hefty formulae, including several with 6 nested if stmts. Whne data is entered, it takes a long time for the sheet to run through it's calculations. Anything I can do to spped it up (short of buying a faster CPU)?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You may want to rethink your entire setup to see if any efficiencies can be gained.

http://spreadsheetstyle.com/

is a great resource for this.

Barring that, you may have a lot of volatile functions or complicated arrays that take a long time to process. It is just a long, slow haul.

If you find yourself having to update data and, after *each* entry the spreadsheet goes through a recalc process, you have to set your Calculation setting to manual

Tools>Options>Calculations (Manual)

then set it back to automatic, or hit F9 (or Ctrl+Alt-F9) to recalculate.

HTH,
Jay
 
Upvote 0
Thanks for very good article Mark> I read through, but nothing I can change there ( I did change to 'Automatic Except Tables').
I've automated a very big log, and the summaries require several array formulae. Maybe there's nothing that can be done... still better than manual calculation!
 
Upvote 0
Thanks for advice Jay.
My calulations are twofold - one, I strip dates out of serial numbers and post to ther columns to eliminate need for users to re-enter(limiting chance for error). I have extensive summaries subtotalling several aspects all by weeks (start and end dates taking statutory and company holidays into account).
THOUGHT: The summary is a separate sheet. Could I somehow only calculate summary when someone looks at sheet? (ie instead of updating all the subtotals as a user is entering data on log sheet?) !!!!
 
Upvote 0
On 2002-04-08 08:44, Big Blue wrote:
Thanks for very good article Mark> I read through, but nothing I can change there ( I did change to 'Automatic Except Tables').
I've automated a very big log, and the summaries require several array formulae. Maybe there's nothing that can be done... still better than manual calculation!

'Automatic Except Tables' won't make any difference unless you're using Data Tables. Have you considered summarizing your data using PivotTables?
 
Upvote 0
No, to be honest I've never explored Pivot Tables. Only have a rudimentary understanding of them. If you think they might serve this kind of purpose, I start to research. My current summaries have about a dozen array formulae to provide weekly subtotals of volumes and values.
 
Upvote 0
You can go into tools, options ... and set the calculation to manual during data entry. When you are finished, you can then either hit F9 to calculate or set it back to automatic calculation.
Alternately, you can copy-paste-special the values for all rows except the first one, and simply copy the first row's formulas into any new rows when needed. Depends on hoiw much the information in the boady changes with time. This solution is good when the data remain essentially unchamged after they are captured.
 
Upvote 0
Also, when you have some spare time, have a look at Excel's Database functions... they are all prefixed with "D"

DSUM
DCOUNT

etc etc

it (sort of) switches excel into database mode - my non-technical way of thinking

one of my recent 20 meg spreadhseets which took 3 mins to recalculate was smoothed down to 9 megs and just a SECOND to recalculate

depends on what you want though, they're not always a definitive better way....... check them out though
 
Upvote 0
Thanks for effort, but I started with D...
and wound up with a horrendous spreadhseet
imagine subtotalling 3 categories of items, by 8 departments, for every week of the year excluding holidays... I had to use arrays
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top