How To Speed Up Excel
‘How To Speed Up Excel’ There is one thing in the life of every Excel user that always frustrates them and that is the slow performance of Excel calculation.
It is not a big issue when you have less data but it becomes difficult if we add more data or calculations to the workbook.
There are many methods and tricks on how to speed up Excel which we can easily use to improve the performance of Excel spreadsheets. In this article I will teach you’ll few tips and tricks to make Excel calculate faster and increase its speed.
Methods to Speed Up Excel Worksheet Performance and Calculation.
Following are the few tips to make Excel spreadsheet run faster (How to speed up Excel)
1. Minimum use of Dynamic/Volatile Formulas
Volatile functions such as RANDBETWEEN, CELL, INFO, NOW, TODAY, INDIRECT, RAND, OFFSET, etc. always recalculate if there is even a minor change in the Excel workbook.
For example, if you use the NOW function in a cell, every time you make a change in the worksheet, the formula would be automatically recalculated and the cell value will get updated. This may take too much time and even additional processing speed.
So the best way out is to avoid such volatile functions as it may affect the speed of the Excel spreadsheet and slow down its performance.
2. Conditional Formatting for unwanted Cells
While applying conditional formatting normally we select the entire column/entire row or the entire sheet, which is not right. The user should only select the range to which he wants to apply the formatting. It may result in a slow excel spreadsheet if applied multiple times or applied on large data sets.
Therefore, another important way out on how to speed up Excel is to use conditional formatting carefully.
Piece of advice – Don’t use conditional formatting on a large data set.
3. Avoid unwanted ranges in Formulas/Functions
While using any Formulas or Functions in excel one should avoid giving unwanted ranges. Here unwanted ranges mean in the terms of extra cells/row/columns. This makes internal calculation slow and sometimes it may hang your file.
So, if possible, try to avoid unwanted ranges in formulas or functions. This can surely solve your query on how to speed up Excel.
4. Avoid Array Formulas
Another reason which many of us don’t know is that the use of array formulas may also slow down the speed of our Excel calculation. Array Formulas takes more time to throw result because of cell reference dependency.
So, the best way out is to avoid array formulas if possible.
5. Use Named Ranges
We should try to use Named Ranges to refer to any data set in Excel. It makes the user’s job easy and internal access time is also less.
While creating any dashboards, it’s always better to convert your data into Excel ranges. It is always a good idea to convert your data into an Excel Table. This will help to speed up your Excel spreadsheet.
An Example of the named range is:
=COUNTIF(MYDATA,”EXCEL”), here MYDATA is named range and we are finding the occurrence of Excel within MYDATA range.
6. Keep source data in one place
It is always better to keep all your source data in one place so that while working with formulas and functions you don’t need to go to another worksheet or workbook to get the data or value. This will consistently help you to speed up excel calculation and boost the speed of your excel sheet.
7. Convert Formulas to Values
Using more formulas affects Excel workbook performance and large file size too. It is highly recommended to convert formulas into values that are the static value.
8. Avoid Using the unwanted Reference in Formulas
One should avoid using the entire row/column or sheets as a reference in formulas. You may think that the row/column only has a few cells with data but Excel doesn’t think that way.
When you give a reference as an entire row/column in any Formula, Excel checks the entire cells of the given row/column/sheet anyways and takes more time for calculations.
9. Better Formulas Techniques
Excel has a strong set of Formulas and it provides different types of Formulas to do the same job. It is always better to use the better one to increase the speed of Excel.
- You can use SUMIF(one condition) instead of SUMIFS
- Use AVERAGEIF(one condition) instead of AVERAGEIFS
- Try implementing COUNTIF(one condition) instead of COUNTIFS
- Try to use IFERROR instead of IF with ISERROR
- Use the INDEX/MATCH instead of VLOOKUP/Hlookup
10. Use Minimum numbers of worksheets
MS Excel calculates a workbook faster if data and formulas are stored in the same worksheet. It’s better to avoid using more sheets in your file
11. Remove Unused Named Ranges & Pivot Tables
Unwanted Range names & Pivot tables are also a reason for the slow performance of the Excel workbook. You can remove the unused range names and pivot tables to speed up your Excel spreadsheet.
12. Use Advance Version of Excel
The older version of Excel does not support 62bit. Check your system and if you are using a 32bit processor, then you can think to update your Excel with 64bit. It is comparatively faster when it calculates large data sets.
13. Reduce Pictures & Shapes in Excel
If you want to use pictures in an Excel worksheet then better compress them. Following are steps to compress the images/pictures/shapes.
- Select the picture you want to compress
- Go to the Picture Format tab
- Click on Compress
14. Use VBA-Macro codes for complex job
Instead of writing complex or nested Excel formulas/functions, it’s recommended to use VBA-Macro Functions or procedures to get your task done. It reduces your file size and gradually increases the speed of your Excel file.
That’s it! I hope this tutorial was helpful and most of yours query on how to speed up Excel must have been solved by now. Follow the above-given methods and you will surely speed up your Excel file. If you are still facing any problems then feel free to write your queries in the comment box. Stay connected and Win Excel with me.
You may also like the following useful Excel links: