Excel Slow with Many Rows? Speed Up a Big Statement

Excel is slow with many rows when each edit forces it to redo work across the whole sheet. In a converted statement the usual causes are volatile formulas, whole-column references inside heavy formulas, conditional formatting on every row, and formatting that stretches far past the data. Find which one you have, fix it, and thousands of transactions stay quick.

Why is Excel slow with many rows of statement transactions?

Excel slows down with many rows when the cost of each action grows with the row count. Typing a value, sorting, filtering or scrolling should take a moment. When it takes seconds, something is being recalculated or redrawn across every row, not only the one you touched.

Three kinds of slowness feel similar but have different causes, so name yours before fixing anything:

• Slow after every edit. The status bar shows "Calculating" or the cursor spins after you type. This is recalculation, and formulas are the cause.
• Slow to scroll or select. The grid lags as you move through it. This points to conditional formatting or a large number of formatted cells being drawn.
• Slow to open and save. The file takes a long time to load even before you touch it. This is file size and the used range, the area Excel thinks holds data.

Row count alone is rarely the problem. A sheet of 50,000 plain values with a few SUM formulas is light work for Excel. The same rows with a lookup on each line that scans the full column, a volatile date check, and three colour rules become heavy. Converted statements pick up these extras because people build helper columns to clean the data, then never remove them.

A diagnosis table

Volatile formulas recalculating down every transaction row

A volatile formula recalculates every time Excel recalculates anything, even when none of its inputs changed. Put one on each of 20,000 transaction rows and every keystroke triggers 20,000 recalculations. Common volatile functions are OFFSET, INDIRECT, TODAY, NOW and RAND. Replacing them is often the single biggest speed gain.

Microsoft's guide to optimizing Excel performance obstructions says volatile functions slow recalculation because they increase the number of formulas that must be recalculated at each calculation. It recommends INDEX in place of OFFSET and CHOOSE in place of INDIRECT, and notes that INDIRECT also always calculates on a single thread.

Where volatile formulas hide in a statement sheet

• An "age" column using TODAY. A formula such as =TODAY()-A2 on every row, to show how old each transaction is. Put TODAY in one cell at the top and refer to that cell instead, or type the report date as a fixed value.
• A running balance built with OFFSET. Something like =SUM(OFFSET($C$2,0,0,ROW()-1)) recalculates constantly. A plain running total does the same job: =C2 in E2, then =E2+C3 in E3 filled down, so each cell depends only on its neighbours.
• INDIRECT pointing at monthly sheets. If each month lives on its own sheet and a summary uses INDIRECT to pick one, consider stacking the months into one table and filtering instead.

A single TODAY in one cell is harmless. The cost comes from repeating volatile calls on every row, where each copy is recalculated each time.

Whole-column references in a big transaction sheet

Whole-column references such as A:A are not always slow. Simple functions like SUM and SUMIF recognise where the data ends and skip the empty rows. Array formulas and SUMPRODUCT do not, so they work through all 1,048,576 rows of the column on every recalculation. Exact-match lookups are also slower the more rows they scan.

The same Microsoft performance guide explains the difference. It says SUM and SUMIF calculate whole column references efficiently because they recognise the last used row, while array calculation functions like SUMPRODUCT either cannot handle whole columns or calculate every cell in them. It also says an array formula that references a whole column is extremely slow to calculate.

What to change

• SUMPRODUCT over full columns. Change =SUMPRODUCT((B:B="Fuel")*C:C) to SUMIFS, which handles whole columns well, or to a range that stops at the last row.
• Exact-match lookups on every row. An XLOOKUP or VLOOKUP on each transaction that scans a whole category column does far more work than one that scans a short category list. Keep lookup lists small and on their own sheet.
• Helper columns filled to the bottom of the sheet. Fill formulas only as far as the data. A formula copied to row 1,048,576 is still a formula Excel has to track.

A check that takes ten seconds: click a cell in a slow formula column, press Ctrl+Down, and see where it stops. If it runs far past the last transaction, the column is filled much deeper than it needs to be.

Conditional formatting rules spread across thousands of transaction rows

Conditional formatting slows a big sheet because Excel evaluates every rule for every visible cell whenever it recalculates or redraws the screen. A few rules on a tidy range cost little. Dozens of overlapping rules, often created by copying and pasting rows, make scrolling lag and edits stutter on a statement with thousands of lines.

Microsoft's performance guide states that when a cell is displayed, every conditional format formula is evaluated at each calculation and each time the display of that cell refreshes. That is why the lag shows up as you scroll, not only when you type.

Cleaning up the rules

1. Select the transaction range and open Conditional Formatting, then Manage Rules, and set the view to "This Worksheet".
2. Look for repeated rules that do the same thing on slightly different ranges. Copy-and-paste creates these silently.
3. Delete the duplicates and keep one rule that applies to the full data range.
4. Replace formula-based rules with built-in ones where you can. A rule that highlights negative amounts does not need a custom formula.

If you highlight rows by category, a filter on the category column is often faster and clearer than colouring thousands of rows.

Do Excel tables slow down a large statement sheet?

Excel tables usually make a large statement sheet faster to work with, not slower. A table's structured references, such as Table1[Amount], grow and shrink with the data, so formulas cover exactly the rows that exist. That avoids both whole-column scans and formulas filled far past the last transaction.

Microsoft's performance guide recommends structured table references for this reason and lists fewer performance drawbacks than whole-column references or dynamic named ranges. Calculated columns in a table also fill to the last row automatically, so you never have a formula lingering 100,000 rows below the data.

Tables can still feel slow in two situations:

• Very wide tables with many calculated columns. Each new row recalculates every column. Move cleanup columns out once their job is done.
• Total rows that use heavy functions. A table total that runs SUMPRODUCT over the table is still an array calculation.

If the converted data has wrapped descriptions split over two rows, fix those first, since a table built on broken rows will carry the problem along. Joining wrapped rows in Excel shows how to merge them back into one transaction per row.

Stray formatting and the used range in a many-row workbook

A workbook can be slow with few real rows if Excel believes the sheet is much larger. Formatting whole columns, deleting data without deleting rows, or pasting a block far below the table all stretch the used range. Excel then tracks and saves that area, making files slow to open and save.

The Microsoft performance guide describes the used range as the area Excel stores information about, and says editing and formatting can stretch it well beyond the cells you consider used. It suggests checking with Ctrl+End and, where the range is too large, deleting the rows and columns below and to the right of the real last cell, then saving. It also advises making a backup first.

Resetting the used range

1. Press Ctrl+End. Note where Excel lands.
2. If it lands below the last transaction, select the first empty row, press Ctrl+Shift+Down, and delete those rows.
3. Do the same for columns to the right of the data.
4. Save, close and reopen. Ctrl+End should now land on the last data cell.

Deleting rows is different from clearing them. Pressing the Delete key clears values but leaves the formatting, so the used range stays the same size.

Fixing a slow many-row statement workbook in order

Fix a slow statement workbook in order of payoff: volatile formulas first, then whole-column array formulas, then conditional formatting, then the used range, and finally finished helper columns pasted as values. Measure before and after each step, so you know which change helped and you do not undo work you need.

An example clean-up

The workbook below is an example, with made-up figures, of a year of converted statements in one sheet.

Before you start, write down the row count and the total of the amount column. Check both again at the end. A clean-up that changes the total has deleted data, not just weight.

Mistakes that make it worse

• Switching to manual calculation and forgetting. It hides the slowness, but totals stop updating until you press F9. Use it during heavy edits, then switch back.
• Pasting every column as values. Keep the formulas you still need, such as a running balance you want to check.
• Splitting the year into twelve workbooks. The slow formulas travel into every copy, and totals across months now need links between files. Fix the cause in one workbook instead.
• Blaming Excel for a slow source. If the hang happens while the statement PDF is still open in a browser tab, the bottleneck is upstream. A browser that crashes with a large PDF covers how to work around that before the data reaches Excel.

The data you start with matters too. A conversion that returns one clean row per transaction leaves less cleanup to do, and the trade-off between speed and accuracy in PDF conversion explains why a slightly slower, cleaner conversion often saves time in Excel later. FastBankConvert is being built to put each statement's dates, descriptions, amounts and balances in their own columns; it is getting ready to open, and the FastBankConvert homepage shows a sample run.