Method 1 – Avoid Using Volatile Functions
Volatile functions can be defined as a function in which Excel will recalculate every time there is a change in values.
In large files, if there are volatile functions, it will eventually reduces Excel’s performance.
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.
Method 2 – Switch to Manual Calculation
Steps
- Go to the Formulas tab in the ribbon.
- From the Calculation group, select the Calculation Options drop-down option.
- Select Manual from the drop-down option.
- When you need to update the formulae, return to the Formulas tab in the ribbon.
- From the Calculation group, select Calculate Now option.
Method 3 – Avoid Using Entire Row or Column References
Some users have a habit of including an entire row or column in references. This will reduce Excel’s performance in large files. Instead, use cells in references.
Method 4 – Remove Unnecessary Conditional Formatting
Conditional formatting is one of the most used features in Excel but it is similar to volatile functions in that any change in conditional formatting values will cause Excel to recalculate the entire worksheet.
Method 5 – Minimize Using Array Formulas
The array formula takes a lot of data into consideration when calculating its solution.
If you must use an array formula, utilize a helper column to control the array formula properly and reduce the amount of data involved in the calculation.
Read More: How to Make Excel Run Faster with Lots of Data
Method 6 – Utilize Helper Columns
Helper columns as so called because they help to avoid array formulas in Excel.
Method 7 – Utilizing Excel Tables and Named Ranges
Tables and named ranges are two of the most important features when you want to simplify your data and references.
Setting a name for any range of cells allows the user to type that name to utilize the range in the formula and makes the formulas more comprehensive. These two features are useful when you work in a large Excel file and it can actually improve Excel performance.
Read More: How to Make Excel Calculate Faster
Method 8 – Use Formulas That Work Fast
- Use the IFERROR function instead of the combination of IF and ISERROR. IFERROR is not available in Excel 2003 or earlier.
- Use the combination of INDEX and MATCH functions instead of the VLOOKUP function. The VLOOKUP function basically decreases Excel performance in large files.
- To convert True and False into 1’s and 0’s, we need to use –(double negative).
In small Excel files there will not be a significant performance increase, but in large files, you will see some noticeable improvement in your Excel performance.
Method 9 – Disable Excel Add-ins
Excel add-ins are really helpful for some advanced calculations. But when you work on a large Excel file, they can decrease Excel’s performance.
Steps
- Go to the File tab in the ribbon.
- Select the More command.
- Select Options.
- The Excel Options dialog box will appear.
- Select the Add-ins option.
- Select the Go option.
- From the Add-ins available section, uncheck all the Add-ins.
- Click on OK.
Read More: How to Open Large Excel Files Without Crashing
Method 10 – Avoid Creating Links Between Workbooks
When you apply links between workbooks, Excel can become slow. Try to use simple call references that work in a closed workbook instead. 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 create a similar problem in the future.
Read More: How to Make Excel Open Faster
Method 11 – Minimize Links Between Worksheets
As in the previous solution, minimizing links between worksheets can improve performance. If you create links between worksheets, it will cause Excel to recalculate every time you change a value in any one of the worksheets.
Method 12 – Convert Unwanted Formulas to Values
Sometimes in a large Excel file, there will be formulas that are no longer in use or not that important. Convert the formulas into static values to reduce the number of calculations and improve performance.
Method 13 – Disable Hardware Graphics Acceleration
Another possible reason Excel slows down when using large files is the hardware graphics acceleration.
Steps
- Go to the File tab in the ribbon.
- Select the More command.
- Select Options.
- The Excel Options dialog box will appear.
- Select the Advanced option.
- Scroll down to find the Display section.
- Uncheck the Disable Hardware Graphics Acceleration.
- Click on OK.
Method 14 – Modifying Advanced System Settings
In Windows 10, there is some unnecessary animations that can cause problems in Excel large files and eventually decreases Excel’s performance. To turn these settings off, you need to follow the following steps carefully.
Steps
- Right-click on my computer icon and select Properties.
- A System dialog box will pop up.
- Select the Advanced System Settings option.
- The System Properties dialog box will appear.
- Select the Advanced option at the top.
- In the performance section, select Settings.
- In the Performance Options dialog box, select the Custom option.
- Uncheck all the options except the Smooth edge of screen fonts.
- All of these options are unnecessary and can slow down large Excel files and gradually decrease Excel performance.
- Click on OK.
Read More: How to Make Excel Faster on Windows 10
Method 15 – Split Workbook into Several Workbooks
Divide large Excel files into several files to improve performance with large files. However, this method can accidentally delete data and has other disadvantages related to maintaining numerous files.
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!
