The file is slow. Everyone knows it. The CFO has been complaining for six months. The question on the table is always the same: do we buy Power BI, Tableau, Looker - some new platform? The answer, nine times out of ten, is no. The file is slow for one of seven reasons, and every one of them has a fix that takes less time than a platform migration. Here is the order we try them in, and the one that usually wins.
The five symptoms that mean you have one of these problems
Before the fix list, the symptom list. If you see two or more of these in your current workbook, the file is in the territory where the seven fixes below pay for themselves in a single afternoon.
- Open takes 30+ seconds, even on a fast machine
- Typing in one cell causes a multi-second recalc across the whole workbook
- Scrolling through a sheet stutters and lags
- Pivot tables refresh takes minutes, not seconds
- Saving the file freezes Excel for ten seconds
The seven fixes, in the order we try them
We work through these in order - the top of the list fixes 80% of slow files, the bottom of the list is the architecture change we reach for when the data is genuinely too big for a flat workbook.
- 01
Move raw data out of the dashboard sheet
The single most common cause of a slow Excel file is a 50,000-row sheet that the dashboard formula sits on top of. Move the raw data to its own tab, convert it to a Table (Ctrl+T), and have the dashboard reference the Table - not the raw range. The dashboard stops recalculating across 50,000 cells on every keystroke.
- 02
Replace volatile functions
NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT and entire-column references (A:A) are volatile - they recalculate on every change, everywhere in the workbook. Replace NOW() with a named cell that holds the current date once. Replace OFFSET-based ranges with INDEX/MATCH. Replace A:A with a Table reference.
- 03
Replace SUMIFS/COUNTIFS with Power Query when the source grows
SUMIFS across 200,000 rows works - but only just. Once the data crosses half a million rows, the formulas outpace the recalc engine. Move the aggregation upstream into Power Query, output a small summary Table, and the dashboard reads from the summary. Recalc drops from seconds to milliseconds.
- 04
Set calculation to Automatic, but limit iterations
File > Options > Formulas > Calculation Options. If the workbook is in Manual mode because someone set it that way, every number on screen is potentially stale. Switch to Automatic. If there is a circular reference, set Maximum Iterations to 1 and Enable Iterative Calculation off - circulars should not exist in a finished model.
- 05
Delete the formatting, conditional formats and unused styles
Every conditional formatting rule scans the entire range it covers, on every recalc. Ten rules across a million-cell range is what makes the file feel slow. Delete rules that no longer fire, scope rules to the visible range, and avoid "Applies to" ranges that point at entire columns. Same for unused named styles - they accumulate and slow the file open.
- 06
Split the workbook, link the pieces
If the file holds a working data layer, a calculation layer, a presentation layer and ten pivot tables, split it. The data layer becomes a Power Query output (a .csv or a hidden tab). The calculation layer becomes a separate workbook. The dashboard workbook reads from both, and each piece opens in under five seconds.
- 07
Move the data model into Power Pivot (Data Model)
The Data Model - Power Pivot underneath - holds millions of rows in compressed columnar storage. The pivot tables and CUBEVALUE formulas read from it at sub-second speed, even on a laptop. For files with more than 200,000 rows of source data, this is the right architecture. Most analysts never enable it because the option is hidden - it is one checkbox in Power Query's Close & Load To.
The order that matters
The reason we work the list in order is that fixes 1-3 solve the problem for 80% of the files we see. Fixes 4-5 are housekeeping. Fixes 6-7 are architecture changes for files that have genuinely outgrown a flat workbook. Most analysts jump straight to fix 7 (we need Power Pivot!) when fix 1 (move the raw data off the dashboard sheet) was the actual problem. Do the cheap fixes first, then escalate.
When the answer is genuinely a new tool
The honest list of when to escalate. Real-time data from operational systems, multi-tenant access control, row-level security that changes per user, dashboards that need to render inside another product, an SLA of sub-second response with concurrent users in the hundreds. None of these are Excel's job. If you have one of them, the answer is a real BI platform. If you do not, the seven fixes above will turn the file around.
Start with fix 01
Most slow files die at fix 01. Open the workbook, find the sheet that holds the raw data and the dashboard on the same tab, and split them. Two tabs, one Table, one reference. Save the file, time the open. If it dropped from 30 seconds to under five, you just saved yourself a platform migration. If it did not, work the list. Fix 02, then 03, then the rest. Seven fixes, one afternoon, the file feels new - and the team keeps running on the Excel they already own.
