Forum Discussion
Formula speed / Laptop specification
Reasons for slow computation and poor performance
Volatile functions slow your performance
The term volatile, simply put, means that these formulas are always recalculated.
Conditional formatting makes your file slow
Many complex formulas degrade your performance
Incorrect or too large references lead to a loss of speed
Large workbooks result in slower worksheets
Increase the speed and improve the performance
Save your file as a binary workbook for better performance
Use pivot tables
Use conditional formatting carefully for better performance
Avoid volatile functions for more performance
Customize the reference areas and use structured references and names
Improve your formula structure for more speed
Avoid matrix formulas in your files
Replace formulas with fixed values
Avoid links between workbooks
Sort your source data for faster calculations
Use VBA for better performance
Switch to manual calculation
Can all workbooks be made faster?
Check links in your file
Remove incorrect or unnecessary formulas
Delete empty cells from memory
Check if you are using volatile functions
Check the references in your formulas
Check your formulas and conditional formatting
Finally, I can only recommend you, if none of the above steps help then you should read this article.
These go into detail on the topic and offer alternative paths.
The articles are sometimes a bit very technical and best only for advanced users but still very helpful.
Excel performance: Improving calculation performance
Excel performance: Tips for optimizing performance obstructions
Hardware: I recommend an SSD hard drive and at least 16GB RAM (when it comes to that many data sets).
At the same time check whether all processors are activated.
Instructions for the check (example with processor i7):
Press the key combination [Ctrl] + [Alt] + [Del] and select the "Task Manager". Switch to the "Performance" tab and click on "CPU" on the left. In the right area of the window you can now read the number of cores used by Windows under. Your quad-core processor should read "Cores: 4" there.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here
Thank you NikolinoDE there is a lot to go through there but I will and I will feed back to you.
I am absolutely blown away by how helpful everyone here has been. I really didn't expect so many replies. Lots for me to review and try out and investigate.
I will certainly share the hardware spec with my manager to see what she can do for me. In the meantime I will make a start on reviewing my master workbooks to see where I can follow all the tips and cut out any unnecessary tasks for excel to do. I've promised to share with my colleagues to as there are other in my team having similar problems.
I will go through your list item by item and make a note of any improvements. Though it may take me a week or two because i'll have to do it all in between work but there will be opportunities as I am going along.
Thank you so much for taking the time to share. I've struggled for two years and there have been tears and tantrums along the way so I am excited to get a bit of control back.
Allison
By the way, on which edition of Excel you are?
Hi SergeiBaklan
So looking in About MS Excel it says:
MS Excel 2013 (15.0.5311.1000) MSO (15.0.5275.100) 32-bit
No idea what the bits in brackets mean but this is the version I use regularly through Citrix.
I also have access to:
MS Excel 2010
version 14.0.6023.1000 (64-bit)
I was given access to this version to help with working with the larger spreadsheets but it doesn't and I can't have multiple windows open so I tend to use 2013 most of the time.
I hope this answers your question.
Allison
