Circular Reference in Microsoft Excel

In this article, we will learn about circular reference.

Circular reference can be a formula that refers to its own cell value, or refers to a cell dependent on its own cell value.

In simple words, the cell in which formula is being calculated & the cell itself is also included or part of the range then a circular reference warning message will appear. It might cause for the incorrect calculation by the formulas.

 

Let us take an example:

  • In cell A1 & A2, numbers are entered & in cell A3 we want to find out the total.

img1
 

  • While typing the sum formula, the range is stretched to cell A3 in which formula is entered.
  • Excel will pop-up an error message

img2
 

If above shown error message regarding circular reference pops up while adding formulas, then you might have created circular cell reference unknowingly. You can track & then remove the incorrect reference & it will remove the circular reference.
 
To track circular reference, follow the below steps:

  • Click on blank cell
  • Click on Formulas ribbon
  • In Formula Auditing group, click on Error Checking
  • Select Circular References & it will provide the location of the cell in which circular reference occurs.

img3
 

  • If there are multiple circular references, then you are required to manually go to each cell & correct the formula until the status bar no longer shows the word “CIRCULAR REFERENCES”

img4

 

Below you can find more examples:-

How we can see circular reference
 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube