10 Ways to Speed up and Optimize Excel
Ever wondered you might also need to optimize Excel someday? Perhaps not, until you stepped into the advanced world of Excel.
Is your Excel workbook taking ages to open up?
And is it taking more than usual time to get things done on Excel?
Microsoft Excel contains thousands of formulae and features to organize and process your data and calculate the results.
However, using more formulae or features to perform a task can slow down the processing speed of MS Excel. The more the processes run in the background, the more time Excel will take to perform a task.
Since huge data is processed in Excel, it needs to be optimized for faster, smooth and effective operation.
So, if your Excel workbook feels too heavy, read along to learn about how to optimize Excel and gear up its operation.
Here are 10 Ways to Speed up and Optimize Excel
1. Reduce the number of worksheets.
We know there is no such limit on creating new worksheets in a workbook. One can create ‘n’ number of worksheets depending upon his/her computer’s processing capacity.
Highly voluminous workbooks containing numerous worksheets may increase the loading time of Excel. Hence, try to incorporate multiple data/table vertically in each worksheet rather than spreading it across worksheets.
2. Reduce or simplify formatting
Excel offers loads of ways to format your data. Be it changing font color, font style, background color, borders and much more.
Use minimal formatting to meet the purpose and avoid excessive decorative formatting.
3. Avoid unnecessary Conditional formatting
Conditional formatting is invariably a very cool feature.
It applies auto-formatting to your data based on certain specified criteria.
Well, although it reduces your efforts but it increases the work to be done by Excel.
Excel has to look for data matching your criteria and apply the desired formatting each time Excel is updated with new data.
So, use it only where it is actually needed.
4. Convert unused formulas to values
At times, they are certain formulae the input for which does not change usually. In that case, the resulting output of the formula can be converted into a static text or value. It would be smart of you to relieve Excel from unnecessary calculations.
5. Replace formulas to VBA Macros
It should not be exaggerating to say Macros is indeed a gift to mankind. Really, its like a mini robot doing repetitive things for you in MS Excel.
So why not let these robot be performing calculations for you rather than you putting in complex formula for all calculations.
6. Switch Excel to manual calculation
What happens when Excel Calculation Options is set to automatic?
All the formula in your workbook would update simultaneously should there be any change in data.
Change it to manual to update relevant formula only. This puts too much of processing load on your system if there are much complex formulae.
Even more when they belong to categories like LOOKUP formulas, frequently updating ones like RAND(), NOW() etc. or ARRAY functions like TRANSPOSE, CHOOSE etc.
7. Remove any links to other Excel files
We often link a cell to another Excel workbook. Excel would look for values to update each time you open Excel or refresh connections. If you don’t want those values to be changed further, break the links.
8. Minimize links between worksheets
Not only external links, but too much of internal linking can also slow down our processing speed in Excel. Hence, look for links containing static values. Replace them by copying and pasting the data.
Creating complex financial models in MS Excel can often lead to slowing down Excel. Apply these tips and let us know in the comments below if it has helped you.
For more discussion on MS Excel and Financial Modelling, join this Quora space: Financial Modelling & MS Excel