Speed up Excel Formulas – Practical Tips

Speed up Excel formulasAre Excel formulas taking a long time to run?

1. Using tables to hold the data

Excel Tables are great for holding data

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

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

If
If

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:

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:

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:

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:

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.

Manual calculation mode in Excel Formulas

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:

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:

Related Blogs:

Excel Training Course

12 Most useful formulas in Excel Part 1

12 Most useful formulas in Excel Part 2

32 Most useful shortcuts in Excel

Important Links:

Community to connect for 24 hrs support – BPA Mastery

Blogs – Blogs

Books to Read – Books to Read

Famous Courses At BPA Educators

Pro Accounting Program – Pro Accounting Program

MIS Analyst Course – MIS Executive Course

Excel & Adv Excel Course – Excel & Adv Excel Course

Tally Prime Course – Tally Prime Course

GST & Efiling Course – GST & Efiling Course

Direct Tax & Efiling Course – Direct Tax & Efiling Course

Busy Course – Busy Course

Quick Book Course – Quick Book Course

Business Communication Course – Business Communication Course

IELTS Program – IELTS Program

and many more

Connect With Me:

Facebook Instagram LinkedIn Twitter Youtube Podcast