Excel Sheet Suddenly Runs Slow

[Ed O'Brien]

PC = Dell Dimension... 4GB RAM Intel Duo core 6700 @ 2.66GHz 2.67GHz.
(Plenty for this job)!

Excel 2007 - Vista Home Premium

I have an Excel workbook for personal accounts. It has several sheets:
Current Account, Credit Cards, Statements, Income Tax....

Suddenly the Current Account sheet - which has much more info and formulae -
has suddenly started to run incredibly slow. It took 37 seconds to paste
just two small entries.

As the page loads, it can be seen to hesitate as it loads. The same when
scrolling the page. It behaves as though it is moving megabites of info yet
the total file size for the workbook is only 218KB.

Has anyone experienced this and can anyone help with a cure?

TIA for any input.

Ed

 

[Michael_R]

I had a similar experience once with a steadily growing sheet. The issue
disappeared when I removed all conditional formatting from that sheet.

 

[Ed O'Brien]

Thanks, Michael. I do have a fair bit of Conditional Formatting, but
removing it is not really an option. I have several quite complicated
worksheets and workbooks with huge amounts of data, even linking data from
one sheet to another and one book to another, but none run slow. It is just
this one sheet.

 

[Michael_R]

Ed, I suggest that just for the sake of analysing the root cause of your
challenge you take a copy of this sheet into a new workbook.

Then time the calculation as is.

Then remove all conditional formatting and time the calculation again.

If there is no significant difference then we know that Conditional
Formatting is not the reason for sluggish performance. But if the difference
is recognisable you know at least why.

 

[Jim Thomlinson]

It is hard to comment on the specifics without seeing exactly what you are up
to. That being sid check out the following link:

http://www.decisionmodels.com/index.htm

look at Calculation, Optomizing Speed and Memory.

 

[Ed O'Brien]

Thanks, Jim. Problem resolved. Check my response to Michael.

Best ewishes,

Ed

 

[Ed O'Brien]

Yes, Michael. Conditional Formatting was the bug.

It was a bit of work but on checking each cell's Conditional Formatting
under "Manage Rules" I found a single cell format had duplicated over the
years to an enormous number. A guess would be around fifty times. And that
was for each cell where the formula was used, 45 in all!! (Probably when
repeatedly replacing data - hard to say).

Deleting the lot and re-entering the formula just the once, did the job. All
now fine.

Thanks for pointing me in the right direction.

Best wishes,

Ed

 

[ozgrid.com]

I suggest reading this page;
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm\