Excel not responding before calculations

W

wilex

Hi,

I'm not sure if the title is meaningful enough so here's the ful
description. I have a complex spreadsheet which contains over 1
worksheets, most of them have data with about 15 columns and about 300
rows. Data in these worksheets is updated on regular basis by pastin
from external source, then various calculations are performed. There i
a lot of VLOOKUP. This spreadsheet has been evolving, I've been addin
and changing different things and the point is that I don't know exactl
what caused my problem and when. What happens now is that every time
paste a lot of data or apply autofilter Excel freezes for some time, u
to a minute, depending on how much data I paste. After it unfreezes i
performs calculations on the new data. So it looks like this:
- I paste data or apply autofilter
- Excel freezes for a while (not responding)
- Excel performs calculations, I can see progress, I can press ESC or
can skip the step all together by enabling manual calculations

I have no idea what caused it but this delay is very annoying. When i
happens it looks like only 1 CPU core works, Excel utilises 50% of CP
power as opposed to normal calculations which utilise both cores. I hav
also noticed what if I remove one of the worksheets, a central one whic
picks up data from all other worksheets, the problem disappears.

What can I do to identify what exactly is causing this? I have Windows
64bit, Excel 2007.

thank
 
W

wilex

I've found the problem - recently I converted all ranges of cells t
tables which made formulas more meaningful and easier to work with, bu
at the same time it caused the exact problem I described. I've jus
converted tables back to ranges and everything works just fine now.

Can I do anything to fix this delay while still using tables
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top