Most Excel files are small enough not to affect performance, but size isn’t the only thing that can slow things down. Fortunately, you don’t have to know all about multithreads and dual processors to eliminate bad performance. The following tips are easy to implement, so even the most casual users can improve performance when a workbook slows down. Better yet, apply this advice when designing sheets to help avoid sluggish performance altogether.
1: Work from left to right
This tip is easy to implement because data tends to flow from left to right naturally, but it doesn’t hurt to know that there’s a little more going on under the hood. By default, Excel will calculate expressions at the top-left corner of the sheet first and then continue to the right and down. For this reason, you’ll want to store independent values in the top-left portion of your sheet and enter expressions (dependent cells) to the right or below those values. In a small sheet, you won’t notice much difference, but a sheet with thousands of rows and calculations will definitely perform better when you position dependent cells to the right and below the independent values.
In technical terms, this behavior is called forward referencing. Formulas should be to the right or below the referenced values. Avoid backward referencing, where formulas are to the left and above the referenced values.
2: Keep it all in one sheet
When possible, store everything on the same sheet. It takes longer for Excel to calculate expressions that evaluate values on another sheet. If you’ve already spread your work across several sheets, rearranging everything probably isn’t worth the effort. But keep this one in mind when planning sheets. Keep expressions and references in the same sheet, if possible.
3: Keep it all in the same workbook
Linking to or referencing other workbooks will usually slow things down, even in an uncomplicated workbook. If you can, store everything in the same workbook. Using fewer larger workbooks will be more efficient than using several smaller linked workbooks. When you must use linked workbooks, open them all — and open the linked workbooks before opening the linking workbooks — to improve performance.
4: Clean things up
What you’re not using, delete. Create a backup so you can reclaim functionality at a later date and then delete everything you no longer use. In doing so, you’ll minimize the used range. To determine the used range, press [Ctrl]+[End]. Then, delete all rows and columns below and to the right of your real last used cell. Then, save the workbook.
5: Convert unused formulas
If you’re still referring to derived values (the results of formulas), #4 isn’t feasible. You can, however, convert the formulas to static values. But only do this if you’re sure you will never need to recalculate the formulas that generated the values in the first place. To convert formulas to their static values, use Paste Special and select Values to paste. Doing so will overwrite the formulas with the results of those formulas. Be careful, though. The formulas really will be gone. Create a backup first, just in case.
6: Avoid multiple volatile functions
A volatile function recalculates every time there’s a change in the worksheet, and that slows things down. An efficient alternative is to enter the volatile function by itself and then reference that cell in your formulas. The function will still calculate as expected, but only once instead of hundreds of times. Examples of volatile functions are RAND(), RANDBETWEEN(), NOW(), TODAY(), OFFSET(), CELL(), and INDIRECT().
7: Avoid array formulas
Gurus and power users alike love arrays, and they are a powerful tool. Unfortunately, they’re memory hogs. It might be hard to believe, but a couple of regular formulas will calculate faster than their equivalent array. If helper formulas aren’t adequate, consider a user-defined function. In addition, you might be able to replace arrays with new functions, such as SUMIF(), COUNTIF(), and AVERAGEIF. (Array formulas perform somewhat better in the Ribbon versions of Excel.)
8: Avoid monster formulas
The performance killer in most workbooks is the number of cell reference and operations, not the number of formulas. Throw in some inefficient functions and you can slow things down enough that users will complain. Two or three helper formulas are almost always more efficient than one super colossal formula.
9: Use ISERROR() to update old error-masking formulas
If you’ve upgraded to a Ribbon version of Excel, you can replace most of your convoluted IF() masks with the IFERROR() function:
=IFERROR(expression,actioniferror)
This function is more efficient than the pre-Ribbon solution of using IF() in the following form:
=IF(ISERROR(expression),trueaction,falseaction)
If you’re still working with a pre-Ribbon version, consider a helper formula (#8). Two columns of simple formulas will be more efficient than a single column of IF() functions.
10: Limit conditional formats
Many techniques rely heavily on conditional formatting, but sometimes at a cost. Every conditional format is evaluated every time the workbook performs calculations. Use conditional formatting wisely, and sparingly. Too many conditional formats will slow things down.