A statement with years of rows is rarely too big for Excel, so a hang usually comes from what is wrapped around the data: formulas that recalculate constantly, formatting stretched down thousands of empty rows, or a heavy workbook. Load the data through Power Query, remove unused columns and rows, set calculation to Manual while you work, and save as XLSX.
Is the file really too big?
Excel handles just over a million rows per sheet. Even a busy account for several years is usually thousands of rows, not millions. So first look for the cause. Press Ctrl+End. If the cursor jumps to a cell far below your data, Excel is carrying formatting or stray content for thousands of empty rows, and that slows every action.
| Symptom | Likely cause | Fix |
|---|---|---|
| Ctrl+End jumps far below the data | Formatting on empty rows | Delete those rows, save, reopen |
| Everything pauses after each edit | Heavy or volatile formulas | Manual calculation; replace the formulas |
| File opens slowly | Many styles, shapes or whole-column formats | Clear formats; remove pictures |
| Sorting or lookups lag | Exact lookups over thousands of rows | Sort the data and use a helper column |
| File is huge on disk | Years of formulas and formats | Split by financial year; save as XLSX |
What should you do first, step by step?
- Make a copy of the workbook so you can go back.
- Select the empty rows below your data, right-click and Delete them (not just Clear), then do the same for unused columns. Press Ctrl+End again.
- Choose Formulas > Calculation Options > Manual. Press F9 only when you want fresh results.
- Choose Home > Find & Select > Selection Pane and delete stray pictures and shapes copied from the bank's file.
- Save as XLSX, close and reopen the file.
How does Power Query keep it light?
Instead of opening the CSV into a sheet and then trimming, use Data > Get Data > From File > From Text/CSV and choose Transform Data. Remove columns you never use, such as value date or branch code, filter out summary rows, and set types. Then choose Close & Load To and pick Only Create Connection or Table. Pivots and totals can be built from that query, and the cleaned data loads once instead of being edited by hand each time.
Which formulas make Excel slow?
- Volatile functions such as OFFSET, INDIRECT, TODAY, NOW and RAND recalculate on every change.
- SUMPRODUCT or array formulas across tens of thousands of rows.
- Whole-column ranges inside many lookups, for example thousands of VLOOKUP formulas.
- Conditional formatting applied to whole columns.
For older years that will not change, select the formula cells and choose Paste Special > Values, which turns them into plain numbers and removes the recalculation cost.
What does the saving look like in numbers?
Say a sheet has 12 category columns and 3,000 rows, and each cell holds a SUMIFS that scans 5,000 rows. That is 12 x 3,000 = 36,000 formulas, and 36,000 x 5,000 = 180,000,000 comparisons on every recalculation. A single summary table of 12 SUMIFS scans the same rows only 12 x 5,000 = 60,000 times. These numbers are only an illustration, but the lesson holds: one summary beats thousands of repeated lookups.
Does the file format matter?
XLSX is the sensible default. XLSB, the binary workbook, can open and save faster for very large files, but other tools and many apps cannot read it, so it is a poor choice for archiving or sharing. CSV is smallest but holds no formulas or formatting. If your statements keep growing, SpendixAI takes statement files as PDF, CSV or Excel and keeps the transactions in a workspace, so you do not need one ever-growing workbook.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

