Introduction

Every spreadsheet gets slower over time if nobody deliberately works against it. Formulas accumulate, formatting piles up on cells that no longer need it, and calculation chains grow long enough that a simple edit takes a visible moment to ripple through the file. None of this happens through a single dramatic mistake — it's the accumulation of small, reasonable decisions made in the moment without anyone stepping back to ask whether the file as a whole is still efficient. Optimising a spreadsheet is the practice of periodically making that assessment, and it's a skill that pays for itself the moment a file grows past the size where "just rebuild it" is still a realistic option.

Want expert help putting this into practice? B-Sheets Pro can guide you through it.

Understanding where slowdown actually comes from

Before optimising anything, it helps to know what's actually causing the slowdown, because the intuitive guess is often wrong. It's rarely the sheer number of rows that causes a file to lag — modern spreadsheet engines handle large row counts reasonably well. The more common culprits are volatile functions that recalculate on every single change regardless of whether their inputs actually moved, formulas that reference entire columns instead of a bounded range, and excessive conditional formatting rules layered on top of each other across large ranges. A file with ten thousand rows and clean, bounded formulas will often outperform a file with a thousand rows and messy, unbounded ones.

Diagnosing this requires a bit of detective work: check calculation settings to see whether the file is set to recalculate automatically or manually, look for functions known to be computationally expensive, and scan for ranges that extend further than the actual data — a formula that says "sum this entire column" recalculates across every row in that column, used or not, every single time anything in the file changes. Many platforms expose a way to time recalculation or flag the slowest formulas in a file; running that check before guessing at a fix saves the wasted effort of optimising something that was never actually the bottleneck.

Trimming what the file doesn't need

Related: Mastering the Art: Complete Guide to how to sheet checklist.

Every spreadsheet accumulates cruft: formatting applied to blank cells that will never contain data, entire unused sheets left over from an earlier draft, named ranges that no longer point to anything meaningful, and conditional formatting rules layered on top of each other because nobody removed the old one before adding the new one. None of this is visible in the numbers on screen, but all of it adds weight the file has to carry every time it recalculates or saves.

A periodic cleanup pass — deleting unused sheets, clearing formatting from empty regions, consolidating redundant named ranges — is unglamorous work, but it's directly proportional to file performance in a way that's easy to underestimate until you've actually done it once and felt the difference. This is particularly worth doing before a file gets duplicated into next year's template, since any bloat present at that point gets copied forward and compounds every year after.

It also helps to check for hidden rows, columns, and sheets left over from earlier iterations of a model. These are easy to forget entirely since they're invisible during normal use, but they still carry formulas and formatting that the file has to process on every recalculation. A quick unhide-and-review pass before a major cleanup often turns up entire sections nobody remembered were still there.

Restructuring formulas for efficiency without losing clarity

Optimising formulas doesn't mean making them cryptic — it means making them efficient without sacrificing the readability that lets someone else maintain the file later. Replacing a chain of nested lookups with a single well-structured lookup, bounding ranges to only the rows that actually contain data, and consolidating repeated sub-calculations into a single reference rather than recalculating the same thing in multiple places all reduce computational load while keeping the logic just as easy to follow, sometimes easier.

A useful test: if a formula optimization makes the file faster but the next person to open it can no longer tell what it's doing, it's not actually a net improvement — it's traded one problem for another. The goal is efficiency that a colleague can still audit, not efficiency that only the original author can decode.

Managing calculation mode deliberately

See also: How to Sheet Requirements: Expert Guide for Clarity and Precision.

Most spreadsheet platforms default to automatic recalculation, which is the right choice for small files and the wrong choice for large, formula-heavy ones being actively edited. Switching to manual calculation during heavy editing sessions — recalculating deliberately rather than after every keystroke — can make an otherwise sluggish file responsive again, at the cost of remembering to trigger that recalculation before trusting any number on screen. This is a temporary technique for active work sessions, not a permanent setting, since a file left in manual mode is a classic source of the exact silent errors that good spreadsheet practice tries to avoid — a number that looks current but hasn't actually recalculated since the last input change.

Knowing when to split rather than optimise further

There's a point past which further optimisation of a single file offers diminishing returns, and the better move is splitting it — separating a large model into a data layer and a reporting layer that pulls from it, rather than trying to keep everything in one increasingly strained file. This adds a bit of structural complexity, but it isolates the parts of the model that genuinely need to be large and detailed from the parts that just need to summarize and display, and each half becomes faster and easier to maintain than the combined file ever was. It also limits the blast radius of future changes — editing the detailed data layer no longer risks breaking the polished reporting layer that leadership actually looks at, since the two are now connected through a defined link rather than tangled together in the same set of tabs.

Making optimisation a routine, not a rescue mission

The best time to optimise a spreadsheet is before it becomes noticeably slow, not after. Building a habit of periodic review — checking for volatile formulas, unbounded ranges, and accumulated cruft every few months — keeps a file efficient continuously instead of requiring an occasional painful overhaul once it's already become a daily frustration. B-Sheets Pro is built to handle large, formula-heavy files gracefully, but no platform eliminates the need for this discipline entirely — the habits behind optimisation matter regardless of which tool is running the calculations.