Comments on: Circular reference in Excel - how to check, find, enable, or remove

This short tutorial explains the basics of Excel circular reference and why you should beware of using them. You will also learn how to check, find and remove circular references in Excel worksheets, and if none of the above is an option, how to enable and use circular formulas. Continue reading →

Comments page 2. Total comments: 69

  1. Hello,
    When I open my country music excel file, I get a note saying there is a circular issue or something to this effect.

    This is in my country music excel file where I am researching about 75 years of weekly Billboard country music charts. Each song is given points based on chart position for each and every week it is on the music charts. Most years have around 60 songs. Most of the charts have 60-100 positions, with a spreadsheet column for each possible chart position. I have broken those 75 years into smaller timespans between 5 - 10 years each and have made a subfolder for each time period. I also have subfolders for various working information to support the project. In addition I give points to any song winning or nominated for a major music award and for other misc things. The end result is subfolder with 200-300+ columns and hundreds of rows.

    So now when I first open the excel program and select this entire folder, when the entire folder opens, I get this message from Excel about a circular issue. How do I identify which subfolder and where in it this issue is? The size and complexity of this folder make this Excel issue so very challenging to correct. This is proving to be quite frustrating and time consuming for what is of unknown importance. Any help you can provide to help me correct this issue would be greatly and I mean greatly appreciated.

    Thanks so much,

    Debbie

    1. Hello Debbie!
      Unfortunately, without seeing your data it hard to give you advice.
      However, the instructions above tell you how to find the circular reference and how to fix it.

  2. hi team
    I am getting below error in my excel document.Kindly help me how to resolve this.
    "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly.
    Try removing or changing these references, or moving the formulas to different cells."

  3. Helpful - thank you!

  4. If you want the current time stamp there's a simple shortcut Ctrl + ; or for a time stamp you can do Ctrl + Shift + ;

    1. Current Date = Ctrl + ;
      Current Time = Shift + Ctrl + ;
      Whoops

  5. What do you do when you have a circular reference that is defined as a column in a table? So I am doing a SUMIFS or SUMPRODUCT on a column - but my cell is in that column. I need to define this dynamically, so I need to have the named column rather than a cell reference, and I can use SUMPRODUCT etc. to multiply by an array that always zeroes out my cell so it's never a true self reference, but Excel still recognises it as such. The labelling doesn't seem sophisticated enough that I can exclude Table[[#ThisRow]:[Column]] from Table[[#All]:[Column]] ... and I'm working on Excel 2007 for good measure.

  6. Hello -

    I have a question about the above formula =IF(B2="yes", IF(C2="" ,NOW(), C2), "").

    The sample shows that the dates are the same but the times are different. I follow the above formula but when I go to enter yes, the whole column changes to the same time. And to better help, here's what I'm trying to do:

    I'm going to work in a stockroom and want to make it more productive and have an easier managing system for items than what's currently being used. I want to create a spread sheet where I can keep track of the date/time products enter or leave. For example, say Johnny is taking out a ream of paper on 4-3-19 at 3:50pm. Susie comes the same date but at 4:00pm and takes out 2 staplers. How can I get it to show the 3:50pm time and the 4:00pm with the current date?

    1. Can you check time zone and Pc Current Date & Time

  7. This is very helpful, especially on how to find the problems. Thank you.

  8. I have a list of five items. Now i need to randomly pick 2-5 items from it, sum them to get the total of the selected items. But i also need to get what percentage of each items are in the total. Say ItemA, ItemB.... ItemE. I choose items A,C and D. Total of these 3 items is T. How do i calculate the percentage of itemA to total items T?

  9. Hello,
    Thank you for your shared informations on excel.

    Regularly, I note the date (in B1) and the value of an investment (in C1) and I want to retain the minimum (in F2) and maximum (in F3) values.
    Here are the formulas:
    In F1: True (to initialize Min and Max) or False
    in F2: =IF(F1,C1,MIN(C1,F2))
    in F3: =IF(F1,C1,MAX(C1,F2))
    Everything works correctly with circular references
    But, how to memorize at the same time the date (in B1) of this statement (in G2 or in G3) ?

    Best regards

  10. Quick and easy solution. Thanks

  11. I am creating a project form for my team to use that includes circular formulas in order to timestamp completed milestone dates. However, people here get annoyed that they always need to enable reiterations to use the sheet - is there a way for the sheet to work on everyone's computer without them changing their excel options every time they use it?

    Thanks!
    M

  12. I've used =NOW()to insert the current date into a cell
    for years. In the latest version of Office 365 Excel it generates a circular reference error at every occurrence and shuts down the application even though it appears to be accepting inputs to new cells. Is anyone else experiencing this issue?

    1. did you ever get an answer?

  13. Thank you soooooooooo much , it solved my problem completely.

    1. I am getting a message while putting formulas in Microsoft excel 7 sheets that there "exists circular reference" in the sheet and the formula that I am trying to put is not calculating anything and showing me the same message every time so how to solve this problem if you can help me to solve the problem i will really appreciate your replies

  14. 2-Jan-17 3-Jan-17 4-Jan-17 5-Jan-17 6-Jan-17

    12 30 40 50 10

    i have circular reference in my excel sheet A linking to other excel file B, when i change the date on the file B it updates the A file with the date match, retains the other dates values.Currently i am not able to perform sum on the file A since i want weekly total.

    Any help would be appreciated thanks

  15. hi, i do product review & tech writing. i cannot find a short procedure on finding a ciruclar reference. (all this verbage.. have no time for it, this is a steps thing, for 1 line bullet sequences). here's the only thing that worked for me so far:
    - select cell with ciruclar reference, in circular reference bar drop down box check all same cells where recently made a change / undo change, calc sheet. done.
    - when need it/ not always see how red lines show up on an error / if applies here or not.
    - still looking for main procedures other than my slight observation, thanks.

  16. I am getting an invalid circular indication on the first line of a new workbook. Excel version 14.7.0. Help please.

  17. I keep getting false circular reference errors. What could cause that?

    1. Hi Greg,

      Usually, Excel displays that error when there is one or more formula on a sheet that directly or indirectly references its own cell. A typical example is a formula, say, in A10: =SUM(A:A)

  18. This problem can be solved if you use IFERROR function

    1. how?

  19. Hi, when I use this =IF(B2="yes", IF(C2="" ,NOW(), C2), "") , I get 1/0/1900 as the timestamp. I have enabled iterative calculation. why am I not getting the right time stamp?

    1. same here.. do you find the right formula? may i know it?

      1. Try to put "" instead of "=" on the first IF statement. See sample below.

        =IF(C5"",(IF(D5="",NOW(),D5)),"")

        1. " "

  20. Very useful, thank you for publishing the article.

  21. For about 20 years I have been calculating our budget with a simple system. I have rows named 'housekeeping', 'vehicle expenses', 'insurance', 'medical expenses', 'clothing', ‘holiday & family’, ‘savings’ and a host of other items we budget for.
    The formula was simple, column 1 would have the name of budget, column 2 and 3 would be blank, that’s where we enter amount budgeted and spent for each item, like debit and credit columns. There is a hidden column (4) where we calculate the sum of column 3 less 2 (income less expenditure). Column 5 would calculate the balance of column 4 (which could be a plus or minus amount), plus column 5, (a circular reference) which would give us a running balance of that row. We delete entries in columns 2 and 3 and the balance remains for the following session. All the items added together in column 5 would then correspond with our bank statement.
    Through the years this system worked flawless until Microsoft brought out Excel 2016. The programme now refuses to calculate the circular reference and I don’t know how to get around this.
    Has anyone got any suggestions to get around the circular reference?

    1. You can use a simple macro, which allows a statements such as this:
      x = x + y

  22. The problem is that the references are not and have never been circular. Excel makes an illogical assumption that the formula is not similar to other adjacent formulas. Not true and so what. It allows you to ignore each of its erroneous warnings but continues to give you the error message upon opening the file while admitting when you hit error checking that all references have been resolved.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)