Need help with very Slow Excel Workbook

[InTheCube]

Hi.

A client of mine has been complaining about a particular Excel workbook (Excel file) that is running very slowly.

Whenever they try to make changes to any of the cells, it takes about 10-20 seconds to make the change. Excel freezes up and becomes unresponsive during this time.

I tested this file on several different machines, including my own, to ensure it wasn't a problem with the machine. It runs very slowly on all 3 machines I've tested.

It's a .xls file, meaning it is Excel 97-2003 format.

The workbook doesn't do any calculations or make use of any formulas at all, and cells don't refer to other cells. The workbook is simply used to store tabular data, and contains about 12 worksheets, and is about 4mb in size.

Any ideas on what the problem could be, and how to resolve this issue?

 

[remybfg10k]

Sounds like a vlookup/hlookup issue, lemme see what i can dig up

Also what does task manager report the CPU usage as?

 

[chutzpah44]

Are the wookbooks linked at all? Are you using any colours? 4mg is fairly big. Change the calc to manual it may speed it up

 

[greggpb]

Check for volatile functions like date..

Under perferences set a max cal cycle

 

[Sting]

+1 : first step!

Change the calc to manual it may speed it up

 

[greggpb]

also check that the are no macros in the workbook..

alt+f11

 

[InTheCube]

Sounds like a vlookup/hlookup issue, lemme see what i can dig up

Also what does task manager report the CPU usage as?

Not sure what vlookup and hlookup actually do, but I don't think its used. It's a simple spreadsheet, only used for capturing data. The Autosum funtion isn't even used.

Task Manager reports Excel as using 50% of the CPU for the full duration that Excel is frozen. I have a dual core CPU, so I guess it is maxing out one of the cores.

 

[InTheCube]

also check that the are no macros in the workbook..

alt+f11

This is what ALT+F11 brings up:

http://i31.tinypic.com/ige14o.jpg

Not sure what it means, but it doesn't look like it has any macros, am I right?

 

[Amida]

I've had problems like that. It happens when the document has been moved to a new server or location. Then what excel does is it keeps trying to reference back to the old location that is currently empty. That's why it so slow.

have'nt found a real solution yet.

 

[InTheCube]

Are the wookbooks linked at all? Are you using any colours? 4mg is fairly big. Change the calc to manual it may speed it up

WorkBooks are not linked at all, not even cells within a WorkBook (as far as I know, or the client has told me).

What do you mean by Change the calc to manual it may speed it up ? How do I do this?

 

[Mars]

Try copying the data and pasting it into another workbook.
Chances are, if this workbook has no macro's or vb and its that big, it was probably created in some older version of excel, and still has some of that original info.

Perhaps its time to start a new workbook?

Otherwise you could export the whole lot to some other format , csv for example and then import the data into another workbook.

Changing the calc to manual will not make a difference if there are no macro's or vb.

 

[Sting]

Tools > Options > Calculations > check MANUAL

 

[InTheCube]

Tools > Options > Calculations > check MANUAL

I'm using Excel 2007

There's No Tools > Options menu.

Update: OK I found it in 2007. Changed to Manual, Saved the file. Closed it. Opened it again. Tried editing a cell. No difference.

Still slow as hell.

 

[Sting]

See here...might help

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

 

[greggpb]

can you control-break or control-c out of the delay ?

if so, it's a macro problem

 

[Mars]

The workbook doesn't do any calculations or make use of any formulas at all, and cells don't refer to other cells. The workbook is simply used to store tabular data, and contains about 12 worksheets, and is about 4mb in size.

Changing the calc to manual will not make a difference if there are no macro's or vb.

I'm using Excel 2007

There's No Tools > Options menu.

Update: OK I found it in 2007. Changed to Manual, Saved the file. Closed it. Opened it again. Tried editing a cell. No difference.

Still slow as hell.

can you control-break or control-c out of the delay ?

if so, it's a macro problem

Eish!

 

[InTheCube]

can you control-break or control-c out of the delay ?

if so, it's a macro problem

Nope.. that doesn't work.

I've managed to narrow it down to one particular worksheet. The problem only happens on this one worksheet - the latest one. All the others are fine.

 

[Conradl]

Have you tried copying and pasting it into a new book? This normally fixes 80% of problems for me.....

 

[greggpb]

on that worksheet.

Ctrl-A, Ctrl-v select new sheet, select cell A1, right click, paste special, values.