Speeding Up Slow Excel Workbooks
A slow workbook is rarely slow because of its size. Most come down to formula and formatting habits that make Excel recalculate, or read, more than it needs.
This page covers nine common causes in accounting workbooks, each with its fix. For the settings and habits that keep Finsyte formulas fast, see Keeping Finsyte Workbooks Fast.
Causes at a Glance
| Cause | Fix |
|---|---|
| A SUMIF whose sum range is a different size | Make both ranges the same size |
| OFFSET, INDIRECT, TODAY and NOW | INDEX, a direct reference or CHOOSE, and a typed as-of date |
| SUMIFS and lookups over a GL export | A PivotTable summary, one MATCH reused by INDEX, or Finsyte balance functions |
| Running totals that re-add every row | The total above plus the current row |
| SUMPRODUCT or array formulas over whole columns | SUMIFS, or an Excel table's structured references |
| Duplicated conditional formatting rules | One rule per format, applied to the whole range |
| Links to other workbooks | Values, or Finsyte formulas for NetSuite figures |
| Formatting past the data | Delete the extra rows and columns, or use Check Performance |
| Last calculated in another Excel version | Calculate and save it once in your version |
Start with the causes that recalculate after every edit: mismatched SUMIFs, volatile functions and duplicated formatting rules. Then cut down what each recalculation has to read.
How Excel Decides What to Recalculate
Excel tracks which cells depend on which. After an edit, it recalculates the cells that changed and the cells that depend on them, and leaves the rest alone. Two things recalculate every time, whatever changed: volatile functions, along with every cell that depends on them, and the conditional formats on screen. Microsoft's guide to calculation performance (opens in new tab) covers the details. Most of the fixes below give Excel less to recalculate, or less to read each time it does.
A SUMIF Whose Ranges Are Different Sizes
SUMIF doesn't require its sum range to match its criteria range. Given =SUMIF(A1:A4,">0",B1), Excel sums B1:B4, a range the size of A1:A4 that starts at B1. Excel treats this form of SUMIF as volatile, so it recalculates, along with everything that depends on it, every time the workbook does. It usually happens by accident, with a sum range typed as its first cell only, or two ranges that drifted apart as the sheet was edited.
Fix: make both ranges the same size. =SUMIF(A1:A4,">0",B1:B4) returns the same total and isn't volatile. SUMIFS can't fall into this trap, because it returns #VALUE! when its ranges differ in size.
Volatile Functions: OFFSET, INDIRECT, TODAY and NOW
A volatile function recalculates every time Excel recalculates, whether or not its inputs changed, and so does every cell that depends on it. Excel's volatile functions include OFFSET, INDIRECT, TODAY, NOW, RAND and RANDBETWEEN, plus CELL and INFO with some arguments. Accounting workbooks use them in three places:
- OFFSET for rolling 12-month and YTD ranges, and in dynamic named ranges such as
=OFFSET($A$1,0,0,COUNTA($A:$A),1). - INDIRECT to read monthly tabs by name, such as
=INDIRECT("'"&B$1&"'!D10"). INDIRECT also calculates on a single thread. - TODAY and NOW in aging schedules, to count days outstanding.
Fix: swap each for a function that isn't volatile.
- OFFSET → INDEX. INDEX can return a range too. With monthly amounts in B2:Y2 and the month number in A1,
=SUM(OFFSET(B2,0,A1-12,1,12))becomes=SUM(INDEX(B2:Y2,A1-11):INDEX(B2:Y2,A1)). For a range that grows with the data, use an Excel table (Insert > Table) and its structured references, such asTable1[Amount]. - INDIRECT → a direct reference or CHOOSE. Point at the tab directly, or let CHOOSE pick among a few:
=CHOOSE($B$1,Jan!D10,Feb!D10,Mar!D10). Better still, keep one table with a Month column instead of a tab per month, and summarize it with SUMIFS. - TODAY → a typed as-of date. Type the as-of date into one input cell (Ctrl+; enters today's date as a value) and point the aging at that cell. The schedule recalculates when you change the date, and the report always shows the date it was run for.
To find them, press Ctrl+F, select Options, set Within to Workbook and Look in to Formulas, and search for OFFSET(, INDIRECT(, TODAY( and NOW(.
SUMIFS and Lookups over a GL Detail Export
A common close pattern is to export GL detail to a tab and build the report with a SUMIFS for every account and month, or with exact-match VLOOKUP, XLOOKUP or MATCH formulas. Each SUMIFS reads every used row of its ranges, and an exact-match lookup reads row by row until it finds a match. A 200-account report with 12 monthly columns is 2,400 SUMIFS, each reading the whole export.
Fix: read the detail less often.
- Summarize once. Build a PivotTable over the export, with accounts in rows and periods in columns, and point the report at that summary instead of the detail.
- Look up once. When you return several columns for the same row, run one exact MATCH in a helper column and use INDEX for each column.
- Skip the export. A Finsyte balance function such as GLAccountBalance returns an account's NetSuite balance for a subsidiary and period directly, so there's no detail tab to sum.
Running Totals That Re-Add Every Row Above
Filled down a column, =SUM($B$2:B2) makes each row add up every row above it. Over 2,000 rows that's about 2 million cell references, and over 20,000 rows about 200 million.
Fix: add each row to the total above it. Put =B2 in C2, then fill =C2+B3 down from C3. Each formula reads two cells, so 2,000 rows read about 4,000 references instead of 2 million.
SUMPRODUCT and Array Formulas over Whole Columns
SUM, SUMIF and SUMIFS handle a whole-column reference such as D:D efficiently, because they stop at the last used row. SUMPRODUCT and array formulas don't: =SUMPRODUCT((A:A=F2)*(B:B=G2),D:D) works through all 1,048,576 rows of each column every time it recalculates.
Fix: use SUMIFS where the conditions allow: =SUMIFS(D:D,A:A,F2,B:B,G2). Where you need SUMPRODUCT or an array formula, limit it to the rows with data, ideally through an Excel table's structured references, which grow as rows are added.
Too Many Conditional Formatting Rules
Excel evaluates the conditional formats on screen with every recalculation, as it does volatile functions, and again as cells are redrawn when you scroll. Rules applied to whole columns cost more, and so do duplicates. Copying and pasting cells copies their rules too, so after a few closes a report can carry dozens of copies of the same rule, each covering a fragment of the range.
Fix: open Home > Conditional Formatting > Manage Rules and set Show formatting rules for to This Worksheet. Delete the duplicates and set Applies to on the rule you keep to the whole report range. When you copy cells within a report, paste formulas or values (Paste Special > Formulas or Values) so the rules stay behind. Conditional formatting is worth keeping (here's how to use it on NetSuite financials); a short rule list keeps it fast.
Links to Other Workbooks
A formula such as ='[Prior Month Close.xlsx]Balance Sheet'!$D$14 ties the workbook to another file. Microsoft's performance guidance (opens in new tab) advises avoiding links between workbooks where possible: they can be slow, easily broken and hard to find. SUMIF, SUMIFS and COUNTIF also return #VALUE! when the workbook they read is closed.
Fix: list the links under Data > Workbook Links (Edit Links in older versions of Excel). Copy the figures in as values, or break the link once the source is final. For figures that come from NetSuite, pull them with a Finsyte formula instead of linking to an exported workbook.
Formatting That Stretches Past the Data
Formatting whole columns or rows, or pasting formats far down a sheet, extends the sheet's used range, the area Excel stores and works over, well past the data. Press Ctrl+End: if Excel jumps far below or to the right of your last real cell, the sheet carries that extra range, which makes the file bigger and slower to work with.
Fix: delete the rows below and the columns to the right of the last real cell (delete them, don't just clear them), then save. Save a copy first, because formulas whose ranges reach into the deleted area change. In Excel for Microsoft 365, Review > Check Performance finds cells that hold formatting but no data, and Optimize sheet or Optimize all clears them.
A Workbook Last Calculated in Another Version of Excel
When a workbook was last calculated in a different version of Excel, Excel recalculates every formula in it when it opens, instead of only what changed. That's why some workbooks take a long time to open and ask to save changes on close when no one edited them. When people on different versions of Excel share a close workbook, it can happen on every open.
Fix: open it in the version your team uses, let it finish calculating, and save it. The next open is quick.