Unbelievably slow spreadsheet

[DashboardNovice]

I have an Excel file I received from a coworker. When I click a cell it takes forever for the click to go through. Same with copying a cell. I also noticed that all my other open Excel files (which are normally not slow) are also slow when I have the problem file open.

What could be causing this spreadsheet to run so slow?

The other spreadsheets have the same amount of data on each tab, same number of named ranges, etc.

Edit: There appears to be one tab in particular that is slow, not the entire spreadsheet. When I click on all other tabs on the problem spreadsheet, it doesn't take a long time to make a selection. So this problem appears to be related to a specific tab.

 

[ETAF]

if it has a lot of volatile functions in it - so everytime you click or move in a cell - ALL those functions will also update

thats one reason

Volatile function

A small number of Excel functions are "volatile". Volatile functions trigger recalculation on every worksheet change, so they can have a drastic impact on worksheet performance.

exceljet.net

 

[Marc L]

Other points to check :​

• conditional fomatting
• VBA event procedure within the worksheet class module
• worksheet VBA event procedure within ThisWorkbook class module …

 

[DashboardNovice]

if it has a lot of volatile functions in it - so everytime you click or move in a cell - ALL those functions will also update

thats one reason

Volatile function

A small number of Excel functions are "volatile". Volatile functions trigger recalculation on every worksheet change, so they can have a drastic impact on worksheet performance.

exceljet.net

Good suggestion. The tab that is very slow doesn't have any functions of any kind so I doubt it's due to volatile functions.

 

[Marc L]

A classic workaround before Excel crashes and loses data as usual :​

copy raw data to a brand new worksheet and delete the original.​

And for data safety this is the first of three rules : backup your workbooks !​

​

 

[DashboardNovice]

Other points to check :​

• conditional fomatting
• VBA event procedure within the worksheet class module
• worksheet VBA event procedure within ThisWorkbook class module …

1. In the VBA Editor, I see the workbook listed twice (redacted twice in the attached pic). With other similar files, the workbook is only listed once. Could this have something to do with how sluggish it is?

2. I opened a different file then I was suddenly prompted with a message about the PERSONAL.XLSB workbook. This has never happened before. Does this have something to do with the problem? See the pic.

I went back into the problem file and now the workbook is listed only once. I don't know how to explain that.

 

[Marc L]

Possible cases when workbook appears twice on VBE side :​

• Excel is 'aware' (JCVD tribute) : that means some memory issue, the Excel weakness,
  so close Excel, do not save the workbook if possible, sometimes the computer needs a restart …
  With such tricky workbook : close any application before to open it alone under Excel.
  
  
• Damaged workbook : so according to first safety rule, restart from a backup or try my previous post classic workaround
  then SaveAs to binary format .xlsb with a different name if it already uses this file format.
  
  
• Check the hard drive integrity as sometimes the issue does not come from Excel but from a hard drive failure …

 

[BobBridges]

Good suggestion. The tab that is very slow doesn't have any functions of any kind so I doubt it's due to volatile functions.

The problem with volatile functions is that they don't know, they can't know, when it is reasonable to recalculate—so they recalculate at every change. The functions needn't be invoked on that one tab.

However, if you're saying only one worksheet is slow and the others aren't, I don't think it can be volatile functions in this case. Maybe something complicated in one of the lookup functions? How much data is on that sheet? And are there any event modules for that worksheet alone?

 

[DashboardNovice]

The problem with volatile functions is that they don't know, they can't know, when it is reasonable to recalculate—so they recalculate at every change. The functions needn't be invoked on that one tab.

However, if you're saying only one worksheet is slow and the others aren't, I don't think it can be volatile functions in this case. Maybe something complicated in one of the lookup functions? How much data is on that sheet? And are there any event modules for that worksheet alone?

I just don't see any VBA code in any of the folders in the VB Editor. I'm really not sure what to think, but I appreciate your suggestions.

 

[BobBridges]

How big is this workbook, anyway? I don't suppose you can post it here for others to look at? Probably contains proprietary information?