Excel Sheet Super Slow

[leilanip]

Help! I have a work spreadsheet which uses a lot of formulas to auto-populate payroll categories based on job codes. The problem I am having is that for the last two weeks the sheet if running super slow. I have tried to go back to a previous sheet, same issue. I have removed all volatile formulas, small improvement but not much. I can't make any of the formulas static as I use them all. There are roughly 2200 formulas. They are mostly nested if formulas.

Is my sheet slow due to the number of formulas? What can I do to speed it up? Could it be corrupt and if so how do I fix it? Thank you in advance.

 

[Alex Blakenburg]

It looks to me like your conditional formatting is just killing your spreadsheet.
This is made even worse by the fact that some of the formatting is being applied to all 1m rows.

You can get the spreadsheet to hang just by going into conditional formatting and changing the drop down box from Current Selection to This Workbook.

 

[leilanip]

Now it just keeps crashing but I can use it on onedrive online and it's ok.

 

[leilanip]

It looks to me like your conditional formatting is just killing your spreadsheet.
This is made even worse by the fact that some of the formatting is being applied to all 1m rows.

You can get the spreadsheet to hang just by going into conditional formatting and changing the drop down box from Current Selection to This Workbook.

Ok I'll see what I can do to remove conditional formatting. Thank you. I hadn't even considered that.

 

[leilanip]

Also try turning off auto calculation. Input all your data then press F9 rather than calculating constantly

Thank you for your help

 

[Alex Blakenburg]

Ok I'll see what I can do to remove conditional formatting. Thank you. I hadn't even considered that.

Even getting rid of your conditional formatting proved challenging with the sheet constantly hanging.

If you want to test it out the below will take 2.5-3mins to delete all the conditional formatting.
You will find that your spreadsheet will immediately be much more responsive.

Sub RemoveCondFormat()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ActiveSheet.Cells.FormatConditions.Delete
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub