Working with large datasets in Excel can turn from a productive task into a frustrating one fairly quickly. A spreadsheet freezes mid-edit, lags every time you enter a value, or sits on the “Calculating (8 Threads)" message while you wait, and the whole workflow stalls with it.

Row count alone is rarely the real problem. A workbook can hold a large amount of data and still run fine or hold relatively little, and Excel lags with large datasets anyway, depending on the formulas, formatting, calculation settings, and links behind it. This guide looks at why Excel is slow and how to speed it up, working through the actual causes first and then the fixes that address each one.

Before jumping into fixes, it helps to understand what is actually causing the slowdown. Most performance problems trace back to a handful of things: formulas that recalculate more than they need to, leftover formatting that still tracks, formatting rules applied too broadly, and links that have to be checked every time the file opens.

Are Volatile Formulas Making Excel Slow? 

A normal formula only recalculates when the cells it depends on change. Volatile functions behave differently, and not all for the same reason. OFFSET and INDIRECT recalculate constantly because they build cell references dynamically, which Excel cannot fully track in advance. TODAY() and NOW() recalculate because they are tied to the current date and time rather than to any cell at all. Either way, the more of these a workbook has, the more often everything around them gets recalculated too.

How Can Unused Formatting and Rows Affect Excel Performance? 

Excel keeps track of every cell that has ever held formatting or data, even after the contents have been deleted. If a large range was formatted at some point and later cleared, Excel may still be quietly tracking all of it as part of the file's “used range," which adds weight the file does not actually need.

Can Conditional Formatting Slow Down Excel? 

Applying conditional formatting to an entire column, such as A:A, forces Excel to evaluate millions of cells that are actually empty, just to check whether the rule applies to them.

Do External Links Make Excel Slower? 

Links to other workbooks or network locations add delay too, since the file has to check those paths every time it opens or saves, even if the linked file has not changed.

Before touching formulas or settings, a bit of basic housekeeping is often the fastest way to reduce Excel file size and resolve a surprising amount of lag on its own.

Remove Unused Rows and Formatting

Press Ctrl + End and see where it takes you. If it lands thousands of rows below where the actual data ends, the file is carrying formatting or old data it does not need.
To fix this, highlight the blank rows below the real data, right-click and choose Delete (not just the Delete key, which clears content but not the cell's tracked formatting state), then save and close the file so Excel resets its internal boundaries.
Before getting into anything more technical, our beginner Excel step-by-step guide covers clearing unused grid lines, cell styles, and blank rows as part of general worksheet upkeep.

Save Large Workbooks as XLSB

A standard .xlsx file is built on XML, which is readable but not especially compact. Saving the same file as .xlsb, Excel's binary format, compresses the underlying data and can reduce file size, and for some large workbooks this also improves how quickly the file opens and saves. The exact difference depends on what is inside the workbook, so it is worth trying rather than expecting a fixed result.
To do this, go to File > Save As, and choose Excel Binary Workbook (*.xlsb) from the file type dropdown.

This is where most of the work to optimise large Excel spreadsheets actually happens. In complex financial models and reporting sheets, formulas are usually a bigger source of lag than the raw data itself. If you work regularly with formulas, lookups, and larger datasets, learning more advanced Excel techniques through an Intermediate Excel Course can help you handle complex workbooks more confidently.

Reduce Volatile Functions

Where OFFSET is being used to build a dynamic range, INDEX often does the same job while only recalculating when its actual inputs change, which cuts down noticeably on recalculation time in a large workbook. Where INDIRECT is being used to reference cells indirectly, restructuring the data into a proper table and referencing it directly (Table1[Column1]) usually avoids the need for it altogether.

Avoid Whole-Column References

A formula like SUMIF(A:A, …) checks every row in the column, including the empty ones far below the actual data. Narrowing it to the real range, such as SUMIF(A2:A10000, …), limits the evaluation to rows that actually hold data.

Review Complex Lookups and Calculations

XLOOKUP and INDEX-MATCH usually run faster than VLOOKUP across full sheets, especially on large ranges, though the actual improvement depends on the size and structure of the workbook rather than being guaranteed in every case. Reviewing lookup formulas for unnecessarily wide ranges is often more useful than swapping one function for another on its own.

For enterprise-scale or multi-layered workbooks, formula cleanup alone sometimes is not enough, and this is where Excel memory optimisation settings come into play.

Use Manual Calculation for Heavy Workbooks

When entering large volumes of data, Excel recalculating in the background after every keystroke wastes processing power. Go to the Formulas tab, click Calculation Options, and choose Manual. From there, press F9 whenever the workbook actually needs to recalculate. It is worth remembering to switch calculation back to Automatic once the heavy data entry is done, otherwise formulas can appear outdated simply because they have not been told to recalculate.

Check Multi-Threaded Calculation

It is worth confirming that Excel is using all the processor cores available on the machine. Go to File > Options > Advanced, scroll to the Formulas section, and check that “Enable multi-threaded processing" is turned on.

When a workbook is handling a genuinely large dataset, and calculations and worksheet formulas are also heavy, keeping raw data directly on worksheet grids tends to become the bottleneck, regardless of the exact row count.

Raw Data Files (CSV/Database)

       │

       ▼

[ Power Query Engine ]  ──(Data Cleaning)──►  [ Data Model / Pivot ]

Power Query cleans and shapes the data before it reaches the workbook, and the Data Model stores it in a compressed form that Excel can work with more efficiently than rows of raw cells. Calculations run through DAX or Pivot aggregations rather than being spread across thousands of individual grid formulas, which keeps the worksheet itself lighter even as the underlying dataset grows. For anyone regularly working with this kind of setup, our advanced Excel skills for professionals blog covers Power Query and related techniques in more depth.

Before sending or presenting a heavy workbook, it helps to run through a short checklist:

Saved as .xlsb (Excel Binary Workbook) where it helps

Removed unused rows and formatting beyond the actual data boundary

Reviewed volatile formulas such as INDIRECT and OFFSET, and replaced them where a simpler non-volatile approach works

Replaced whole-column references (A:A) with explicit ranges (A2:A50000)

Removed unused conditional formatting rules

Switched calculation mode to manual for heavy data entry, then back to automatic afterward

Most Excel slowdowns come down to a mix of small, fixable things rather than one dramatic cause: a few volatile formulas here, some leftover formatting there, a conditional formatting rule applied too broadly. Working through the causes in this guide, rather than guessing at a single fix, is usually what gets a sluggish workbook back to loading and calculating the way it should.