In this Excel tutorial, we will review some of the best strategies to make Excel faster with lots of data by avoiding the use of volatile functions, array formulas, unnecessary conditional formatting, and much more.
While preparing this article, we used Microsoft 365 for applying all operations, but they are also applicable in all Excel versions.
Excel’s quick formula calculations and continuous data updates are what make Excel an excellent tool for professionals. However, this continuous data update, among other reasons, sometimes makes Excel slower and reduces productivity. Therefore, knowing why your Excel is working slower and how to speed up Excel is vital.
⏷18 Effective Tips to Speed Up Excel
⏵Avoid Using Volatile Functions
⏵Avoid Using Array Formulas
⏵Use Helper Columns
⏵Avoid Unnecessary Conditional Formatting
⏵Use Named Ranges and Excel Tables in Formulas
⏵Storing All Referenced Data in One Sheet
⏵Avoid Using Entire Row/Columns in References
⏵Change Calculation Option to Manual
⏵Reduce Complexity and Number of Formulas
⏵Use Faster Formula Techniques
⏵Use All Available Computer Processors for Excel
⏵Replace Static Formulas with Values
⏵Disable Excel Add-ins
⏵Remove Unnecessary Pivot Tables
⏵Replace Formulas with VBA Macros
⏵Delete Unused Ranges and Sheets
⏵Save Excel File as Excel Binary File
⏵Store Data Using Database Tools
⏷Frequently Asked Questions
⏷Speed Up Excel: Knowledge Hub
Why It Is Necessary to Speed Up Excel?
There are several reasons why it is necessary to speed up Excel.
- While a faster Excel file improves your productivity, a slower one might decrease your productivity.
- Better-performing Excel minimizes the burden on the system’s resources and extends the life of the hardware.
- Slow Excel can also lead to errors or inaccuracies in calculations resulting in data inaccuracy.
- Faster Excel also gives you a competitive advantage in your field of work.
So, to know why your Excel is running slow and speeding up your Excel is vital to increase productivity, and data integrity and get a competitive advantage in your field.
How to Speed Up Excel: 18 Effective Tips
There are 18 different ways to speed up Excel, for example:
- Avoiding the use of volatile functions.
- Avoiding array formulas.
- Making use of helper columns for calculations.
- Properly storing referenced data.
- Avoiding the use of unnecessary Conditional Formatting.
And much more.
1. Avoid Using Volatile Functions
Volatile functions recalculate every time Excel calculates, even if the arguments of functions have not changed.
For example, NOW, INDIRECT, TODAY, OFFSET, and RAND are volatile functions. These functions update cell value each time there is a change in the workbook.
This extra calculation each time requires additional processing power, resulting in a slow Excel workbook.
So, to speed up Excel, always avoid volatile functions if possible; otherwise, minimize the use of these functions in Excel formulas.
2. Avoid Using Array Formulas
With the array formula, several calculations can be done simultaneously. However, the usage of numerous cell references slows down Excel’s performance. So, to make Excel faster, we must avoid using the array formula.
3. Using Helper Columns
Using a helper column enhances the Excel file’s performance. The helper column is an additional column in the dataset that simplifies a complex formula for calculation or analysis. Using the helper column, we can also avoid using an array formula.
4. Avoiding Unnecessary Conditional Formatting
Conditional formatting is one of the most popular features of Excel. It can perform lots of formatting operations with or without the use of any complex formula.
In conditional formatting, we sometimes apply complex conditional formulas that perform calculations throughout whole worksheets or workbooks for applying the formatting. Thus, it reduces the performance of Excel.
Also, conditional formatting is volatile and changes results occasionally. So, it is better to use less conditional formatting.
5. Using Named Ranges and Excel Tables in Formulas
Excel Tables and Named Ranges are one of the finest tools of Excel. They are dynamic—there is no need to edit them manually all the time. Forming a table or named range is time-consuming work. But those are extremely useful. They simplify the formula by avoiding the use of cell range.
6. Storing All Referenced Data in One Sheet
We commonly use cell references from the same or another sheet when applying any formula. In such cases, you can store all referenced data in one sheet to increase the speed of your Excel.
The image below shows that the product database is in the Database worksheet. In the Summary Report worksheet, a database summary is created using a formula containing references from the Database worksheet.
Click the image to see the full view.
The simple trick to increase the speed of this Excel sheet is to keep all the cell references in one sheet. You can see in the image below that the database and summary created from the database are all stored in one sheet. This trick will increase your Excel’s speed significantly.
7. Avoid Using Entire Row/Columns in References
Sometimes, we reference the whole column (A: A) or row (1:1) instead of specific cells. And even if we don’t want to, Excel still checks the empty cells within the range making the process take more time. As a result, Excel’s performance drops. So, it is better not to use a whole column or row as a cell reference.
For example, I calculated each product’s sum using the SUMIF function. However, I used the whole column as a cell reference in the formula, making Excel slow.
So, instead, use cell reference of the cells that contain values like the image below. It will help your Excel to run a lot faster.
8. Changing Calculation Option to Manual
Excel contains different calculation modes, such as Automatic, Automatic Except for Data Tables and Manual. Automatic mode makes Excel calculate every formula every time you perform a calculation, which makes Excel slow.
So, you can enable Manual calculation when using a complicated formula with volatile functions.
- To enable manual calculation, go to the Formulas tab.
- Then, choose the Manual option from the Calculation Options.
9. Reducing Complexity and Number of Formulas
Excel contains more than just formulas. It includes information such as data, tables, charts, and add-ins. All of these add to the processor’s workload.
Additionally, it causes the processor to slow down when thousands of complicated formulas are running. So, reducing the number of formulas and their complexity might speed up Excel.
10. Using Faster Formula Techniques
Excel files can be made faster by using faster functions or formulas. For example, we want to check whether a number is positive. If the number is positive, we will return the number, else, we will return 0.
Usually, we apply the following formula using the IF function to accomplish this.
=IF(B5>0,B5,0)
But we can also apply an alternative formula using the MAX function which is 6% faster than the previous formula. Here is the faster alternative formula:
=MAX(B5,0)
Instead of the VLOOKUP function, we can use a combination of INDEX and MATCH functions. VLOOKUP executes a difficult operation, making the Excel file slower.
A double negative sign converts TRUE and FALSE to 1 and 0. This speeds up the Excel file.
11. Using All Available Computer Processors for Excel
You can also increase the speed of Excel by using all the available processors. Your computer processes more than one thread at a time, which makes it slower. Excel will run much faster if all the processors are available. Follow these steps to do so:
- Go to the File tab and click on Options. Then, the Excel Options window appears.
- Select Advanced options, scroll down, and click on Use all processors on this computer option; lastly, press OK.
Click the image to see the full view.
12. Replacing Static Formulas with Values
If you have a large dataset of calculations and some formulas do not affect the outcomes, then you might replace the formulas with values. For instance, you can use the VLOOKUP function to import data from tables.
Click the image to See the Full View.
After importing, the formula is no longer needed, and you can replace them with values. To do this, copy the result range, select the range with the right click, choose Value, and then press the OK button.
13. Disabling Excel Add-ins
Disabling unnecessary Excel Add-ins can speed up Excel. Excel add-ins are extra features added to the tool that are only needed sometimes. So, you can enhance Excel’s performance by disabling them.
- To disable Excel Add-ins, go to the File tab, select Options, click on Add-ins, and then the Go… button.
Click this image to see the full view.
- Then, uncheck all the Add-ins and click OK from the Excel Add-ins window.
14. Removing Unnecessary Pivot Tables
Removing unnecessary Pivot Tables can increase Excel’s speed. Pivot tables are an excellent tool for dynamically summarizing data. But these tables take up a lot of memory, resulting in a slow Excel. So, you can increase your Excel’s speed by removing unnecessary Pivot Tables.
Before removing Pivot Tables, copy the pivot report. Then, delete the tables and use dynamic data formulas to increase Excel’s speed.
15. Replacing Formulas with VBA Macros
In Excel VBA, using the WorksheetFunction object, you can use all the functions of the Excel sheet. Moreover, with macros, these functions calculate faster than on a worksheet. So, using VBA macro instead of formulas makes your Excel faster.
16. Deleting Unused Ranges and Sheets
Deleting unused ranges and sheets can speed up Excel.
Using extra sheets and ranges is a great tool while working in Excel. But Excel keeps a record of the ranges used and remembers them. As a result, Excel slows down.
So, it is better to delete these extra sheets and ranges for better-performing Excel.
17. Saving Excel File as Excel Binary File
If working with other tools is unnecessary, save your Excel file as an Excel Binary File. Excel binary files end with the .xlsb extension. This removes all functionality from Excel. Simply put, other tools won’t be able to communicate with your file, thus will speed up Excel.
18. Storing Data Using Database Tools
Yes, storing data using database tools makes Excel faster.
Usually, Excel takes longer to process a large amount of data. Therefore, to speed up Excel, you can save the source data in another database tool or CSV file and only use Excel for calculations.
You can also temporarily use Power Pivot or Power Query to import data for data analysis and make Excel run faster.
Conclusion
This article demonstrated how to speed up Excel in 18 different ways. You can make your Excel faster by keeping the file size small when using Excel, avoiding the use of too many formulas and functions, and closing any unnecessary programs. Additionally, updating Excel to the latest version and optimizing the computer’s hardware can also increase performance. Lastly, you can use Excel more effectively and save time by applying these suggestions into practice.
Frequently Asked Questions
1. Why is my Excel running so slow?
Answer: Your Excel is running slow because you might have a large dataset or complex formulas containing volatile functions. Also, it might be a hardware-related issue. In this article, we have discussed more problems and their solutions above.
2. How do you clear the Excel cache?
Answer: Go to the File tab and click on Options. The Excel options window will open. Then, click the Save option and Delete cached files from the Cache Settings. Next, a dialog box will appear to confirm if you want to clear the cached files. Lastly, to remove the Excel cache, click Delete Cached Files.
3. Will more RAM help Excel run faster?
Answer: Yes, more RAM (Random Access Memory) in your computer can help Excel run faster, especially when you’re working with large and complex spreadsheets. RAM is used by the computer to temporarily store data and processes that are actively in use. When you’re working with large Excel files, having more RAM allows your computer to handle larger datasets and complex calculations more efficiently.
With more RAM, Excel can store more data in memory, reducing the need to constantly access the slower hard drive, which can significantly improve performance. So, if you find that Excel is slow when working with large files, upgrading your computer’s RAM can be a practical solution to speed up its performance.
Speed Up Excel: Knowledge Hub
<< Go Back to Excel Files | Learn Excel
