How do you handle large datasets in Excel and improve a slow workbook?

First, work out what is slow — opening, calculating, or scrolling. They have different causes.

The usual culprits and fixes:

• Volatile functions — NOW, TODAY, RAND, OFFSET, INDIRECT, and CELL recalculate on every change to the workbook, not just when their inputs change. Replacing OFFSET and INDIRECT with INDEX is often a dramatic improvement.
• Whole-column references — SUMIF(A:A, ...) processes a million rows. Use a Table reference or a bounded range instead.
• Array formulas over huge ranges, and SUMPRODUCT across entire columns.
• Excessive conditional formatting, which is re-evaluated constantly and quietly duplicates rules when rows are copied.
• Unused cells with formatting. If Ctrl+End goes far beyond your data, delete the empty rows and columns and save — this alone often shrinks a file dramatically.
• Too many links to other workbooks, and images or shapes accumulated over time.

For genuinely large data, change approach rather than optimise:

• Power Pivot and the Data Model hold far more than a worksheet, compress heavily, and are built for aggregation.
• Power Query to filter and aggregate at the source so you load only what you need.
• Manual calculation mode while building, then F9 to recalculate.

Note: Saving as .xlsb rather than .xlsx is a quick practical win — the binary format is smaller and opens faster.