Microsoft Excel is an excellent productivity tool and most of us use it in day to day work. One of the major problems that we all face is that Excel files turn very slow when many equations are involved and sometimes it takes more than a minute to open a file. Sometimes it stops responding if you try to open that heavy Excel file. Well, there are certain best practices which we can follow to overcome this problem. In this article, we will find out those best practices and see How to speed up Excel file load time and processing.
Sometimes if you have too many files open at a time RAM usage becomes high. When RAM usage is high, processing becomes very slow. In that case, if you restart Excel or I would suggest if you restart your computer your Excel file might feel better to work on.
Use Excel in safe mode
Some corrupt excel ad on’s can slow down excel file processing. In this scenario, we can open the excel file in safe mode. In safe more, if the excel file loads fast, then we can be sure that some add on’s are making it slow. After then we can disable all add on’s and enable add on’s one by one to diagnose which add on is creating the problem. To open excel in safe mode you have to follow below process
“Windows Key+R“->Type Excel/safe and press Enter
Excel will load in safe mode and it will show in parenthesis in the file name.
Disable unnecessary Add In’s
Unnecessary add on’s are a big burden and should be disabled if you want to speed up Excel. Please make sure that the add on’s that you are disabling does not have any impact on your current file.
You can follow the below process to disable add in’s.
Developer Menu -> Excel Add In’s-> Uncheck the Add-In that you want to disable
Save Files as. Xlsb format
Reducing Excel file size has a big impact on speed. There is a different way to reduce Excel file size but the best option is to save the file as the xlsb format. It reduces almost 90% of file size. Another good thing about the .xlsb format is that all your formula will still work even if you convert files to the .xlsb format
To save files as the .xlsb format you have to follow below steps
File-> Save As-> Save as .xlsb
Use Manual Calculation
When you open an Excel file, the system automatically calculates all formulas. Due to this the loading time increases and Excel become very slow. To solve that you will have to set the Excel file to use Manual Calculation option. If that option is set system won’t calculate formula when you open the file. To set manual Calculation you have to follow below steps.
Formula -> Calculation Option -> Manual
Reduce macro usage
Macros slow down Excel files. Unless it is required, it’s better to avoid too many macros. If you can’t get rid of too many macros, you can try to combine macros to speed up the file.
To delete Excel macros you can follow the below steps.
Developer -> Macros -> Select the Macro -Delete
No external reference
We need to make sure that the Excel file that you are working on does not have any external references, if not possible try to use less external references. In the worst case if you need to use external references, make sure that you do not move or deletes those external references file. If you do so you will get error and Excel file will be very slow.
Avoid conditional Formatting
Conditional Formatting rules are a big reason for Excel files getting very slow. Avoid conditional Formatting rules as far as possible if not delete formatting rules as and when required to speed up.
You need to follow the below steps to disable conditional Formatting rules.
Home -> Conditional Formatting -> Manage Rules -> Select the rule -> Delete
Don’t use Excel over the cloud
Nowadays the cloud is in a boom. Many people prefer to work on Excel in cloud mode. For example, OneDrive and Google drive allows you to create and work on Excel files in the cloud it selves. This process slows down the excel file. Best solution is to download the Excel file, open it in your computer, save it and upload again to the cloud when work is done
You might have noticed that if you open an Excel file directly by double-clicking the file, it takes more time to load. But if you open Excel application first and then go to open and open the Excel file, it loads much faster. This happens due to the Super fetch function. You will have to enable Super fetch so that even if you double click Excel file will load faster
Don’t use array formulas
I know we can’t ignore formulas in Excel. But try to ignore array formulas as those array formulas have to do so much of calculations and that slows down the Excel. If possible use some other formula type which is fast to calculate.
Sometimes Excel gets corrupt due to many reasons and it slows down the application. So when you open files it takes forever to load. The solution is to repair the Excel application or reinstall the Excel application so that you can get rid of those corrupted application.
Don’t use Un-Trusted plugins
This is one of the major reason for our Excel files becomes buggy. We install so many add in’s from the internet without knowing that those may contain a virus and can affect or slow down you our Excel application
Use other spreadsheet application
Well, Microsoft office is not known for fast processing. It is always buggy and things become worse after a few months of usage. You can try some of the other leading office suites which are free and also very fast. Please read one of my article on ” Microsoft office alternatives” where I mentioned about some of the free office suite software which is almost like Microsoft office.
If you follow my above recommendation, it might speed up your Excel file a bit. But sometimes following all recommendation is not possible as it may affect the file. For example, I said not to use the array function. But your work needs that array function, so you can’t ignore that. Right? So it is my suggestion is to try other lightweight Excel software which comes handy to solve this kind of slowness problem with Microsoft Excel.
That?s all we have in this article. Hope you liked the article and found some useful information. If you have any queries please do write in the comment section and I will be happy to answer. If you find this article helpful please rate us five stars.