Speed up a slow spreadsheet

Large workbooks with thousands of rows and formulas can become slow: opening takes a long time, filtering stutters and you have to wait after every change. This usually isn't down to your computer, but to how the spreadsheet is built. These steps will make it run much more smoothly.

1. Don't use whole columns in formulas

=SUMIF(A:A,...) or =XLOOKUP(...,B:B,...) calculates across more than a million rows, even if you only use 2,000. Use a fixed range instead: $A$2:$A$5000.

2. Lighter formulas

• Thousands of SUMIF or VLOOKUP formulas that refer to another sheet slow the file down. A single pivot table often does the same job much faster (see Pivot Tables).
• TODAY(), NOW(), INDIRECT() and RAND() recalculate with every single change. Use them sparingly.

3. Convert fixed results to values

Does a result no longer change, such as last year's figures? Copy the cells, right-click and choose Paste special with values only. That way Euro-Office doesn't have to keep recalculating them.

4. Tidy up

• Remove empty rows and columns that still have formatting applied, right up to the end of the sheet. Press Ctrl + End: if you end up far below your last data, there's still formatting sitting there.
• Clean up conditional formatting via Manage rules: after a lot of copying and pasting, there are often dozens of duplicate rules there.
• Remove large images and worksheets that no one uses any more.

5. Split it up

Put older years in a separate archive file. A workbook containing only this year's data opens much faster.

6. Working with many people at once

If a lot of colleagues work in the file at the same time, set the collaborative editing mode to Strict on the Collaboration tab. Other people's changes will then only come through when they save, so you're not constantly interrupted.

Please note: if a file is still very slow after these steps, please contact WebOké support. Mention the file name and what's slow (opening, typing, filtering).