How to Make Excel Open Faster (16 Possible Ways)

Excel is capable of processing 6.6 million formulas per second, yet sometimes it can become very slow. In this article, we will discuss some of the main reasons, and their solutions.

Solution 1 – Avoid Volatile Formulas

Volatile functions are those that recalculate values when changing worksheet. This frequent, automatic calculation hampers the performance of Excel. NOW, TODAY, RAND, OFFSET, INDIRECT, etc. are known to be volatile functions. To speed up Excel, avoid the use of volatile functions.

Read More: How to Make Excel Calculate Faster

Solution 2 – Utilize Helper Columns

A helper column is an extra column added to a dataset that simplifies a complex formula for calculation or analysis, improving performance. We can also avoid processor-intensive array formulas by using helper columns.

Solution 3 – Avoid Array Formulas

An Array Formula can perform multiple calculations at the same time, but it uses many cell references, which decreases the performance of Excel. So, avoid the use of the array formulas to speed up Excel.

Solution 4 – Reduce the Use of Conditional Formatting

Conditional Formatting is one of the most popular features of Excel, since it can perform lots of operations without the use of complex formulas. But conditional formatting performs lots of background calculations, which reduce the performance of Excel. Conditional formatting is also volatile – It changes results from time to time.

Solution 5 – Use Named Ranges and Excel Tables in Formulas

Excel Tables and Named Ranges are dynamic, meaning there’s no need to edit them manually all the time. Although creating tables or named ranges can be time-consuming, they simplify formulas by avoiding the use of cell ranges, which makes Excel run faster as a result.

Solution 6 – Extract Values from Formulas

Where formulas are unused or static, i.e. their results never change, replacing these formulas with their results will reduce the processing required by Excel and therefore make it run faster.

Solution 7 – Separate Referenced Data into a Different Sheet

Keeping all the cell references in another sheet will increase the speed of the active sheet referencing them.

Solution 8 – Avoid Using Whole Rows/Columns in References

Using a whole column or row as a cell reference complicates formulas and slows Excel down. Rather reference just the ranges required in those columns/rows.

Solution 9 – Enable Manual Calculation Mode

Excel has different calculation modes. When using complex formulas consisting of volatile functions, enable Manual Calculation mode. In Normal Calculation mode, Excel recalculates formulas in the background automatically. With Manual Calculation, Excel will ask when the formula needs to be recalculated. So changes to data or formulas using Manual Calculation will not reflect instantly, but performance will improve.

To enable Manual Calculation:

Go to the Formulas tab.
Select the Manual option from the Calculation Options.

Solution 10 – Apply Faster Formulas

We can use faster functions or formulas to make Excel run faster. For example:

Using the MAX function instead of the IF function. MAX function is 40% faster than the IF function.
Using the combination of INDEX and MATCH functions instead of the VLOOKUP function. VLOOKUP is a complex operation that slows Excel down.
Use the double negative sign to transform TRUE and FALSE to 1 and 0.

Solution 11 – Enable the Use All Processors Option

While Excel is running, your computer may be busy with other background tasks that are competing for processing power with Excel. To ensure that the maximum number of processors are dedicated to Excel:

Go to File >> Options.
In the Excel Options window that appears, select Advanced.
Check the Use all processors on this computer option.
Click OK.

Solution 12 – Disable Excel Add-ins

Add-ins can slow down Excel files. When the purpose of a particular Add-in is fulfilled, disabling it will speed Excel up.

To disable Add-ins:

Go to File >> Options.
Choose Add-ins from the Excel Options window.
Select the Add-ins to disable.
Go to Manage and press the Down arrow.
Choose Disabled Items from the list.
Click Go and then OK.

Solution 13 – Use VBA Macros Instead of Formulas

Most of the functionality of Excel formulas and features can be accomplished using VBA macros. VBA macros only use Excel processing resources when running, so don’t slow Excel down to the same extent as formulas or features that are running constantly.

Read More: How to Make VBA Code Run Faster

Solution 14 – Remove Unused Sheets and Ranges

A large number of sheets in a workbook can seriously impact the speed of Excel. Removing unused sheets and ranges can significantly increase performance.

Solution 15 – Save the Excel File as an Excel Binary File

If an Excel file only needs to store data, and no Excel tools are required, then save the Excel file as an Excel Binary file, whose format is .xlsb. An .xlsb file removes all Excel features from the sheet and no Excel tools can be applied to it. Therefore an .xlsb file is very much faster than a normal Excel file.

Solution 16 – Consider Using Database Tools for Storing Data

Usually, we store data and perform calculations on it in the same Excel file. But when there is a large amount of data it is better to use one file to store data and another to apply whatever manipulations we need to that data. Save the data in CSV format or other lightweight Excel-supported files. This approach will speed up the opening of the Excel file in particular.

<< Go Back to Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!