MS Excel is world widely application used for managing crucial data and carry out complex calculations. This is highly useful but as larger an excel spreadsheet gets, the calculation becomes slower. This is one of the frustrating issue most often faced by the users.
Well, it might be neglected when the data is less but it becomes more profound as more data or calculations are added to the workbooks. Depending on the formulas, workbook size and the PC, the calculation takes more time and spreadsheet becomes slower.
However, this is not something that can’t be fixed. Here in this article, we are going to describe best tips to optimize the performance of the Excel spreadsheets even in the large data.
Here’s the deal:
1. Prevent Volatile Functions.
2. Use Helper Columns.
3. Disable all Excel add-ins
4. Use Conditional Formatting with Caution.
5. Use Excel Tables and Named Ranges.
6. Convert Unused Formulas to Values.
7. Keep All Referenced Data in One Sheet.
8. Avoid Using Entire Row/Column in References.
9. Use Manual Calculation Mode.
10. Use Faster Formula Techniques.
11. Divide the workbook into Several Files.
Detailed Tips to Optimize Slow Excel Spreadsheets
1. Prevent Volatile Formulas
The class of formulas in Excel is called Volatile formulas. These formulas are recalculated every time when there is the change in the workbook. (For instance: NOW, TODAY, INDIRECT, RAND, OFFSET etc.) So when the worksheet has a lot of volatile formulas, and any modification was done, the entire formulas are re-calculated. And this is the reason worksheet becomes slow.
So to get rid of this issue it is recommended not to utilize the volatile formulas or it is better to use a table.
YOU MAY ALSO LIKE:
2. Utilize Helper Columns
Helper columns are the most underestimated design constructs in Excel. Generally, it is found that user won’t make use of the helper columns.
AVOID DOING THIS.
Utilizing the ‘Helper Columns’ help you avoid array formulas.
Well, this doesn’t means won’t utilize array formulas. These are also very useful in some cases. However when you attempt to do all with one long formula, this impact the performance of the Excel workbook, there are some array formulas that are worth, but if it requires utilizing in many places then it is considered to make use of the helper columns to optimize Excel File
3. Disable all Excel add-ins
Generally, the add-ins are useful, but it is also found it slow down Excel and also it starts crashing, freezing. So, it is good to disable the add-ins to optimize Excel File.
Here follow the ways to do so:
- First, go to File > Options > Add-Ins.
- Check the enabled add-ins and by selecting, disable them like “Com Add-Ins”.
- Next, click on “Go”.
- Un-check all add-ins that are not unnecessary. If the add-ins are required you can enable them again
4. Employ Conditional Formatting with Caution
Well, the Excel Conditional Formatting is volatile. This might be not known by many users, and the users may not notice the difference with small data sets and can result in slow excel spreadsheet if used on large data sets or utilized multiples times. But apart from that, it is easy to even make the blended data looks beautiful, I personally like this. Instead of doing the comparison yourself, you can simply look at a cell’s color or icon and easily notice how it compared with others.
It is also recommended to use it carefully to optimize Excel file.
5. Utilize Named Ranges and Excel Tables
Named Ranges and Excel table are two most amazing features that hold data and makes referencing simplified. This might takes some time while using but as you start using, it becomes easy and fast.
While creating data-driven dashboards, it is a good idea for converting data into an Excel Table. This is also beneficial in making formulas more understandable, makes the workbooks faster and even optimize Excel file.
6. Convert Unused Formulas to Static Values
It is said, “If you don’t need it, don’t keep it”. Plenty of formulas results in a slow excel workbook. So if you are having formulas that are not needed, it is better to convert them into a static value just by pasting as values and optimize Excel File.
7. Maintain Entire Referenced Data in One Sheet
Well, this is not possible always, but if you are able to do this, gradually your excel sheet become faster.
The logic behind this is simple; formulas in worksheet don’t require going far for the data when it is placed next in the same sheet.
8. Avoid Using the Entire Row/Column as Reference
This is mentioned in the list because it is seen that many users have the habit of using the entire row/column references in formulas. This is really a bad habit and needed to be avoided.
We all think that row/column just has few cells with data but Excel doesn’t think like this. As we reference an entire row/column, Excel just as a good servant, check it anyways and this takes more time for calculating and as a result spreadsheet become slow.
9. Use Manual Calculation Mode
This point is suggested in many forums and blogs and I have also mentioned it. Manual calculation provides the flexibility to let Excel know when to calculate, rather than excel take its own decisions. This will not speed up Excel workbook, but if your spreadsheet is slow, this will definitely save time by not making Excel recalculate again and again. To utilize manual calculation, follow the step
- Go to Formula Tab > Calculation Options > Manual (press F9 key to recalculate)
10. Utilize Faster Formulas Techniques
Excel provides the lot of formulas and formula-combos for doing the same thing. So it is best to recognize and utilize the faster ones.
- Utilize IFERROR in place of IF and ISERROR combo (if you are using Excel 2003 or earlier, which does not have IFERROR).
- Utilize — (double negatives) for converting TRUE’s and FALSE’s to 1’s and 0’s (in place of multiplying it by 1 or adding 0 to it). The speed improvement is noticed in large data sets.
11. Divide the workbook into several files
Separation of the workbook into several files is very effective but also complicated in some cases. For example: utilize one file for processing the raw input data, within the second file, all main calculations are done and the third file will display results. This seems quite complicated but fixes the issue of slow excel spreadsheets.
Well, these are the best 11 tips that will boost the performance of the Excel spreadsheet and also saves your time. But that’s not all, below follow Bonus Tips to fix issues in the Excel file and increase the speed up Excel file.
Bonus Tips to Speed Up Excel File:
#1 Fix Excel Files Are Slow to Save/Open:
In some cases, Microsoft Excel file take the long time to open. When Excel is launched this starts normally, but the issue arises when you try to open the file directly prior running Excel and the file takes some time to start.
Sometimes, this issue is caused by an OS service known as “Superfetch” being disabled, this is dependable for caching data on the RAM so that, as the name specifies, it can be fetched super-fast when necessary.
To fix this issue try to change the Superfetch settings but if, it won’t help you to fix the problem then modify the registry for Excel file.
#2 Try to Reduce Excel File Size:
It is found that bigger Excel file or Excel file that is having large data, consequently becomes slower and also the file start crashing. So, it is recommended to reduce the file size to prevent the issue and optimize Excel file.
To do so execute some instructions:
Check the file type, remove the unnecessary formatting, also try to clean up formulas by setting the excel option in a manual calculation, prevent volatile formula and check the unused cells and delete them.
However, if in case your excel file gets corrupted, damaged or start showing errors than in this cases make use of the MS Excel Repair tool, this is the best tool to fix any type of issues and also able to recovers entire excel data including the charts, worksheet properties cell comments, and other important data easily.
Generally, Excel takes a long time in calculating large Excel models. The reasons are different for each workbook and as a result Excel spreadsheet becomes slow.
So, to fix this issue we have described best 11 tips along with bonus tips, hope this work well to speed up Excel spreadsheet. These tips will magnify your future work and also improve your skills from the bottom to up.
Start implementing them Right Now….