Circular reference what is

The NAME? Frequently, the NAME? Fixing a NAME? This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel. How to fix the NUM! The NUM!

For example, if you try to calculate the square root of a negative number, you'll see the NUM! The examples below show formulas that return the NUM error. How to fix the NULL! The NULL! Technically, the space character is the "range intersect" operator and the How to fix the hashtag error. The "hashtag" error, a string of hash or pound characters like is not technically an error, but it looks like one.

In most cases, it indicates the column width is too narrow to display the value as formatted. You might run into this odd The rectangle that encloses the values is called the " spill range " How to fix the CALC!

With the introduction of Dynamic Arrays in Excel , formulas there is more emphasis on arrays. The CALC! These are very dangerous, because they are hard to detect. For example, because the operation of such a circular reference is dependent on the value of another cell.

Read more about the different types of circular references and how to find hidden circular references using PerfectXL Risk Eliminator.

What is a circular reference A circular reference refers to a formula, that visits its own or another cell more than once in its chain of calculations, creating an infinite loop which slows down your spreadsheet significantly. Example of a Circular Reference. Manually detect Circular References in Excel. Different types of Circular References Most circular references are unintended: mistakes.

This is the desirable condition. The solution diverges, which means that from iteration to iteration, the difference between the current and the previous result increases. The solution switches between two values. For example, after the first iteration the result is 1, after the next iteration the result is 10, after the next iteration the result is 1, and so on.

You can always ask an expert in the Excel Tech Community or get support in the Answers community. Excel Essential Training. Overview of formulas in Excel. How to avoid broken formulas. Find and correct errors in formulas. Excel keyboard shortcuts and function keys. Excel functions alphabetical. Excel functions by category. Need more help? Expand your skills. Get new features first.

Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue.

Clear instructions. Easy to follow.