On the ‘Excel Options’ window, go to the ‘Formulas’ section and tick the ‘Enable iterative calculation’ box. Click ‘OK’ to save the changes. After that, you will not get any warning whenever there’s a circular reference. However, we strongly advise you not to do this. The same result can be achieved by using the CONCATENATE function, and the following tutorial explains all the details: How to combine text strings, cells and columns in Excel. Reference operators in Excel formulas and functions. To supply rages to MS Excel formulas and separate arguments in Excel functions, the following operators are used. If you mean the Error message that pop-up when a circular reference happens like on this screenshot: You may try checking the Enable iterative calculation via File Options then Formulas. Once the option is enabled, the error window will not be displayed every time a circular reference happens.

Sep 21, 2013 First turn off Iterative calculations in Excel Options.Formulas so that you see the circular cell at the bottom left of the Application next to 'Ready' Then go to that cell and start working out where the dependencies are by using the Formula Auditing tools, Trace Precedents & Trace Dependents. Jan 23, 2014 A circular reference can have a significant impact on performance because it can iterate indefinitely. Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. By default, iterative calculations are turned off in Microsoft® Excel®. You can handle a circular reference by doing one of the following.

Turn Off Circular Reference In Excel 365

• Points

  24

  Trophies

  1

  Posts

  6

  I have an intential Circular Reference in a spreadsheet.

  
  

  However, when a user opens the spread sheet, they get a circular reference warning box and it freeks them out

  
  

  see this
  [Blocked Image: http://66.193.175.250/~howard/excel001.gif]

  
  

  Is there a way to disable this message when the spreadsheet it opened?

  
  

  Thank you

  1. Navigation
  2. OzGrid
  3. Forum
  4. Members
  5. Options
  6. Current Location
  This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies.

  Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.

While using the IF function, Vineet wants to retain the old value in the cell if the condition is false. In other words, the value in a cell where the IF function is used should change only if the condition being tested by the IF function is true. By default, however, the IF function makes the value 0 if the condition is False.

The IF function can take up to three parameters. The first parameter is the comparison that is to be made, the second parameter is what should be returned if the comparison is true, and the third is what should be returned if the comparison is false. It is possible to leave off the last parameter, but if you do then Excel will return the value 0 if the comparison is false. (This is what Vineet is seeing returned by his IF function usage.)

The obvious solution, then, is to make sure that you provide the IF function with something that should be returned when the comparison is false. For instance, let's say that your formula is in cell B1 and you are comparing something in cell A1. The formula you use may look like this:

Note that the words 'under ten' are returned if the value in A1 is less than 10. If this condition is not met, then the value in B1 is returned. Since this formula is in cell B1, this means that the previous value in the cell is returned if the condition is false.

It also means that the formula contains a circular reference. For circular references to work OK you need to let Excel know that it is OK for them to occur in your worksheet. Follow these steps if you are using Excel 2010 or a later version:

1. Display the File tab of the ribbon and then click Options. Excel displays the Excel Options dialog box.
2. At the left side of the dialog box, choose Formulas. (See Figure 1.)

Figure 1. The Formulas tab of the Excel Options dialog box.

3. Make sure the Enable Iterative Calculation check box is selected.
4. Click OK.

Excel Turn Off Circular Reference Notification

If you are using Excel 2007, choose Tools | Options | Calculation tab and make sure the Iteration check box is selected. Excel will now allow the circular reference without complaint.

Turn Off Circular References In Excel

If you don't want to allow a circular reference in your worksheet, then the only recourse is to create a macro that updates the value in cell B1 based upon any changes to cell A1:

This simple macro, when added to the ThisWorkbook module, is executed every time there is a change in the workbook. If the value is cell A1 is changed (and only that cell), then the value is checked to see if it is less than 10. If it is, then the value in cell B1 is changed. If it isn't, then the value in cell B1 is left alone.

There is one 'gotcha' that you need to keep in mind with any of the approaches discussed thus far, formula or macro. If the value in cell A1 is (let's say) 15, then cell B1 will contain what was there before, whatever it was. If you change the value in cell A1 to (let's say) 7, then B1 will change to 'under ten.' That's fine, but from that point on cell B1 will never appear to change. Why? Because if you then change cell A1 to a value greater than 10, cell B1 will contain (as just explained) what was there before. And, as you now understand, the value that was there before is the result of the previous true result, which was 'under ten.' Thus, true or false, the formula or macro from this point on displays the text 'under ten.'