Excel is one of the most acceptable applications for data calculation. It is capable of processing 6.6 million formulas per second. Despite this, sometimes we see that Excel is very slow. There are lots of reasons for that. In this article, we will discuss those reasons and their solution to make Excel open faster.
How to Make Excel Open Faster: 16 Useful Tricks: 16 Useful Tricks
1. Avoid Volatile Formula
Some functions of Excel work in a volatile manner. Volatile functions are those that recalculate values with the change of worksheet. This frequent, automatic calculation hampers the performance of Excel. NOW, TODAY, RAND, OFFSET, INDIRECT, etc. are known as volatile functions. To make Excel faster we should avoid the use of those functions.
Read More: How to Make Excel Calculate Faster
2. Utilize Helper Columns
Utilizing the Helper Column helps to increase the performance of the Excel file. The helper column is an extra column added to the dataset that simplifies a complex formula for calculation or analysis. We can also avoid the array formula using the helper column.
3. Bypass Array Formulas
The Array Formula can perform multiple calculations at a single time. It uses lots of cell references. For that, it decreases the performance of Excel. We must try to avoid the use of the array formula to make Excel faster.
Read More: How to Make Excel Faster on Windows 10
4. Reduce Use of Conditional Formatting
Conditional Formatting is one of the most popular features of Excel. It performs lots of operations without any use of any complex formula. Because the conditional formatting performs lots of calculations without knowledge. As a result, this reduces the performance of Excel. Also, need to mention that conditional formatting is volatile. It changes results from time to time.
5. Use Named Ranges and Excel Tables in Formulas
Excel Tables and Named Ranges are one of the finest tools of Excel. They are dynamic. No need to edit them manually all the time. To form a table or named range is time-consuming work. But those are very useful. They simplify the formula by avoiding the use of cell range.
6. Extract Values from Formulas
Sometimes we see that some formulas are unused or static. Those do not have any changes. For those cases, we will extract values from the formula and place them on the same cell.
7. Separate Referenced Data in Another Single Sheet
When we apply any formula, we use cell references. There is a simple trick. If we keep all the cell references in another sheet, that will increase the speed of the Excel sheet.
8. Stop Using Whole Row/Column in References
Sometimes we use the whole Column or row as a cell reference instead of certain cells. This makes the formula complicated. We should stop using the whole row/column n formula.
9. Enable Manual Calculation Mode
Excel has different types of calculation modes. When we use any complex formula consisting of volatile functions, we should enable manual calculation. This manual calculation is not like we need to calculate manually. This will ask when the formula will need to be recalculated. When we enable Manual Calculation, the change will not reflect instantly.
- To enable manual calculation, we will enter the Formulas tab.
- Then, choose the Manual option from the Calculation Options.
10. Apply Faster Formulas
We can use faster functions or formulas to make faster the Excel file. Like,
- Using the MAX function instead of the IF function. MAX function is 40% faster than the IF function.
- A combination of INDEX and MATCH functions despite the VLOOKUP function. VLOOKUP performs a complex operation. As a result, slows down the Excel file.
- Use double negative sign to transform TRUE and FALSE to 1 and 0. This sped up the Excel file.
11. Utilize Maximum Processors Available for Excel
The computer at a time doesn’t perform only one task. It may process other tasks at the same time. We need to ensure that the maximum number of processors is dedicated to Excel. Follow the below steps.
- Go to File >> Options.
- Excel Options window appears.
- Select Advanced options.
- Now, check Use all processors on this computer option.
- Finally, press OK.
12. Disable Excel Add-ins
Add-ins are additional functionality of Excel. When we need to customize the tool use Add-ins. Those add-ins can slow down Excel files. When the purpose of a particular add-in is fulfilled we should disable these add-ins.
- Go to File >> Options.
- Now, choose Add-ins from the Excel Options window.
- Then, choose the add-ins.
- Go to Manage Press the Down arrow.
- Choose Disabled Items from the list.
- Then press Go and after that, press OK.
13. Use VBA Macros Instead of Formulas
We can perform most of the works of Excel formulas or other tools using the VBA macros. VBA macros do not make Excel files slower. When we run a VBA that runs the code at other times it remains ideal. To apply a VBA code is an excellent option to make Excel faster.
Read More: How to Make VBA Code Run Faster
14. Remove Unused Sheets and Ranges
Usually, when we work in Excel, extra sheets and ranges are not useful for our calculations. We do not bother about those ranges and sheets. But for a file with a large number of sheets, those can seriously affect it. So, we should remove unused sheets and ranges.
15. Save Excel File as Excel Binary File
If we need only to store data and no Excel tools are necessary, that time we can save the Excel file as an Excel Binary file. The format of the Excel binary is .xlsb. A .xlsb file removes all Excel features from the sheet and further, we can not apply any Excel tool here. This .xlsb file is very much faster than a simple Excel file.
16. Consider Using Database Tools for Storing Data.
Usually, we store data and calculate it in the same Excel file. But when there is a large amount of data it is better to use another additional file to store data. And input the formula or apply any operation in the existing file. We can save data in CSV format or other Excel-supported files. This will open the Excel file faster.
In this article, we described 16 reasons and recovery options to make an Excel file open faster. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.
- How to Improve Excel Performance with Large Files
- How to Open Large Excel Files Without Crashing
- How to Stop Calculating Threads in Excel
- How to Stop Calculating 8 Threads in Excel