This will prevent Excel from calculating and updating while the VBA macro is running, which will allow the macro to process much faster that it otherwise would.
![excel 2016 slow recalc with filters on excel 2016 slow recalc with filters on](https://i0.wp.com/www.stl-training.co.uk/b/wp-content/uploads/2015/03/Right-click.jpg)
![excel 2016 slow recalc with filters on excel 2016 slow recalc with filters on](https://cdn.ablebits.com/_img-blog/excel-calculations/excel-calculate-active-sheet.png)
You can access the VBA editor by pressing "Alt" and "F11." Enter those lines after the "Sub" lines or "Dim" lines at the top of the code. It's not always possible to find another way to recreate formulas without these functions, but if you can, it will certainly speed up Excel's calculationsĪdd the following two lines to any VBA macro that you may use:Īpplication.ScreenUpdating = False Application.Calculation = xlCalculationManual The seven volatile functions are RAND, NOW, TODAY, OFFSET, CELL, INDIRECT and INFO. In Excel, volatile functions are those that will always recalculate every time any part of the worksheet changes. Remove volatile functions and replace them with non-volatile ones wherever feasible. You can sort a column by selecting the column and then pressing the "Sort and Filter" button under the "Home" tab. Excel functions that look up data run much faster on data that is already sorted. You will now save valuable calculation time, as Excel will only need to make that calculation once. Find an empty cell on the worksheet and type that portion of the formula into the cell then change the formulas to reference that particular cell instead of performing the calculation. For example, if you have thousands of formulas down column "C," and they all have "($A$1+$B$1)" as part of their formula, it means that Excel will make that calculation thousands of times whenever it calculates the worksheet. This solution may not be possible, or desirable, if the data is on a different worksheet for design or access reasons.Ĭheck your worksheet's formulas for any repeated calculations that you can change to static references. It takes Excel longer to calculate if it has to pull data from a different sheet. Move all of your data to a single worksheet, if possible.
![excel 2016 slow recalc with filters on excel 2016 slow recalc with filters on](https://docs.microsoft.com/en-us/office/vba/images/ocd_xl2010_ta_improvingcalculationperf_fig03.jpg)
Place a check mark next to "Enable multi-threaded calculation," and click the radio button next to "Use all processors on this computer." Click "OK" to close the window. Click "Advanced" on the left side of the Options window and scroll down to the "Formulas" section. Click on "Formulas." Place a check mark next to "Enable iterative calculation" if your worksheet has intentional circular references, as this will allow Excel to stop endlessly calculating. Click "File" at the top of the screen and select "Options" to open the Options window.