Forum Discussion
Can drop down lists slow down Excel?
Hi,
I am investigating why a Excel sheet is extremly slow. Checked and optimized formulas and conditional formating. Made a copy of the sheet with formulas. Worked OK, but as soon as I added drop down lists, it started to be slow.
And its's faster in Excel web, than in Excel desktop.
Any suggestions?
Best Regards
Geir.
The source of the validation lists might be the issue. Are you using dynamic items to supply the validation source?
Here's what Microsoft says:
Conditional formats and data validation
Conditional formats and data validation are great, but using a lot of them can significantly slow down calculation. If the cell is displayed, every conditional format formula is evaluated at each calculation and when the display of the cell that contains the conditional format is refreshed. The Excel object model has a Worksheet.EnableFormatConditionsCalculation property so that you can enable or disable the calculation of conditional formats.
Excel performance - Tips for optimizing performance obstructions | Microsoft Learn
The above article pre-dates dynamic arrays but has some good information.
The source of the validation lists might be the issue. Are you using dynamic items to supply the validation source?
Here's what Microsoft says:
Conditional formats and data validation
Conditional formats and data validation are great, but using a lot of them can significantly slow down calculation. If the cell is displayed, every conditional format formula is evaluated at each calculation and when the display of the cell that contains the conditional format is refreshed. The Excel object model has a Worksheet.EnableFormatConditionsCalculation property so that you can enable or disable the calculation of conditional formats.
Excel performance - Tips for optimizing performance obstructions | Microsoft Learn
The above article pre-dates dynamic arrays but has some good information.
- Thank you, good article. I added Formulas -> Names to my check list. It was long here, and full of reference Errors (#REF)
- You're welcome!
