Your Excel setup is slowing down your workbook—here's how to fix it

It's tempting to blame a slow Excel spreadsheet on a weak processor, but the fix usually lives in the formula bar. By removing the hidden bottlenecks inside your formulas and data structure, you can make Excel feel brand new. Here's why your sheets are slow and how to repair them.

Replace volatile formulas with static alternatives

Kill the "always-on" calculations

Volatile functions are one of the fastest ways to slow down an Excel workbook. Unlike standard formulas, which update only when their specific inputs change, volatile functions calculate every time anything changes. This creates a recalculation domino effect where a single change can trigger large sections of the workbook to recalculate.

Functions like OFFSET, INDIRECT, TODAY, and RAND can trigger these full-workbook loops even when you're editing unrelated cells. At scale, this creates constant background noise that drags performance to a halt.

To fix this:

• Replace OFFSET with INDEX: INDEX is non-volatile and achieves the same dynamic results without forcing a recalculation every time you click a cell.

• Avoid INDIRECT for dynamic ranges:INDIRECT breaks Excel's ability to track dependencies, forcing the engine to "guess" what needs updating. Use structured tables instead.

• Toggle manual calculations:If volatility is unavoidable, switch Excel to manual calculation mode ( Formulas > Calculation Options > Manual). This prevents full recalculation after every edit and lets you control updates with F9.

• Paste as values:If you only need a result once, convert a formula to a fixed value. Copy the cell ( Ctrl+C) and paste as values ( Ctrl+Shift+V).

Narrow your data ranges to save processing power

Stop checking a million empty rows

Referencing full columns forces Excel to scan over a million rows, even when only a fraction contain data. A formula like =XLOOKUP(A1,E:E,F:F) looks clean, but it actually tells Excel to evaluate every single row in columns E and F. Multiply that across a workbook, and recalculation time increases quickly.

To fix this:

• Convert to tables:Click Insert > Table(or press Ctrl+T) to turn data into a table. Tables use structured references (like [ColumnName]) that only ever include rows contained within their confined object.

• Reset the used range:Press Ctrl+Endto find the last used cell. If it jumps to row 1,048,576 but your data ends at row 500, select the empty rows, delete them via the right-click menu ( not the Delete key ), and save the file to purge the phantom bloat.

If you don't want to hunt for ghost cells manually, click Review > Check Performance(Excel for Microsoft 365), then review the Workbook Performancepane on the right to see which sheets have the most optimizable cells. Then, click Optimize Allto strip away the digital scar tissue in one go.

Move the heavy lifting into Power Query

Stop the copy-paste and lookup cycle

If your workbook relies on long chains of XLOOKUP formulas to combine datasets, Excel repeatedly recalculates those formulas across the dependency chain. Power Query moves that work outside the grid. Instead of recalculating continuously, it processes data during a refresh and loads the results as a static output.

To optimize with Power Query:

• Merge instead of searching:Click Data > Get Data > Combine Queries > Mergeto join tables together rather than using lookup formulas.

• Filter early:Use the Power Query Editor to remove unnecessary rows and columns before the data ever reaches your worksheet.

• Keep the grid clean:Load your data as a Connection Onlyquery so Excel stores it without creating extra worksheet copies of the same data.

• Refresh on demand:Unlike formulas that "live" in your cells, Power Query only runs when you click Refresh, giving you total control over when the processing happens.

If you want periodic refreshes to happen automatically, set Power Query to update on a timer . While background refreshes still carry a performance cost, the impact is significantly lower than maintaining dozens of active XLOOKUP and volatile formulas.

Build a Data Model with Power Pivot

Handle millions of rows with ease

The standard Excel grid can struggle with large datasets, even when formulas are optimized. Power Pivot solves this by loading data into a compressed model and using DAX measures that only calculate when referenced in a PivotTable or report.

To get started:

• Enable the add-in:Go to File > Options > Add-ins > COM Add-ins, then select Power Pivot for Excel.

• Use relationships:Link tables via shared IDs rather than pulling data from one sheet to another with formulas.

• Switch to DAX:Build measures for your calculations. These stay dormant until they're actually displayed in a PivotTable.

• Compress your data:The Power Pivot engine can handle millions of rows while keeping your XLSX file size surprisingly small.

Purge ghost formatting and hidden metadata

Slim down your file size instantly

Formatting and hidden metadata can quietly bloat your workbook, affecting load times, save times, and navigation responsiveness. Common culprits include excessive conditional formatting and full-column styling.

To clean things up:

• Clear redundant rules:Go to Home > Conditional Formatting > Clear Rules > Entire Sheetand reapply only what you need.

• Avoid whole-column formatting:Don't apply colors or borders to entire columns. Keep styling restricted to your actual data ranges.

• Use the Document Inspector:Go to File > Info > Check for Issues > Inspect Documentto —but review the results carefully before deleting anything, as some items (like comments or embedded data) may be useful.

• Save as binary:If your file is still huge, go to File > Save Asand select Excel Binary Workbook (.xlsb), a compressed binary format that opens and saves faster.

---

Make Excel fast again by fixing how you use it

The fixes I've talked about in this guide will go a long way toward improving the performance of your Excel workbook. But actually, many slowdowns are caused not by the app or your hardware, but by how you're using it. Making a few minor tweaks to streamline your workflow helps keep things fast, so you'll get your work done in no time.