When we work with a large file, we find it difficult in terms of Excel performance. It is really painful to have Excel with bad performance. It decreases the work accuracy and at the same time, it irritates the user while dealing with large Excel files. This article will show you how to improve Excel performance with large files. I hope you find this article very informative and solve lots of issues by going through this article.
How to Improve Excel Performance with Large Files: 15 Effective Ways
To improve Excel performance with large files, we have found 15 ways through which you can solve your issues. When you work with large files, you may face several issues that can be solved by utilizing various approaches. All of these methods are applicable and helpful for various issues.
1. Avoid Using Volatile Functions
The Volatile function can be defined as a function in which Excel will recalculate every time there is a change in values. So, when there is a need for extra processing time to recalculate. In large files, if there are some volatile functions, it will eventually decrease Excel performance and also slow down the Excel file entirely. In Excel, there are 8 volatile functions.
- NOW()
- TODAY()
- RAND()
- RANDBETWEEN()
- INDIRECT()
- OFFSET()
- CELL()
- INFO()
If you use these functions several times in your large Excel file, it will slow down your Excel file and eventually decrease your Excel performance.
2. Switch to Manual Calculation
Another important solution to improve Excel performance with large files is to use manual calculation. The automatic calculation tries to recalculate every time there is a change in value. For large files, it creates a bad environment to work in Excel. Moreover, it slows down Excel’s performance to some extent. Follow the following steps to use manual calculation
Steps
- First, go to the Formulas tab in the ribbon.
- Then, from the Calculation group, select the Calculation Options drop-down option.
- After that, select Manual from the drop-down option.
- It will stop Excel from recalculating every time after changing any values.
- Then, again go to the Formulas tab in the ribbon.
- From the Calculation group, select Calculate Now option.
- It will basically calculate when you need to calculate.
- This solution is the best for improving Excel performance with large files.
3. Avoid Using Entire Row or Column References
Some of the users have a habit of using an entire row/column in references. Rows/columns are made of several cells. You need to use cells in reference. Otherwise, Excel looks at entire rows and columns for calculating and it takes a few more times to give the final solution. As a user of Excel, you need to avoid this habit. For large files, it takes far more time to recalculate than you think. Eventually, it slows down Excel and decreases the Excel performance.
4. Remove Unnecessary Conditional Formatting
Conditional formatting is one of the most used features in Excel. Everyone loves to use conditional formatting. However, it is unknown to everyone that conditional formatting is volatile. You can’t find any problem in a small Excel file. But when you utilize this conditional formatting in large files, you will find Excel performance decreases with time. This happens because of the volatile characteristics of conditional formatting. For any change in value, Excel will recalculate the whole worksheet. It takes far more time than expected in the large file. That’s why you need to use conditional formatting with proper caution to eliminate any unwanted problems.
5. Minimize Using Array Formulas
In large Excel files, we often prefer to use the array formula to solve any problem. The array formula basically takes a lot of data. Then analyze it and after that, it provides us with the final solution. So, eventually, it takes a lot of time to give the final solution. For large files, this array formula gradually decreases Excel performance. That is why we need to minimize the use of array formulas. Otherwise, it is quite certain to face that issue. If using an array formula is a must, then you need to utilize a helper column. It helps to control the array formula properly.
Read More: How to Make Excel Run Faster with Lots of DataÂ
6. Utilize Helper Columns
Helper columns are unpopular with most Excel users. Some of them are not aware of helper columns properly. Mainly helper columns help to avoid array formulas in Excel. Please don’t think the array formula is bad. But what we are trying to say is that an array formula utilizes lots of references in a single formula box. After that, it needs to analyze them properly before giving any final results. So, eventually, our whole Excel performance decreases with that. That is why you need to utilize helper columns more.
7. Utilizing Excel Tables and Named Ranges
The Excel tables and named ranges are the two most important features when you want to simplify your data and references. When you set a name for any range of cells, it is quite easy to type that name to utilize the range in the formula. It also makes the formulas more comprehensive. When you work with a data-driven dashboard, it is a must to convert your data into an Excel table. These two things are useful when you work in a large Excel file, it actually improve Excel performance and make Excel faster to use.
Read More: How to Make Excel Calculate Faster
8. Use Formulas That Work Fast
In Microsoft Excel, we have too many formulas to solve any problems. Some of the formulas take more time compared to others. You need to be wise enough to understand which formula can give us faster results. You may think that is why it is important, It is essential when you use a large Excel file. In large files, it is really essential to use faster formulas instead of some basic formulas. Otherwise, Excel performance will decrease gradually.
- You need to use the IFERROR function instead of using the combination of IF and ISERROR If you are a user of Excel 2003 or earlier, you can’t find IFERROR. So, use the latest version of Excel and utilize the IFERROR function.
- Then, you need to use the combination of INDEX and MATCH functions instead of the VLOOKUP function. The VLOOKUP function basically decreases Excel performance in large files. To avoid this problem, you need to look at this solution.
- To convert True and False into 1’s and 0’s, we need to use –(double negative).
In small Excel files, you don’t get the advantage of these functions. But when you use large files, you will see some noticeable improvement in your Excel performance.
9. Disable Excel Add-ins
Generally, Excel add-ins are really helpful for some advanced calculations. But when you work on a large Excel file, you will find discomfort while using it. In some cases, Excel add-ins create some internal problem through which the Excel performance decreases over time, To disable Excel add-ins, you need to follow the steps properly.
Steps
- First, go to the File tab in the ribbon.
- From there, select the More command.
- Then, select Options.
- The Excel Options dialog box will appear.
- Then, select the Add-ins option.
- After that, select the Go option.
- Then, from the Add-ins available section, uncheck all the Add-ins.
- Finally, click on OK.
Read More: How to Open Large Excel Files Without Crashing
10. Avoid Creating Links Between Workbooks
To improve Excel performance with large files, you need to avoid links between workbooks. Because when you apply links between workbooks, Excel can become slow, broken, and not easily fixed. Try to use simple call references that work on a closed workbook. By doing this, you can avoid recalculating all the workbooks. If you need to include links between workbooks, you need to keep the workbooks open. Otherwise, it will face a similar problem in the future.
Read More: How to Make Excel Open Faster
11. Minimize Links Between Worksheets
Just like the previous solution, you need to minimize links between worksheets. If you create links between worksheets, it will recalculate every time you change a value in the present worksheet. This recalculation takes lots of time and in the meantime, Excel decreases its performance level. When you do this in a large Excel file, this recalculation takes even more time and in some cases, the Excel file will be broken. So, it is better to minimize links between worksheets.
12. Convert Unwanted Formulas to Values
Sometimes in a large Excel file, you have some formulas that are unused and not that important. It is better to convert them into static values. Because having too many formulas in a large file can hamper your Excel performance. So, before experiencing bad performance, you need to convert those unwanted formulas into values. It saves time and also saves Excel from doing too much recalculation at a time.
13. Disable Hardware Graphics Acceleration
Another possible reason to slow down Excel performance with large files is the hardware graphics acceleration. By name, you may think this can improve Excel performance. But ironically, it referees some unnecessary animation happening while using Excel. It eventually slows down Excel’s performance to some extent. To disable hardware graphics acceleration, you need to follow the steps carefully.
Steps
- First, go to the File tab in the ribbon.
- From there, select the More command.
- Then, select Options.
- The Excel Options dialog box will appear.
- From there, select the Advanced option.
- Then, scroll down to find the Display section.
- After that, uncheck the Disable Hardware Graphics Acceleration.
- Finally, click on OK.
14. Modifying Advanced System Settings
In Windows 10, there is some unnecessary animation that can cause problems in Excel large files. It eventually decreases Excel’s performance. To turn these settings off, you need to follow the following steps carefully.
Steps
- At first, right-click on my computer icon and select Properties.
- A System dialog box will pop up.
- Then, select the Advanced System Settings option.
- After that, the System Properties dialog box will appear.
- Select the Advanced option at the top.
- Then, in the performance section, select Settings.
- After that, in the Performance Options dialog box, select the Custom option.
- Then, uncheck all the options except the Smooth edge of screen fonts.
- All of these options are unnecessary. It can slow down your large Excel files and gradually decrease Excel performance.
- Finally, click on OK.
Read More: How to Make Excel Faster on Windows 10
15. Split Workbook into Several Workbooks
Finally, you can divide your large Excel files into several files. It can improve your Excel performance with large files immensely. But to maintain several files is also very tedious. Some data can be deleted while doing calculations. It has some disadvantages to use. If you want to improve your Excel performance this can be a good option. Otherwise, I won’t recommend you.
Conclusion
We have shown 15 effective solutions to improve Excel performance with large files. All of these methods are equally effective in improving Excel performance with large files. All of these methods are fairly easy to use. I hope you find this article really informative and solves lots of problems regarding this issue. If you have any questions, feel free to ask in the comment box.
Related Articles
- How to Speed up Excel Calculating 4 Processors
- How to Stop Calculating Threads in Excel
- How to Stop Calculating 8 Threads in Excel
- How to Make VBA Code Run Faster
<< Go Back to How to Speed Up Excel | Excel Files | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
