Excel Running Slow? Speed Up Slow Formulas

When Excel is running slow, formulas are the usual suspect, and the cause is rarely the number of formulas alone. A few heavy patterns do most of the damage: functions that recalculate after every edit, array formulas over whole columns, running totals that re-add every row, and lookups that search the same data again and again. This guide shows how to spot each one and the faster formula to use instead.

How to tell if formulas are making Excel slow#

Check whether Excel is busy calculating before you change anything. Watch the status bar at the bottom of the window. If it shows "Calculating" with a percentage after you type, formulas are the bottleneck.

A quick test: go to Formulas > Calculation Options and choose Manual. If typing becomes fast again, the problem is calculation. If Excel is still slow, look at other causes, such as many images, thousands of conditional formatting rules or a slow network drive. Switch back to Automatic when you've finished testing.

Then work through the patterns below, starting with the ones you recognise in your file.

1. Volatile functions recalculate on every change#

A volatile function recalculates whenever anything in the workbook changes, even a cell it doesn't use. Every formula that depends on it recalculates too. The common ones are:

• TODAY() and NOW()
• RAND() and RANDBETWEEN()
• OFFSET() and INDIRECT()

One TODAY() is harmless. The trouble starts when thousands of rows each call TODAY(), or a dashboard builds every range with OFFSET.

The fix:

• Put =TODAY() in one cell, such as H1, and point every formula at $H$1 instead. The $ signs lock the reference so it doesn't shift when you fill down; the lesson on absolute references covers this.
• Replace OFFSET ranges with INDEX, which is not volatile. For example, SUM(OFFSET(C2,0,0,G1,1)) adds the first G1 rows of column C. SUM(C2:INDEX(C2:C1000,G1)) does the same job without the constant recalculation.
• Avoid INDIRECT where a direct reference works. If you use it to switch between sheets, consider one combined table with a column for the sheet name instead.

2. Array formulas and SUMPRODUCT on whole columns#

SUMPRODUCT and other array formulas work through every cell in the ranges you give them. With whole columns like A:A, that can mean over a million rows per condition, most of them empty.

Here is a typical slow formula and its faster replacement, on a small Region, Status and Amount table in A1:C8:

=SUMPRODUCT((A:A="North")*(B:B="Paid")*C:C)
=SUMIFS(C2:C8,A2:A8,"North",B2:B8,"Paid")

Both return 850 (500 + 250 + 100). SUMIFS is built for conditional totals and handles large ranges far more efficiently. If you used whole columns in SUMPRODUCT, header text in C1 would also make it return #VALUE!, which is another reason to switch.

SUMIFS and COUNTIFS cope well with whole-column references, because Excel only checks the used part of the column. In array formulas, limit the range to your data, or turn the data into an Excel table (Windows: Ctrl+T; Mac: ⌘T) so ranges grow with it. The SUMIFS function guide shows the full syntax, and the SUMIF lesson is a good place to start if conditional totals are new to you.

3. Running totals that re-add every row#

A common running total adds everything from the top down to the current row:

=SUM($C$2:C2)

Filled down, row 3 adds two cells, row 4 adds three, and row 10,000 adds 10,000. Over the whole column, Excel does about 50 million additions. A running total that adds the current amount to the total above does just one addition per row:

The results are the same. The trade-off is that inserting or sorting rows can break the chain, so check it after big edits. The guide to running totals in Excel compares both methods.

4. Many lookups searching the same data#

If you pull five columns for each order with five VLOOKUPs, Excel searches for the same ID five times per row. On a big table that adds up quickly.

Do the search once with MATCH in a helper column, then reuse its result with INDEX. With the ID in A2 and a product table in Products!A2:F5000:

=MATCH($A2,Products!$A$2:$A$5000,0)
=INDEX(Products!B$2:B$5000,$H2)

The first formula, in H2, finds the row number once. The second, filled across, returns each column from that row with no new search. Other habits that help:

• Look up in exact ranges, not whole columns, so the search covers only real data.
• Don't look up the same value in several places. Look it up once and refer to that cell.
• Sort the data and use approximate match only if you understand the risks; it is much faster on huge tables but returns wrong results on unsorted data.

5. A used range far bigger than your data#

Excel tracks the used range: every row and column that has ever held data or formatting. On Windows, press Ctrl+End to jump to its last cell. On any version, including Mac, you can open Go To (Ctrl+G), click Special and choose Last cell. If that lands far below your real data, the file is carrying empty, formatted cells.

Select the empty rows below your data, right-click and choose Delete, do the same for extra columns, then save. Also check Home > Conditional Formatting > Manage Rules for rules that apply to whole columns or that were copied many times.

6. Use manual calculation while you build#

For a large model, switch to manual calculation while you work, so Excel doesn't recalculate after every edit:

1. Go to Formulas > Calculation Options > Manual.
2. Press F9 to recalculate all open workbooks when you need fresh results (Mac: ⌘= or F9).
3. Press Shift+F9 to recalculate only the current sheet (Mac: ⇧⌘=).
4. Switch back to Automatic before you share the file.

In manual mode, results can be out of date, and the status bar shows "Calculate" as a reminder. Never send a report while it says that. Speed and accuracy both matter in finance and reporting roles; see the Excel skills for finance jobs and how a clean sales report in Excel is built with SUMIFS. A question about slow workbooks also comes up in Excel interview questions.

Practise it#

Fast formulas start with the right function and the right references. Practise SUMIF in a live spreadsheet to build conditional totals the efficient way, then use the absolute references lesson to lock ranges with $ so one formula fills a whole column correctly. Each answer is checked instantly. Try the SUMIFS exercise on this page first.