Hi,

I'm not sure if the title is meaningful enough so here's the full description. I have a complex spreadsheet which contains over 10 worksheets, most of them have data with about 15 columns and about 3000 rows. Data in these worksheets is updated on regular basis by pasting from external source, then various calculations are performed. There is a lot of VLOOKUP. This spreadsheet has been evolving, I've been adding and changing different things and the point is that I don't know exactly what caused my problem and when. What happens now is that every time I paste a lot of data or apply autofilter Excel freezes for some time, up to a minute, depending on how much data I paste. After it unfreezes it 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 I can skip the step all together by enabling manual calculations

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

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

thanks

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

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