Quick method to fix Excel loop error

Excel has a loop error when a formula or macro loops indefinitely. That makes the calculation impossible to complete. TipsMake guides on how to handle loop errors, also known as Circular References errors, in Excel. 

What is a loop error in Excel?

Loop errors are a relatively common type of error when using Excel. Occurs when a formula or macro creates an infinite loop that prevents Excel from calculating or processing data as expected. When a loop error occurs in Excel, Excel will notify the error and stop calculating.

A notice board containing the keyword Circular References will appear. Or sometimes it just returns a calculation error of 0.

Cause of loop error

There are a number of common reasons why Excel errors appear such as:

1. Wrong setting or not setting the condition to stop the calculation loop.
2. Using a previously undeclared variable.
3. Errors in formulas contain loops, syntax, or calculation errors.
4. The number of iterations is too large, causing an error or Excel not responding.

How to detect loop errors in Excel

If Excel does not display a loop error message or only returns a value of 0, you need to pay attention to check to determine the error and correct it.

1. Check to see if a formula appears that can create a loop when referring to the cell it is in or creates a loop.
2. With new versions of Excel, try using the error checking tool. (At 'Formulas' tab > 'Error checking'.)
3. Use the tool to check for loop cycles in cells and make repairs.
4. Check if macros are used in spreadsheets because it's easy to create an infinite loop.

Method to fix Excel loop error

Once you have identified and detected the loop error, you can proceed with the repair. To make it easier to understand, TipsMake will provide specific instructions on how to identify errors and fix them for each method.

Method 1: Delete and revise the reference formula

This method applies to the case of unintentional loops.

1. Step 1 : Determine the error cell in Formulas on the Menu bar .
2. Step 2 : Go to  Formula Auditing and select Error checking (error sign).
3. Step 3 : Select Circular References from the drop-down list. At that time, the error will be displayed next to it for you to check and fix.

1. Step 4 : Go to the error cell and proceed to delete or edit the formula.

Method 2: Enable loopback when required

In some cases, you need to use loops to calculate, you need to enable loops. Excel currently allows a maximum number of loops of 32767. Apply the following trick to allow opening loops in Excel!

1. First you need to open the settings in Excel: File > Options.
2. Next, select the Formulas tab , go to ' Calculation options ' and check ' Enable iterative calculation '.
3. Proceed to customize the desired number of loops in Maximum Iterations .
4. Press ' Ok ' to enable looping.

However, this method will depend on whether the spreadsheet formula you are running allows repetition or not.

If you don't want to repeat and let Excel appear an error message, see how to avoid it.

How to avoid Excel having internal loop errors for future use

Avoid loop errors with the IFERROR function: 

The IFERROR function is useful for making a spreadsheet easier to read and avoiding unexpected error messages. This function should be applied when encountering a loop error in the calculation formula.

Function syntax: IFERROR(value, value_if_error)

1. value : value you want to check for errors
2. value_if_error : value to return when an error occurs.

The loop error is zero with the following assumption:

In the example there is A1/B1 where by reference B1 is 0. This formula has a loop error so you can use a function.

=IFERROR(A1/B1, 'Error')

From there, if your formula has error B1 as 0, it will return the value 'Error' instead of displaying the error. Now you can handle errors easily and look more beautiful in the worksheet.

Use macros to fix loop errors:

This method is relatively complicated and you need to be careful and test thoroughly. Therefore, in this article TipsMake will not provide specific instructions. Instead, you should download the latest versions of Excel to perform the first two methods.

So TipsMake has shared ways for you to handle Excel loop errors. Wishing you success in handling the error, leave a comment if you have other more interesting ways.

David Pac

Update 20 December 2023