There are a few tricks you can pull to help fix a slow excel. It is hard to notice excel spreadsheet is slowing you down when working on a small amount of data but as you increase the amount you will now note the difference. The following will help speed up excel:
Avoid complex and many formulas
It is indisputable how excel can execute 6.6 million formulas in a second even though the computer is also running other programs. Other than formulas excel has data tables, charts, add INS, etc which increases the burden on the processor. Many complex formulas slow down the processor.
Use all the processors that are available for excel.
Make all processors be available for excel to increase their speed through the following steps:
1. Go to file then options
2. Click advanced
3. Scroll down to find the formulas section
4. Choose to use all the processors in this computer
Reduce use of volatile functions
Volatile formulas include: NOW, TODAY, OFFSET, RAND, INDIRECT, INFO, etc. These functions recalculate each time a change is made in excel files. They normally increase the workload on the processors, therefore, reducing the speed of the excel workbook. It is therefore important to avoid using volatile functions and if not try to reduce their use to enable excel to work faster.
Perform manual calculation in excel.
Manual calculation in excel simply means you tell excel when to calculate rather than excel executing commands on its own. This will save time used by excel to repeatedly recalculate.
To switch to manual calculations:
1. Go to the Formula tab
2. Click calculation options then select manual (press F9 key to recalculate)
3. Use static formulas instead of values
If you don't have the need for formulas, it is best to convert them into static values through the following steps:
4. Copy the results range
5. Right-click on the range and go to paste special
5. Select the value and click OK
Disable Excel add-ins
Excel add-ins are additional functions added to Excel and may not be needed every time. When disabled it saves a lot of resources in the system, therefore, increasing formula calculation speed.
To disable add-ins ;
1. Go to file then options
2. Click Add-ins in the Go button. The add active add-ins will open.
3. Uncheck the add-ins that you don't need
4. Click OK
Use database tools for storing data.
Large amounts of data in the workbook slow down Excel formula calculations. To increase the speed of formula calculation save the source data in other database tools or CSV files and use excel only for calculations. Consider using power pivot or power query to import data temporarily.
Use VBA macros instead of formulas
VBA is very useful when working on a large set of calculations and it is easier to do calculations in VBA than directly on a worksheet.
