Spreadsheet file takes a long time/stops responding between every action

ETA: a smaller version of my spreadsheet. Unfortunately the problem doesn't persist once I remove about 950 rows, so I'm not sure I'll get any help that way. I did try removing some of the other sheets (also not on the small one) from the original to see if that fixed it, but it did not.

I am working with a workbook that is not overly large (520 KB), but every time I perform an action, it takes forever (blue "thinking" circle) and/or stops responding before finally completing the action. For example, I'm moving some rows around, so I cut a row from one place (that action is fine), but when I "Insert Cut Cells" to another location, that is where the spreadsheet hangs up for up to 60 seconds before it inserts them. Even something as simple as inserting a blank row will do this. It's a personal spreadsheet, so at least I'm not costing a client time/money, but still!

I have followed all of the troubleshooting steps on this page but to no avail: https://support.microsoft.com/en-us/...5-4ca6853a1ff4. Most weren't relevant, but I checked/did them anyway, to include moving the file locally rather than on my thumb drive. I did have some random hidden objects and some formulas that referred to entire columns, so I had hoped that fixing/removing those would help, but it did not I also tried on a different computer (both Windows 10), and that didn't help, either. This isn't systemic--it appears to be only this file.

There are no links to external sources/workbooks, either.

Any other thoughts/suggestions I can try?? One sheet goes to column M, but I can't "delete" the blank rows at the bottom (I read awhile back there's a bug with that, but I don't remember where or what the fix was), so I don't know if that has something to do with it?

You would be more likely to get a constructive answer if you share a desensitised version of the workbook.

See the yellow banner.

As the file is only 520kb, I'd rather see the full file with sensitive information removed BUT displays the problem, rather than a cut down version that doesn't.

That said ...

On Sheet Item#s, this

Formula:

Please Login or Register  to view this content.

could be just this

Formula:

Please Login or Register  to view this content.

On this sample, the Conditional Formatting is split. So, if that is the case in the real file, you could have a lot more CF than you need. I'd be inclined to remove all the CF and re-apply it.

On the Stampin' Rewards sheet, you might be better off using INDEX/MATCH rather than VLOOKUP. There is an argument that VLOOKUP will require you to read the whole search range into store even though you only search the first column and return data from one other, whereas, INDEX/MATCH only refers to two columns.

So this:

Formula:

Please Login or Register  to view this content.

could be this:

Formula:

Please Login or Register  to view this content.

Could be other things but obviously I'm not going to be able to test the benefits of these suggestions.

If there are other sheets, there may be other issues that I cannot comment upon.

Typically, in my experience, slow response or apparent hanging up can be caused by:

1. Full column references and/or
2. Complex Array Functions ... multiple columns
3. Heavy use of Conditional Formatting
4. "Fractured" Conditional Formatting caused by inserting and deleting rows and/or columns
5. Formulae designed to return blank if a key column, say column A, is blank copied way down the sheet
6. Bloating, where Excel thinks there are more rows and columns of data than there actually are

Can't necessarily see examples but then this is a cut down sample that doesn't exhibit the problem

You're welcome.

The shorter concatenate formula is quite straightforward. Apart from the first value, it checks each value to see if it is blank. If it's not, it concatenates the separator(s) and the value, otherwise it concatenates a null value.

Glad you found the CF issue. I once created an application that was designed to insert and delete blocks of rows and columns. In testing it was fine, but, when used in anger it became deadly slow. In the end, I resorted to using a Worksheet Open Event handler to clear all the CF and re-instate it.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.