Excel 2003

For example, when the formula =B1+B3 is entered into B3, it creates a circular reference; the formula in B3 repeatedly recalculates because, each time it is calculated, B3 has changed. Remove all formula references but keep values in cells with copying and pasting. Please do as follows to remove all formula references but keep values in specified cells in Excel. Select the cells you need to remove all references, then press Ctrl + C keys, keep these cells selected, right click and select Values under Paste Options section. You don't want to turn of the message unless you are doing this on purpose. Normally circular references are considered an error, but there are a couple of ways to use them deliberately. You get rid of them by starting at the cell noted in the status bar (bottom of screen) and walking the formula back through its references to find the circular.

1. Open the 'Tools' drop-down menu at the top of the screen if the 'Circular Reference' toolbar is not displayed, then click 'Customize.'
2. Click on the 'Toolbars' tab at the top of the window that appears, then check the 'Circular Reference' check box if it is not already checked. Click the 'OK' button and close the window.
3. Click on and highlight the first cell in the 'Navigate Circular Reference' box within the 'Circular Reference' toolbar.
4. Examine the formula in the highlighted cell carefully for errors; i.e., referencing the cell itself. If you find no such mistake that could be the cause of the circular reference message, click to the next cell in the 'Navigate Circular Reference' box. (If the word 'Circular' is shown in the status bar without referencing which cell, switch to another worksheet.)

How To Turn Off Circular Reference In Excel For Mac Shortcut

5. Continue through each cell, reviewing and correcting any circular references present in each until the status bar no longer shows the word 'Circular.'

How To Turn Off Circular Reference In Excel For Macs

Excel 2007
6. Click on the 'Formulas' tab at the top of the window.
7. Click on the arrow on the 'Error Checking' button in the 'Formula Auditing' box.
8. Select the first cell listed in the submenu under 'Circular References.'
9. Examine the formula in the cell closely and look for self-references. If you find no cause of the circular reference in the cell, click to the next one in the 'Circular References' submenu.
10. Repeat the previous step and review each cell in turn until the words 'Circular References' disappear from the status bar.

Circular references and calculation settings

If you want to work with circular references, the calculation settings of Excel are very important. This page gives you some pointers!

Calculation settings

The first thing that needs to be done if you want to assure your model works, is to turn on iterative computation of the file.

Iteration settings in Excel 2010

It is up to you to decide how many iterations you want Excel to do before it stops, or what precision you need before Excel stops (whichever comes first). As soon as you check the box 'Enable Iterative calculation', Excel will do a calculation of your model. After saving the file, if you open the file again you should no longer get the circular reference warning message.

If you are troubleshooting your calculation, set Maximum Iterations to 1. This gives you the opportunity to step through the calculations one at the time by repeatedly hitting the F9 key.

Which calculation settings apply

I often get this question: I have checked the 'Enable Iterative calculation' box on my file. Why do I still get the circular reference warning? To be able to understand what causes this it is important to know how Excel handles its calculation settings.

Application wide settings

Calculation settings are application-wide. That is, if workbook A needs manual calculation and workbook B needs automatic calculation and you have both workbooks open, Excel's current setting will apply to both workbooks. The same goes for the iterative calculation settings: they apply to all workbooks in your Excel session.

When you save a workbook, whichever calculation setting was applied at that time is saved with the workbook.

First-come first-serve

Excel will apply the calculation settings of the first workbook you open in a session. So if you first open workbook A (which had iteration disabled when it was last saved) and then Workbook B (with iteration enabled when it was last saved), Excel will keep iteration disabled. This explains why you do get the circular reference warning on that workbook.

How To Turn Off Circular Reference In Excel For Macbook Pro

Warning: When you save your workbook, the calculation settings that are currently in effect are saved with the file. This means that if you have previously set up iterative calculation and the max iterations and max change, these settings may be overwritten with the current settings.

Making sure you have the calculation settings you need

There are several ways to ensure your workbook calculates as expected:

Always open as the first workbook

Well, that one is obvious enough. Of course if your model is used by other people as well, this is not exactly fool-proof. Your users would have to be made aware of this situation, but chances are very high your calculation settings will get overwritten at some point, making your model unreliable. And even if you're the sole user, this is a big risk.

Use a bit of VBA to control calculation settings

A more reliable way to control the calculation settings is by adjusting them when your workbook loads. This means you will have to add macro's to your file, but this is straightforward enough.

I assume the file with the circular references is already open.

Open its ThisWorkbook module by double-clicking on it in the project explorer as shown below:

The Project explorer in the VBA Editor

How To Allow Circular Reference In Excel Mac

Paste this code into the code window that opens up and modify the calculations settings so they match what you need.

How To Turn Off Circular Reference In Excel For Mac Os

Now save your file (if you are using Excel 2007 or up, make sure you change the file-type to one that can hold macro's, otherwise the macro code is discarded after you close your file!)