EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Excel Circular Reference

Excel Circular Reference

Madhuri Thakur
Article byMadhuri Thakur

Updated August 16, 2023

Circular Reference in Excel

Circular Reference in Excel

Circular Reference in Excel is a kind of alert message we can see when selecting the values as a Circle in any formula. Rectifying circular references is important; otherwise, our applied formula will give us the incorrect value.

For example, if we sum the value of 5 cells into the 6th cell and mistakenly consider the range of 6 cells, including the cell where we will see the output, then although we would get the output, it will be an incorrect value. We can enable or disable Circular reference from Excel Options’ Formulas section.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Use Circular Reference in Excel?

Using It is very simple and easy. Let’s understand the situation of the Circular Reference error with some examples.

You can download this Circular Reference Excel Template here – Circular Reference Excel Template

Example #1

  • As we can see, Cell A4 and A5 have some value, and in Cell A6, we have to add those values, including the value in Cell A6 itself.

Circular Reference Example 1-1

  • So we enter the formula like the below screenshot.

Circular Reference Example 1-2

  • When we hit the enter key, it will display an error message, as shown below.

Circular Reference Example 1-3

  • If you click OK or close the window by clicking the cross button, Excel will take it as a Zero value or the last calculated value in the cell.
  • If you click on Help, you can get more information.
  • We have clicked OK, which will display 0 in cell A6. ( Refer to the below screenshot )

Circular Reference Example 1-4

You might be surprised that Excel will sometimes complete the formula with the last calculation having a Circular reference condition without displaying an error message and returning the value in the cell.

In some conditions, we might require to create a circular reference while calculating. Let’s take an example to create that scenario where circular reference must be included.

Example #2

Suppose you go to a restaurant and have lunch. You get the bill which you have to pay before leaving. The GST ( Goods and Service Tax ) on total expenses is 10%, so we must calculate the total bill.

Circular Reference Example 2-1

Here, we will apply the formula in the below screenshot.

CR Example 2-2

Here we will sum up all the Item prices and calculate the 10% on that amount. Then will add this tax amount to the total item’s price. It will give you the total expenses.

But here, Circular reference is a part of the formula; it won’t calculate the total bill. So in this situation, we need to make some changes in Excel settings for further calculation.

This scenario will display an error message, as shown in Example 1 ( Figure 3 ).

Find Circular Reference in Excel

Follow the below steps :

  • Go to the FILE tab.
  • Click on Options in the left pane window. Then, refer to the below screenshot.

CR Example 2-3

  • It will open a dialog box for Excel options.

CR Example 2-4

  • Click on the Formulas tab.

CR Example 2-5

  • Tick on the “Enable iterative calculation” checkbox, as shown in the screenshot below.
  • Click on OK.

CR Example 2-6

  • It will calculate the Total Bill in Cell B15. Refer to the below screenshot.

CR Example 2-7

By enabling this Iterative calculation option, it will repeat the re-calculation until a specific numeric condition is met.

There are two ways to turn on this option.

We can see two options available under the Enable iterative calculation section, as shown in the screenshot below.

Enable Circular Reference

  • Maximum Iterations: As we can see here, the default value is 100. This means Excel will repeat the calculations 100 times and try to solve this.
  • Maximum Change: Excel will try to repeat the calculation 100 times and stops when it finds the difference is not more than 0.001 between iterations.

Things to Remember

  • We should avoid Circular reference, the reason being, it leaves an impact on the performance and displays a warning message every time at the time of opening the workbook.
  • It can also be the reason for other issues that are not immediately visible.

Recommended Articles

This has been a guide to Circular Reference in Excel. Here we discuss finding and using a circular reference in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. SUM Function in Excel
  2. Relative Reference in Excel?
  3. Excel Absolute Reference
  4. Compare Dates in Excel
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Circular Reference Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Circular Reference Excel Template

EDUCBA

डाउनलोड Circular Reference Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more