Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Circular References > Types
Deze pagina in het Nederlands

Working with Circular references in Excel

Types of circular references

Simplistically speaking there are only a few types of circular references to discern:

Deliberate circular references

The most important thing to decide up front is whether the model you are trying to calculate actually is of a circular nature. Very often there is a way to rewrite your calculations in such a way that no circular references are needed. This is always the preferred method to use.

Some people deliberately create (sets of) circular reference chains to have a calculation model do iterative calculations. Examples may include models of a chemical facility where (part of) an input stream of a process is a recycled output stream of the same process. Distillation plants often have such streams and if you want to calculate a model of such plant, using a circular reference may be a good way to model your process.

If -and only if- you are convinced you really need circular references to solve your problem, meticulously document your model, stating clearly what your intentions are and how the model functions. Especially important: Devise a way to clearly indicate the circular reference chain.

There are some sub-types of circular references to discern. I list them here under the deliberate circular references, assuming that is the only case where it could be important knowing which subtypes there are.

Self-referencing cells

The simplest type of a circular reference is where a cell references itself. This is sometimes used when trying to have a value in a cell which only updates under certain circumstances. An example can be the following:

Suppose you want to keep track of when an entry was made in a certain cell, without using VBA. One way could be the use of the TODAY() function, in combination with the IF function:

Self-referencing circular reference
Self-referencing cell

As soon as an entry is made in cell A1, the IF function's test evaluates to false, making the formula in cell B1 point to its own value, being the date of the last time Excel recalculated.

Single circles

When all cells within a circle are not part of any other circle, we have a single circular reference chain. An example of such a single circle is depicted below:

A single circular chain
A single circular chain of cells

Cell C2 uses the value of cell D3, which uses the value of cell C4, which uses the value of cell B3, which in turn uses the value of the first cell, C2, completing the circle.

Multiple circles

To complicate things, cells may be part of more than one circular reference chain. See the example below:

Multiple circles
Multiple circles

In the example above, there are two circles. The first circle is A-B-C-D-E-F-A (green arrows). The second one is A-B-C-D-E-G-A, the red arrows. Detecting such multiple circular chains is very difficult.

Note that circular reference circles can span multiple worksheets and even multiple workbooks (try to avoid that like the plague!). If you need a circular reference, I advise you to try to keep the cells which are included in the circle as close together as you can so they can be viewed on a single screen all at once. This makes troubleshooting and validating your model easier.

Accidental circular references

More often, people inadvertently create a circular reference, for example by having a SUM function which includes the cell the SUM function itself resides in.

Example: In cell A10, you write this function: =SUM(A1:A10)

Of course this formula is intended to sum the values in the cells above the cell with the sum function, so the argument for the SUM function should be A1:A9.


 


Comments

All comments about this page:


Comment by: Thomas Barkow (7/26/2012 7:10:25 PM)

Litte mistake:
Not "The first circle is A-B-C-D-E-F-A (red arrows)."
But "The first circle is A-B-C-D-E-F-A (green arrows)."
Th
(greeting from Cologe, Germany)

 


Comment by: Jan Karel Pieterse (8/7/2012 11:24:39 AM)

Hi Thomas,

Thanks!!!

 


Comment by: Fred Lieberg (12/9/2012 9:55:44 PM)

In my Windows 7 excel workbook I use references to different cells on the same sheets. I get warnings of circular references (my referred cells are not circular references)
Tracing the "errors" shows the refences are not consistent with adjoining cells but they are the ones i want.
I go to each worksheet and indicate the "error" is to be ignored and save the workbook.
When I open the workbook I still get the warning of a circular reference. Using the error checking each worksheet is clear of the warning but it still comes up when I open the workbook.
Code goes here
.

 


Comment by: Jan Karel Pieterse (12/10/2012 8:21:37 AM)

Hi Fred,

Telling Excel to ignore formula errors will not make your circular reference error go away. Circular reference errors are not erros you can tell Excel to ignore (nor will Excel flag them like other formula errors).

Download my reftreeanalyser from www.jkp-ads.com/reftreeanalyser.asp . It will enable you to track the cirular references. The free Demo version should do the trick.

 


Comment by: David Salter (7/19/2013 5:34:55 PM)

I get the Circular Reference warning when I tried to open an XL spreadsheet. It then shows no data but at the bottom I see:

Ready Circular Reference: H44

How can I proceed? My file shows it has 247 kB and it took a lot of time to develop.

 


Comment by: Jan Karel Pieterse (8/8/2013 9:12:40 PM)

Hi David,

You can download my RefTreeAnalyser to locate the circular references:

www.jkp-ads.com/reftreeanalyser.asp

 


Comment by: Konstantinos (2/11/2014 12:56:56 AM)

I've searched around for circular reference but i cannot find a solution to my problem anywhere.
Can you please let me know if there is a way to achieve what i want.
Here is my problem.
let's say we have cell A2
in it is the value 5
but it's wrong and the right number is 1
instead of just typing 1, i want a function because there are thousands of cells that need this type of correction.
the function i thought is 6-(the value of the cell)
but of course if you enter this it doesn't work and gives the circular reference error.
Even if you go to excel options, and in formulas tab you enable iterative calculations with only 1 iteration, still i cannot solve the problem, because the moment i start writing the function in the cell, the value of the cell (which is 5) is lost.

just for reference, and in case it helps.
the wrong cells have values that range from 1 to 5, and the correct values should be 5 to 1, meaning, 5->1, 4->2, 3->3, 2->4 and 1->5
that's why i thought about the function 6-(the number in the cell).

thank you in advance,
I'll be looking forward to your answer.

with regards,
Konstantinos

 


Comment by: Jan Karel Pieterse (2/11/2014 10:39:33 AM)

Hi Konstantinos,

A simple way is like so:

In an additional empty column use a formula like:
=6-A1
Copy down to match column A's number of rows with data
Select formula cells, copy, select column A, paste special values. Remove formula column.

 


Comment by: Konstantinos (2/19/2014 11:15:17 PM)

First of all i would like to thank you for taking the time
to reply and give me an answer.

i have 270 rows and 300 columns, and the faulty columns with the cells are scattered in there. so this means that i should do that for every column, which is around 130-150 columns! but i guess it's still quicker than going to each cell and correcting the value!!

PS: another similar kind of solution is to write the value 6 on one cell, copy it, then pick all the faulty cells and paste special with subtract, then on another cell write the value -1, pick all the faulty cells again and paste special multiply.
don't know which is faster, but at least now i have some options!
I hope anyone with a similar problem can stumble upon this page and save himself some time.

once again thank you for your help! it was really valuable!

with regards,
Konstantinos

 


Comment by: Jan Karel Pieterse (2/20/2014 9:18:29 AM)

Hi Konstantinos,

I was going to suggest to do the paste special trick, but I see you already have discovered that. Is there any way to recognise the faulty cells? Perhaps a bit of VBA code can be of help.

 


Comment by: Konstantinos (2/20/2014 9:47:11 AM)

Hello again Jan,

well i know which columns are the ones with the faulty cells (wherever there is a faulty cell, all the cells in that column are faulty).
So for example i know that columns AX, JH, JI, JK, KA, etc. are the ones with the faulty cells (148 columns in total).

If there was a way into vba code, that you could distinguish between old value of a cell and new value, that could help, you could tell it "New Value" = 6 - "Old value"
but that is just thoughts, i don't know how exactly vba works.
if you can think of some code please let me know. it would be helpful.
Thank you for the interest in my problem and the help so far!

with regards,
Konstantinos

 


Comment by: Jan Karel Pieterse (2/20/2014 1:43:02 PM)

Hi Konstantinos,

Is there some characteristic through which I would be able to "see", which those columns are? LIke a specific color in a cell or a specific value?

 


Comment by: Konstantinos (2/20/2014 1:57:25 PM)

Not really, no.
But i could pick them and color them red (all the faulty columns), of course that would be the same as picking them up in the aforementioned solutions.
Also there is no characteristic that could set them apart from the others, because all the columns have numbers from 1-5, and just for the faulty columns the numbers need to be reversed.

 


Comment by: Jan Karel Pieterse (2/20/2014 6:51:29 PM)

Hi Konstantinos,

You could use this macro:

Sub UpdateColumns()
    Dim vRanges As Variant
    Dim vValues As Variant
    Dim lRow As Long
    vRanges = Array("A:A", "B:B", "C:C")
    Dim vRange As Variant
    For Each vRange In vRanges
        vValues = Intersect(ActiveSheet.UsedRange, Range(vRange)).Value
        For lRow = 1 To UBound(vValues, 1)
            vValues(lRow, 1) = 6 - vValues(lRow, 1)
        Next
        Intersect(ActiveSheet.UsedRange, Range(vRange)).Value = vValues
    Next
End Sub

Make sure you change the A:A, B:B, C:C to your column letters before running the macro. Also: the macro assumes you use ONE column at the time, so DON'T use A:Z to do 26 columns, each one has to be done in turn, like in the example.

 


Comment by: Konstantinos (2/21/2014 3:18:46 PM)

Thank you very much Jan!
i'll try that!
you've helped a lot! I'm glad i found this article.
regards,
Konstantinos

 


Comment by: Dev (5/23/2014 12:57:45 PM)

I am using circular reference in more than one cell in same sheet but my problem is that one circular reference is affecting other formula why? Is it not possible to use more than one circular reference? please help me i am stuck.

 


Comment by: Jan Karel Pieterse (5/23/2014 1:22:28 PM)

Hi Dev,

Sure, you should be able to have multiple circles. But everything depends on how they are set up. Can't really asnwer your question without more information.

 


Comment by: Dev (5/23/2014 6:08:03 PM)

thank you Jan for ur response..... i want to calculate the number of rupee denomination in bank every time i receive the money because i am a cashier...example in C1 cell i will put 1 then D1 cell should display 1 and again i will enter 4 in C1 then D1 should display 5 ( 1 previous value plus 4 new value) and so on it should display other summed value whenever i put new value in C1. This is hapenning when i am trying but when i am applying same formula in C2 cell for other denomination whose result is showing in D2 the strange result is showing in D1 cell i.e previous result. So my question is why one circular reference in D2 cell is affecting in D1.

I hope you have now understand my problem and waiting your response.

 


Comment by: Jan Karel Pieterse (5/24/2014 4:59:14 PM)

Hi Dev,

Rather thn doing it in a difficult way I would be pragmatic and enter each amount you receive in its own cell beneath each other and simply use =SUM(C1:C100) to calculate the total.

 


Comment by: Dimitrios (7/8/2014 1:47:23 PM)

Hello, I would like to ask, if there is a way to make excel do the calculations simultaneously. What i mean...

lets say we have 5 cells

a     b         c         d         e
100 (a+c)/2 (b+d)/2    (c+e)/2     0

i want the first calculation in excel to be

100    50     0         0            0

but exel calculates each cell seperately and i get

100    50        25         12.5     0

 


Comment by: Jan Karel Pieterse (7/9/2014 10:36:21 AM)

Hi Dimitros,

I'm not sure I understand what you are trying to do, can you perhaps elaborate?

 


Comment by: Alaa (11/27/2014 9:38:56 PM)

i receive money every day and i want to sum the money daily till the end of each month ...example: in the 1st day of November i will put 100 in C1 cell then D1 cell should display 100 and in 2nd day of November i will enter 300 in C1 then D1 should display 400 (100 value of yesterday plus 300 value of today) and so on.., SO i want D1 cell display other summed value everyday i put new value in C1 till the end of November (At the end of November, D1 should display the total money i received in November)
What the calculation i can write in C1 cell and D1 cell for this issue ?

Thanks in advance

 


Comment by: Jan Karel Pieterse (11/28/2014 1:26:52 PM)

Hi Alaa,

WHile this is theoretically possible using circular references, I would not recommend doing this. I would simply enter the numbers below eqach other in a small table and add the column of that table. That way you also keep a record of the numbers you enetered in the past.

 


Comment by: Alaa (11/28/2014 2:59:01 PM)

Thanks Jan for your reply but I have about 15 sales rep each one have target for sales and collecting money, so I have to enter what they sale and collect everyday in column called "achieved today" and in next column I want to calculate "achieved till now"
And if I made 30 columns for each day of the month, the sheet will be very complicated to supervisors whose using this sheet specially that I protect the sheet so I can't use "grouping tool"
So help me if there's any way to solve this issue by circular reference or any other suitable solution
Thanks in advance for your appreciated support.

 


Comment by: Jan Karel Pieterse (11/28/2014 3:27:37 PM)

Hi Alaa,

I think the easiest is to make a separate tab for each rep. On that tab you place a small table with just two columns:

Date         Sale

Then you let them enter dates and amounts in these two columns.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].