Circular Reference Warning

One or more formulas contain a circular reference and may not calculate correctly. Circular reference are any refereces within a formula that depend upon the results of that same formula. For example, a cell that refers to its own value or a cell that referes to another cell which depends on the original cell's value both contain circular references.

For more information about understznding, finding and removing circular references, click ok. if you want to create a circular reference, click cancel to continue.

Ad

I have a value in cell E1 say 2500000. I now enter the formula =E1/1000000 the result is a circular reference. How to remove it ?

Posted on Apr 06, 2010

Ad

Clicking cancel will invalidate the formulae, circular references refer to the dependant and precedent cells using each other.

Track/ Audit depending on your Excel version to show which formulae is incorrect - or options view, tick show formaulae and remove them.

Posted on May 22, 2009

Ad

Hi,

A 6ya expert can help you resolve that issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

The service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

Good luck!

Posted on Jan 02, 2017

first start excel and click on formula tab -->Calculation options -->Manual

Now open file, if asks for update formulae, say no.

Now select entire sheet, select formulas tab--> Error Checking -->Trace Error.

It will locate all error containing cells, now rectify the error save it reopen in excel to see if problem is solved.

Now open file, if asks for update formulae, say no.

Now select entire sheet, select formulas tab--> Error Checking -->Trace Error.

It will locate all error containing cells, now rectify the error save it reopen in excel to see if problem is solved.

Apr 12, 2017 | Microsoft Excel for PC

Correct a #N/A error
Show All
Hide All
This error occurs when a value is not available to a function or formula.

- Optionally, click the cell that displays the error, click the button that appears , and then click
**Show Calculation Steps**if it appears. - Review the following possible causes and solutions.
Missing data, and #N/A or NA() has been entered in its place

Replace #N/A with new data.

**Note**You can enter**#N/A**in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.

Giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function

Make sure that the lookup_value argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) is the correct type of value — for example, a value or a cell reference, but not a range reference. Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table

By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_lookup argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE. The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the match_type argument. To find an exact match, set the match_type argument to 0.

Using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula

If the array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) has been entered into multiple cells, make sure that the ranges referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range 15 rows high (C1:C15) and the formula refers to a range 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15).

Omitting one or more required arguments from a built-in or custom worksheet function

Enter all arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function.

Using a custom worksheet function that is not available

Make sure that the workbook that contains the worksheet function is open and the function is working properly.

Running a macro that enters a function that returns #N/A

Make sure that the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) in the function are correct and in the correct position.

Oct 31, 2008 | Computers & Internet

open Excel

Click the 'Microsoft Office' button and click 'Excel Options'. Click the 'Formulas' button. From 'Calculation options' section, un-select the 'Automatically' option. then use file->open->yourfile.xlsx. It should now open. Find the circular reference and fix it. Then save the file.

Click the 'Microsoft Office' button and click 'Excel Options'. Click the 'Formulas' button. From 'Calculation options' section, un-select the 'Automatically' option. then use file->open->yourfile.xlsx. It should now open. Find the circular reference and fix it. Then save the file.

Mar 18, 2013 | Microsoft Office Excel 2007 Full Version...

You can trick Excel into allowing the circular reference by using the steps provided in this other Fixya post here:

http://www.fixya.com/support/t1207484-reopen_circular_reference_file_in_excel

http://www.fixya.com/support/t1207484-reopen_circular_reference_file_in_excel

Jul 19, 2012 | Microsoft Excel for PC

A couple of possible causes:

Setting Workbook.ForceFullCalculation to True will cause this.

The Iteration Option is turned on and the workbook contains circular references. Turn off Iteration (Tools-->Options-->Calculation) and press F9-- it may show "Circular Reference."

Setting Workbook.ForceFullCalculation to True will cause this.

The Iteration Option is turned on and the workbook contains circular references. Turn off Iteration (Tools-->Options-->Calculation) and press F9-- it may show "Circular Reference."

Jun 20, 2011 | Microsoft Office Professional 2007 Full...

what is the formula?

Jul 08, 2009 | Microsoft Excel for PC

The "circular reference" indicates that the xref you are pulling in is also xrefing the drawing file you are in which creates this circular reference to each other. The error message is only a warning and does not prevent you from working and saving changes. To get rid of the error message, close the primary drawing file and then open the reference drawing and DETACH the xref that is linked to your primary drawing. Save the changes and then open the primary drawing file again to see if the error was resolved.

Mar 16, 2009 | Autodesk AUTOCAD LT 2008 SLM 10 SEATS...

- Click the
**Microsoft Office Button**, click**Excel Options**, and then click the**Formulas**category. - In the
**Calculation options**section, select the**Enable iterative calculation**check box. - To set the maximum number of times that Excel will recalculate, type the number of iterations in the
**Maximum Iterations**box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet. - To set the maximum amount of change you will accept between calculation results, type the amount in the
**Maximum Change**box. The smaller the number, the more accurate the result and the more time that Excel needs to calculate a worksheet.

Nov 05, 2008 | Microsoft Excel for PC

Hi shamsherpate

**In order to remove this error message you need to locate and remove the incorrect references:**

On the Formulas tab, in the Formula Auditing group, click the arrow on the Error Checking in-group button, point to Circular References, and then click the first cell listed in the submenu.

Tip You can move between cells in a circular reference by double-clicking the tracer arrows.

Review the formula in the cell. If you cannot determine whether the cell is the cause of the circular reference, click the next cell in the Circular References submenu.

Note The status bar displays the word "Circular References" followed by a reference to one of the cells contained in the circular reference.

Continue to review and correct the circular reference until the status bar no longer displays the word "Circular References."

Regards,

TheAssembler

On the Formulas tab, in the Formula Auditing group, click the arrow on the Error Checking in-group button, point to Circular References, and then click the first cell listed in the submenu.

Tip You can move between cells in a circular reference by double-clicking the tracer arrows.

Review the formula in the cell. If you cannot determine whether the cell is the cause of the circular reference, click the next cell in the Circular References submenu.

Note The status bar displays the word "Circular References" followed by a reference to one of the cells contained in the circular reference.

Continue to review and correct the circular reference until the status bar no longer displays the word "Circular References."

Regards,

TheAssembler

Jun 06, 2008 | Microsoft Excel for PC

Jan 28, 2016 | Microsoft Excel for PC

Dec 12, 2013 | Microsoft Excel for PC

Jul 23, 2013 | Microsoft Excel for PC

1,311 people viewed this question

Usually answered in minutes!

Circular Reference and may not calculate correctly. Circular references are any references within a formula that depend upon the results of that same formula. For example, a cell that refers to its own value or a cell that refers to another cell which depends on the original cell's value both contain circular references. For more information about understanding, finding, and removing circular references, click OK. If you want to create a circular reference, click Cancel to continue.

Circular Reference Warning

One or more formulas contain a circular

reference and may not calculate correctly. Circular reference are any

refereces within a formula that depend upon the results of that same

formula. For example, a cell that refers to its own value or a cell

that referes to another cell which depends on the original cell's value

both contain circular references.

For more information about

understznding, finding and removing circular references, click ok. if

you want to create a circular reference, click cancel to continue.

Which version of Excel are you using?

Which cell (A5, B7, etc), and what is the exact formula in the cell?

×