Speed Up Your Slow Spreadsheets!

Posted by Colleen Ludgate on Wednesday, Feb 21st, 2018
Category : Microsoft Excel

Speed Up Your Slow Spreadsheets!

Are you finding your Excel spreadsheets are slowing down to a crawl?

If your workbook contains many formulas, there is a chance those formulas are slowing Excel down. This is because Excel recalculates all the formulas that depend on a specific cell every time you change that cell.

So the formula =SUM(A:A), which adds up all the cells in column A will recalculate each time you update any cell in column A.

However… the sum formula is very fast, so even if you use it 1000 times in your workbook, it won’t cause a noticeable slowdown.

So what can slow down your workbook?

The answer is… more advanced formulas like SUMPRODUCT() or an array formula.

For instance, the following array formula is a pretty simple array formula. This formula sums up all the odd numbers in column A:

{=SUM(IF(MOD(A:A,2)=1,A:A,0))}

This formula is much slower than the SUM function. I’ve used this formula only 12 times on sheet1 in this workbook and on my machine it takes 5 seconds to add a value to column A, which makes this workbook too slow to use.

Fortunately for us, there are ways to make Excel work faster even with these advanced formulas.

Use limited ranges or dynamic ranges

The reason the array formula above evaluates so slowly is that it calculates for every cell in column A (that’s more than 1 million cells).

One way to make this formula work faster is to limit the range. So instead of using this formula:

{=SUM(IF(MOD(A:A,2)=1,A:A,0))}

You might use this formula:

{=SUM(IF(MOD(A1:A10000,2)=1,A1:A10000,0))}

Because the formula above is limited to “only” the first 10,000 cells, it will work 100x times faster!

But sometimes you can’t be sure that the data won’t exceed a specific amount of rows. In those cases, you can use a dynamic range that expands automatically to include the number of rows contained in the worksheet.

You can define a dynamic range called ‘ColumnA’ which will equal this:

=OFFSET(Sheet2!$A$1,0,0,COUNT(Sheet2!$A:$A),1)

And then use it in the original formula in the following manner:

=SUM(IF(MOD(ColumnA,2)=1,ColumnA,0))

This will cause the formula to calculate only for the rows in column A that contain data. Click the following link for more information on dynamic ranges.

But at times, even limiting the range used in the formula isn’t enough.

To see the performance difference between these two methods, open the sample file and update data on the first and second sheet. You’ll see a very palpable difference.

One of our clients had a workbook with over 12,000 array formulas and although we used dynamic ranges to limit the range size in each of those formulas, the workbook took over a minute to update with 1000 data rows.

For that client we used the following technique:

Stopping/Starting data calculation

This workbook was divided into a data entry sheet and ‘data analysis’ sheets which contained the array formula.

We employed a simple macro to stop the formulas on the workbook from automatically updating every time the user entered the ‘data entry’ sheet and a second macro to calculate all the formulas on the workbook when the user left the ‘data entry’ sheet.

This way the user was able to update data very quickly and wait only once (when leaving the sheet).

Here is the macro we used whenever the user entered the ‘data entry’ sheet:

Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationManual
End Sub

And this is the macro we used when the user left the sheet:

Private Sub Worksheet_Deactivate()
    Application.Calculate
    Application.Calculation = xlCalculationAutomatic
End Sub

You can see how stopping and starting the automatic calculations effects performance in the sample file.

Note that in the sample file, we stop and start the calculation by pressing buttons and not by moving between sheets.

Summary

We use Microsoft Excel to improve productivity. We can improve productivity even more by removing unnecessary waits through writing efficient formulas and controlling when Excel does its magic! Look for opportunities to use these techniques to speed results and improve the user experience.

P.S.

Don’t forget to check out our PDF To Excel Converter. It can save you a lot of precious time you now spend on retyping PDF data.

Latest From Our Blog