worksheet running really slow

**slimsadie** · 06-21-2009, 03:42 PM

The spreadsheet I am using has started working really slowly, i.e. if I input '12' into a cell and hit the return key it will take about 3 seconds for the data to appear and then about 8 seconds until the egg-timer icon goes and the cell below is highlighted. If I highlight any sections of the data and try to move or sort the data it hangs - I waited for 30 minutes then had to crash the pc to close the program as it wouldn't respond to anything. The other worksheets within the same file appear to be working fine. There is a fairly large amount of data on the problem worksheet - 6 columns, 500 rows (equiv to an annual bank statement). I have tried saving the file to a different destination, increasing my windows virtual memory and checking my PC task manager to see if anything else is causing a problem, but nothing has helped. I am using Excel 2007. Any help or suggestions would be hugely appreciated!

**shg** · 06-21-2009, 03:48 PM

Welcome to the forum.

Is there any code in the workbook?

500 rows x 6 columns is a trivial amount of data. That said,

• Use efficient formulas

o Don’t duplicatively calculate the same expression in multiple formulas; use a helper cell, row, or column
o Avoid volatile functions when possible (OFFSET, CELL, INDIRECT, ...)
o Avoid array formulas when possible
o Limit the range of references to be the minimum necessary

• Arrange the sheet so that most references are to cells to the left or above

**martindwilson** · 06-21-2009, 03:49 PM

how big is it in kb

**slimsadie** · 06-22-2009, 08:42 AM

I'm only familiar with the basics on Excel, so as far as I know I haven't put any code in! The only formula I have used on that sheet is the 'sum' function, to total one column at 3 different points. I've just remembered (I started it quite a while back!) that part of that sheet is copied and pasted from an on-line bank statement onto an originally blank worksheet. Would that have code in it? If so, how would I find out?

I will look up the size in KB once I get back.

Thank you!

**slimsadie** · 06-24-2009, 04:23 AM

The size of the file is 237KB

**Steve-B** · 06-24-2009, 04:32 AM

Hardware problem with you CPU's RAM?

**martindwilson** · 06-24-2009, 06:18 AM

The size of the file is 237KB

thats not big at all, must be something running

**broro183** · 06-24-2009, 06:39 AM

hi,

Do your Sum (or conditional formatting?) functions total the whole column?
If so, try limiting them to the used range (I think Excel's meant to automatically do this in Excel 2007 but it can't hurt to do it explicitly in the formulae). If rows will be continually added, then you can use dynamic named ranges to ensure formulae automatically update the range (see Debra's explanation, http://www.contextures.com/xlNames01.html)

To remove some/any impact of the copied sheet, you could try selecting the range (in a copy of your file), & then Edit - Clear - Formats.

"Normally" you should receive a "Disable/Enable macros?" warning when you open files based on your security setting (Tools - Macros - Security, hopefully "medium").
To check if there is any code, press [alt + F11] to open the VBE, press [ctrl + R] to show the Project tree, find & expand the "project" which has the name of your spreadsheet, double-click each of the sub items to open the code window (if any of the windows have anything more than "option explicit" in them, then you have code in your file). Once you've checked them all, close them all using the lower cross at the right of the window, & then close the VBE using the cross at the top right of the window.

Daved Mcritchie's below link may have helpful suggestions:
http://www.mvps.org/dmcritchie/excel/slowresp.htm (long page as it has a lot of info)

The small file size suggests that the next link of Charles William's is not likely to be as helpful but it is still an interesting read & gives detailed info about Excel's calculation etc (see other links below the page title):
http://www.decisionmodels.com/optspeed.htm

To help identify if any other processes are chewing up your computer's resources you can download a modified Task Manager from http://technet.microsoft.com/en-us/s.../bb896653.aspx (Google "process explorer" for more details)

hth
Rob