While Excel is a powerhouse for data analysis and manipulation, complex workbooks with tons of formulas can slow down your workflow in no time. After all, nobody wants to wait around for their calculations to finish, and these slow formulas can be a major productivity killer. The good news is that you don’t have to suffer through it. There are some proven techniques to optimize your formulas and dramatically speed up your Excel performance.

Whether you are a spreadsheet guru or just starting out with Excel, these tips will help unlock a new level of efficiency in your spreadsheets.

8 Skip volatile functions

There is no shortage of Excel functions out there. However, among them, you need to be careful with volatile functions. They recalculate every time any cell in the workbook changes. This constant recalculation can put a significant strain on your spreadsheet.

NOW, TODAY, RAND, OFFSET, INDIRECT, etc., are some of the formulas that you need to avoid in large workbooks with complex formulas. Suppose you have a spreadsheet tracking project deadlines. If you use NOW(), every time you enter data, change a format, or just scroll through your sheet, Excel will recalculate the formula.

For example, instead of using =A5-NOW() formula to calculate a deadline, you can use =A5-$E$1where the E1 cell has the current date entered.

7 Refer to specific cell ranges

This is another neat trick to speed up your Excel formula. When you use a B:B formula, you are essentially telling Excel to look at every single cell in the B column. It’s time-consuming and consumes resources.

Let's say you have a sales report with data in columns A to D, and you want to sum the total sales in column C. For example, instead of using a formula like =SUM(C:C), you can use =SUM(C1:C100) to reduce the calculation load. Overall, you need to be precise with your cell references in order to get the job done in no time.

6 Break down complex formulas

Complex formulas can be difficult to troubleshoot. You need to break them down to understand, manage, and optimize things easily. You can also use helper columns to simplify spreadsheets and make them more efficient. Instead of cramming all your logic into one massive formula, create a helper column to divide the task into smaller, more manageable steps.

5 Optimize lookup functions

Lookup functions like VLOOKUP, HLOOKUP, and XLOOKUP are essential tools in Excel for finding and retrieving data. However, they can be intensive, especially when dealing with large databases.

Among them, XLOOKUP is the most efficient and versatile of the bunch. It offers exact or approximate matching and searches in any direction (rows or columns), then returns multiple values. If you have access to XLOOKUP, prioritize it over older functions like VLOOKUP and HLOOKUP.

Besides, instead of referencing ranges like A1:C100, create named ranges (e.g., "Products"). This makes your formulas more readable and can improve performance.

4 Be careful with excessive formatting

While it’s important to format your Excel spreadsheets for a professional look, you shouldn’t go overboard with it. It can impact overall spreadsheet performance. Every formatting element you apply, whether it is font color, border, or conditional formatting rule, adds to the overall file size.

After all, Excel needs to process all the formatting information along with your formulas and data. On the flip side, I’m not suggesting that you strip your Excel workbook from every formatting style either. It would look bare bones, and doesn’t leave a good impression. You need to strike a balance between visual appeal and performance by being mindful of your formatting choices.

3 Choose your functions carefully

As I mentioned, you need to avoid volatile functions and think twice while dealing with LOOKUP formulas. You should also skip array formulas. While they are powerful, such formulas can be computationally intensive. You can use alternatives like SUMIFS or COUNTIFS for multiple criteria.

Before you start with a formula, make sure to clearly define what you want to achieve with it. For example, instead of using a long, nested IF formula to assign student grades based on scores, use VLOOKUP with a grade threshold. It’s more efficient, readable, and easier to maintain.

2 Revert to manual calculation

Excel offers several calculation options that can significantly impact the performance and behavior of your spreadsheets. By default, it’s set to automatic calculations, where Excel recalculates all formulas whenever you make a change to the workbook.

Instead, you can revert to manual calculation, where you can control the workbook behavior. The spreadsheet only calculates when you press F9 or click the ‘Calculate Now’ button. It is ideal for large workbooks with complex formulas.

1 Explore DAX formulas for large databases

When you deal with a large database that requires complex analysis, it is worth exploring the power of DAX formulas within Power Pivot. It’s specially designed to manipulate data in analytical databases like Power Pivot and Power BI.

DAX stores data in columns rather than rows. This structure allows for faster data retrieval. With DAX’s range of functions, you can perform complex calculations and intelligent analysis, which may be insufficient in regular Excel.

Speed up your spreadsheets

And there you have it. Implement the tricks above and leave those frustrating calculation delays in the dust. Not only can you unlock a faster Excel experience, but you can also become an efficient spreadsheet user. Aside from these tips, you should also explore the built-in automation techniques to fly through your workbooks.