How to Make Excel Open Faster (16 Possible Ways)

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.

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.

Conclusion

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.

<< Go Back to How to Speed Up Excel | Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!