Join Dennis Taylor for an in-depth discussion in this video Dealing with circular errors, part of Excel Tips Weekly.
- [Voiceover] From time to time working with Excel formulas, you might encounter what's called a circular reference. On this worksheet we've got 46 locations in our operation. We've just added five new ones, and suppose I write a formula to increment this by five, and I'm thinking, okay A nine plus five. Either I'm inattentive or I'm just not sure what's going on. I'm going to type in equal A nine plus five, but if I do it in the same cell I'm envisioning maybe the answer 51, but Excel can't really do this.
It's saying this five is added to the current value of A nine, that's what you might be thinking. Press enter, a message, "There are one or more circular references or a formula refers to its own cell." Click okay, what happens here? We get a zero, and at the bottom of the screen we see circular reference in cell A nine, and here's that formula, and that just won't work, we can't do that. Now what I'll do here of course is press escape and control Z. Perhaps write the formula here if that's what we want to do.
You can't really have no mechanism for updating this itself. Now, there's a formula in cell H two. It's adding up this data, I want it to include column D as well, so I highlight the C, and I'm looking at my keyboard about to hit D. Maybe I hit the letter S, which is right next to it and enter. Whoops, what's happened here? Same message, now what am I trying to do here? I'm trying to add up a bunch of cells and amongst those cells is the current cell. I click okay, and there's what the former looks like, so that too is a circular reference, and we wouldn't leave it that way and of course that should be a D, and we're all set.
No circular references. I'm going to open another file. This one has a circular reference in it already, and maybe somebody sent it to you and didn't happen to see that there was a circular reference, or maybe it's an old file of yours that you never quite cleaned up properly. So I'm going to press control O and open this file that I've opened recently and as I do open this file, there's a message, "One or more circular references." Well click okay, now lower left corner of the screen says circular references, but it doesn't say where, and what if I jumped back to the other workbook that's still open, I'll press control tab.
That says circular references. In either case if you slide over circular references, you see the message, "There are circular references in an open workbook." That doesn't necessarily mean the current one. In this case, in this workbook, there are no circular references. There were, but we got rid of them. What's happening in the other workbook? Bottom of the screen just says circular references. Doesn't say anything else. How do we track these down? Go to the formulas tab in the ribbon, and off to the right you'll see error checking with the drop arrow, choose circular references, and then we see that there are four of them.
Now this is on a different sheet, this says pricing formulas. Look in the lower left corner of the screen here, you'll see that's the name of the other worksheet in this workbook, so I click one of these, cell F 10, we jump over here and we see some arrows. What do they mean here? So here's a formula in cell F 11. Let me zoom in on this to make it a little clearer too. As I double click cell F 11, we see that there's a formula there getting data from F 10, B seven, and B three, but let's go over to cell B seven and see what's going on.
B seven has a formula that says among other things, get data from cell F 11, but didn't we just see a formula over an F 11 that's getting data from B seven and other locations? There's the problem, maybe we don't know the long term solution here, but we do know that this reference here just isn't appropriate, at least for now, so maybe I'll make a note of it. But for the moment let's take that out of there. I'll simply delete that and as I press enter, that might or might not clean up the issue. It looks like it did.
Now I still have other work to do perhaps to see why that was there and maybe it should have been the square root of another cell or something. We don't know, but we have no more circular references. Nothing in the lower left corner, and going back to error checking on the formulas tab, clicking that drop arrow, circular references is grayed out. Now on the other hand, there are times when you might want to use a circular reference. On the sheet to the left of this we've got a formula set up here in column B, also column C and D.
Here's a formula in cell B five. We're going to be calculating profit based on total sales minus the overhead that does not include advertising, minus advertising, so B two, minus B three, minus B four. Now, we want advertising to be 10% of our profit, so we're about to write a formula here that's going to be getting data from cell B five, and yet B five will be getting data from B four.
So what happens if we write a formula here, I want our advertising expense to be 10% times the profit, and then press enter, and there's that circular reference. Click okay, and for the moment we're stymied. Lower left corner of the screen says, circular reference B five, but we might want to allow this, so let's go to the file tab in the ribbon. Choose options, then on the left side, formulas off to the right, enable iterative calculation, maximum iterations 100, check the box, click okay and now we've got an answer.
That is 10% of this, and the formula reads 10% of B five, and we can copy this to the right. Just as a reminder, I'll use a function out here that is relatively new in Excel. I use it for training purposes, called formula text, to refer to this cell, just as a reminder as to what is going on, we can work with any of these columns of course, so what happens if, and maybe this is a projection, we're projecting our total sales for the year, a million and a half. As I press enter, the formulas will react properly, and we see what's happening.
That is 10% of that, and once again here's the formula that's actually being displayed here for cell D four. In the formula for cell D five right there, so we see what's going on, this is working. Now, if you do have iterative calculation on, what happens if we were, and this would be an obvious mistake, but I'll do it on purpose here like a did in the very first example. Equal A eight plus five, I get an answer. By the way where is that 500 coming from? And what if I put in some other data over here? Strange things are happening, it's adding 500 each time.
So a little bit confusing as to how we work around this. We wanted to allow iterative calculation up here. What if we turn it off now? In the words, maybe those numbers are for the moment frozen? We want to keep them this way, we don't want to manipulate them. We can go back to file, options, formulas. Turn off enable iterative calculation. Click okay, we get our circular reference again. Click okay, let's get rid of this over here. We still see this reference, we can get rid of that. But what if I write that same kind of formula again.
Equal A eight plus five or one or whatever. Circular reference in cell C five, we still see this. What about this one? We've got one there too, but it's not flagging it is it? Go to error checking, circular references, yes it is flagged there. Now if we want to work with the data in a dynamic way, we can't right now because we've turned off iterative calculation, but for the moment these answers, these calculations, are correct. If you like to keep these displayed, but not have those arrows there, we can remove arrows, so we are in a situation where we have allowed iterative calculation.
We've allowed circular references at least for these calculations. So, you have to answer the question yourself as to how convenient these are, how necessary they might be in your environment, but for the most part circular references are truly errors and you don't want to see them. So we've seen different examples of how they might occur and how to get rid of them, and in this one situation here, how to allow them.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Excel Tips - New This Week
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.