Working on this Excel workbook causes Excel to freeze for several seconds. Why?

Question

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've tested this file on several different machines 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?

Update:

• I am using Excel 2007. Haven't been able to get hold of earlier versions of Excel to test on.
• I've managed to narrow the problem down to the latest worksheet in the file/workbook. All the other worksheets are very fast. No problems there.

Answer 1

Have you tried simply copying the worksheets into a new spreadsheet? This fixes many goofy Excel issues.

Answer 2

Open the Excel file in Excel 2007 and save it in the Excel 2007 format (.xlsx) - it will solve your problem. I had a similar issue and this resolved it.

Answer 3

Check the formatting.

If individual cells are formatted with colour, border etc. instead of entire columns or rows then the file size and speed of the workbook will be impacted. Check also that there is no conditional formatting applied to any of the cells - this is a killer in terms of performance. In a workbook with many thousands of data cells this can get quite noticeable.

Most of the time this formatting can be stripped out and replaced with column/row formatting without any major impact on the look of the worksheets.

Answer 4

Ensure empty rows and columns are really empty, by removing them once again. In old versions of Excel this will give you new empty rows and columns; in later version you might not see those.

To remove: right-click and select something like "Delete Rows". Do not press the Delete key, which would only delete the empty contents and does not help you.

To select: select the first empty row below the data, then hold down Shift, and press End followed by Down one or two times. Likewise: select the first empty column, keep Shift down, and press End followed by Right one or two times. (I am not 100% about the keys. You can also try Ctrl-Shift-End to extend the selection to the last cell in the sheet.)

Answer 5

Change from Automatic to Manual Calculation and see if it's still slow. If so, then you can safely rule out calcs, but my Spidey-senses say otherwise.