Speed up Excel formulas – Are Excel formulas taking a long time to run?

Let’s discuss how to optimize & speed up Excel formulas today. Here are some tips & ideas you can use in order to speed up your sluggish workbook. Unquestionably, to optimize your complex worksheet models & make them faster, follow the tips outlined in this post.

1. Using tables to hold the data

Excel 2019 allows you to keep all related data in a table. Evidently, a table is used to store data from call centers. As a matter of fact, the tables can be used as a source for pivot tables, in formulas with structural references, etc. Henceforth, the tables expand and shrink as you add and remove data none of your Excel formulas need to be dynamic. Likewise, if you have a table called cs, the formula sum(cs[column_name]) refers to the sum of all values in the column_name, after all, the formula still works if you add more data to CS.

Here are some resources to help you learn about Excel tables – Speed up Excel Formulas:

• What are Excel Tables and how do I use them?
• Transposing in Excel: how to do it?
• How to use Excel functions?
• How to use Excel shortcuts?
• Excel Tutorial

2. How to use named ranges, and named Excel formulas?

By using named formulas and names, a spreadsheet can be simplified. In addition to storing intermediate results, named formulas can hold arrays (i.e. lists of values). As a result of reducing formula overhead, your workbooks will run faster.

Learn about named ranges & named formulas with these resources:

• Use of Sumifs, Countifs, and other Excel formulas
• Logical functions
• If Functions
• How to use Excel functions?

3. How to Use Dynamic Arrays & Spill Ranges?

We can easily build complex calculations using Dynamic Arrays, an Excel 365 feature. Moreover, in all Excel versions, this feature is now a popular one. It would be useful to incorporate new functions like:

• You can use the FILTER to retrieve values that meet a certain set of criteria.
• Sort the values by using SORT
• Eliminate duplicate values automatically with UNIQUE
• A variety of lookups can be performed using XLOOKUP
• Datasets can be combined using VSTACK and HSTACK
• For converting data tables to single rows or columns, use TOCOL/TOROW
• To manage spill ranges, use a spill operator or #

You can learn more about Dynamic Arrays here:

• Advance Filter in Excel
• Data validation feature in Excel
• Flash fill and fill handle

4. Using Pivot Tables

Firstly, it is common for us to use formulas even when we do not need them. As a matter of fact, a pivot table is an excellent way to calculate a large number of summary values in a few clicks. Once you have built the pivot, you can refer to its values using GETPIVOTDATA or a simple cell reference. This will reduce the number of unnecessary calculations. You can refresh all pivots in one go using the DATA ribbon if you are changing the data. When the data is stored in tables, this process runs smoothly.

It is common for workbooks to be slow when there is a large amount of data. However, by using pivot tables, you can speed up your workbooks since they are designed to handle large amounts of data.

Resources to learn Pivot Tables:

• Pivot table in Excel
• Advance Filter in Excel

5. How to sort your data?

Specifically, sluggish performance can be caused by searching through a lot of unsorted data. Excel is trying to find a needle in a haystack. In fact, through data imports, we often inherit unsorted data. When data is sorted and lookup formulas are used correctly, a sluggish workbook can be instantly speeded up. In conclusion with Power Query or a simple VBA macro, also you can automate the sorting process if you find it a pain.

Here are some resources on sorting:

• How to use Excel functions?
• How to use Excel shortcuts?
• Excel Tutorial

6. Using Manual Calculation Mode

The complexity comes at a high cost in the form of speed. Sometimes, however, we want our Excel workbooks to be complex to reflect the real world. In this case, you should set formula calculations to manual mode.

To run the formulas, simply press F9. It is important to note that Excel runs formulas whenever you save the file.

7. Using Non-volatile Excel formulas

In Excel, volatile formulas belong to a distinct category. As soon as there is a change in the workbook, these formulas are recalculated. Among the volatile functions are RAND, NOW, TODAY, OFFSET, etc. All Excel formulas in a worksheet with multiple volatile functions must be recalculated whenever the worksheet is changed. As a result, your worksheet becomes slow.

What’s the solution? It’s simple, don’t use volatile formulas. In addition to OFFSET, you can also use INDEX to construct a dynamic range with INDEX. Indexes are non-volatile, so they tend to be faster. Consider using a table instead.

8. How to keep formulas in a separate sheet? – Speed up Excel Formulas

The formulas are the backbone of any Excel workbook or model. It will minimize the chances of errors, omissions, or repetitions by keeping them all in separate worksheets. Having all formulas in one place makes investigating or debugging slower performance easier. Excel formulas are usually stored on one sheet when I am designing a dashboard or complex spreadsheet. In addition, this structure helps me structure my calculations and Excel formulas.

The following are some guidelines we follow when writing formulas.

• For example, SUMPRODUCT is faster and easier to write than SUMIFS, which is built-in.
• You do not need to refer to the entire column when you need only a few values. In situations where you know there are only values in A1:A10, do not write SUM(A: A).
• If you want to simplify your error checking, use IFERROR instead of long IF(ISERROR formulas.
• Find out why formulas are not working and how to fix them [How to fix formula errors]
• Instead of using old clunky array formulas, use Dynamic Array formulas
• The number of references to other workbooks should be reduced or removed. Make use of Power Query instead.
• Ensure that any named ranges resulting in errors or missing links are removed.
• By coming up with alternative formulas, you will not only sharpen your mind, but you will also discover better solutions.
• If you don’t need something, don’t calculate it.
• Make sure you don’t calculate the same thing twice. The first result should also be used a second time. To accomplish this, use LET.

Here are some resources to help you write better formulas:

• Use of Sumifs, Countifs, and other Excel formulas
• Logical functions
• If Functions
• How to use Excel functions?

9. Other options

There are times when the workbook remains slow regardless of what you do. Whenever I encounter such a situation, we try these whacky alternatives:

• You should replace formulas with values. It is a good idea to take a backup of the formulas. After selecting everything, I press CTRL+C, ALT+ESV (or CTRL Shift V).
• Create the workbook from scratch. Sometimes it is helpful to redesign the workbook from scratch.
• Make sure that external data links are replaced with actual data: If necessary, import data by copy-pasting.
• If possible, reduce the number of features in the workbook to see if the consumer can live without them.
• Try an alternative solution: Trying to do everything in Excel is foolish. Find out if you can use an external tool that will do this more quickly and efficiently.

For writing better formulas, check out these resources – Speed up Excel Formulas:

• What are Excel Tables and how do I use them?
• Transposing in Excel: how to do it?
• How to use Excel functions?
• How to use Excel shortcuts?
• Excel Tutorial