When you first open the workbook for this video, a message pops up complaining about circular references. If we just click OK, the problem appears to go away, but it doesn't, actually, and circular references are a very serious issue in workbooks. A circular reference is when the calculation cell includes itself as part of that calculation, and as a result, gets into an infinite loop. They can also occur when a cell indirectly references itself, so it refers to another cell which refers to it. Now we will cover this in more detail in this week's tool box, so make sure to check it out. Now the problem is, now that I've pressed OK, how do I find out where that circular reference is? As in the previous video, we will use the error checking tool on the Formulas tab. So come up here and click on it, but this time click the drop down and at the bottom you will see Circular References. Now this will be grayed out if there aren't any in your workbook. But when I hover over it, you can see we've got one in G19. And when I click on this, it will take me directly to the cell. The fact that the cell is zero when I'm trying to add up all the values from 2016 suggests that there is a problem. So look out for that kind of thing as well because it can indicate a circular reference. In this case here, we've actually summed G5 all the way up to G19, which is our calculation cell. And it's easily fixed just by selecting the correct values, so G5 to G18. Press Enter, and circular reference resolved, easy to do, but very important to find them and correct them. So that's one slightly subtle error. The other one we're going to look at, are the green triangles. This sometimes crop up in your workbook, they may indicate nothing at all, but they are Excel's way of telling you that something about that calculation, while not necessarily an error, looks like it might be incorrect in some way. The most common reason is an inconsistent formula. That is when you have a lot of formulas that all look the same, and then one that looks a little bit different. So let's come over here to C15, which has a green triangle, and click on it. Come to the little exclamation mark that appears and click the drop down. That is exactly what's happened here, we've got an inconsistent formula, without Excel's help we might never have spotted that. The value looks completely reasonable but if we double click on this cell, you will see it's not including the 2016 figures. So it is actually an error. Excel also gives us some options for fixing that. So again, if we come to our little drop down, if it was not an error we could just choose Ignore Error. And the green triangle would go away. If however it is an error, if it's an inconsistent formula, you can just click Copy Formula from Above and problem fixed. Now we'll come to this green triangle, because the other one is now gone, and click the drop down. When you're getting a lot of green triangles and there aren't errors to go an manually ignore all of them would be a bit of a pain. So you have another option, and that is to change your error checking options. So come in here and then at the bottom you will see Enable Background Error Checking. Now that you probably want turned on, that is what allows Excel to go and do this kind of checking for you. You can also chose what color you want your triangles to be, so they don't have to be green. But more importantly here are all the different options that Excel is going to check for. And for example, here we have Inconsistent calculated column. Now, one that may crop up which you don't like is the unlocked cells containing formulas. This is when you're using error protection. You may wish to flag any cells that are unlocked that you'd actually like to be locked because they contain a calculation. And we'll look at this more when we get to protecting our data at the end of the week. But have a look through these options and any that you want to turn on or off you can. If you're not sure though the defaults are generally a pretty good starting point. So just before we come out of this at the top you will notice another option which is Workbook Calculation. Now by default this is set to automatic and what that means is every time you make a change in your workbook all the calculations recalculate. Usually you want this on. However, if you have a massive workbook and it's being very unresponsive, so every time you make a small change you have to wait a couple of minutes for it to do all the calculations again. You can instead set your workbook to manual. So I'm going to click Manual and say OK. So let's see how setting the calculation to manual changes things. So for example down here, we actually have a drop down list where we can choose which cells manager we want to focus on. And I'm going to click on the drop down and I'm going to choose Yvette instead. Of course it's changed to value that I've selected, but you'll see the rest of the values have not updated. Now if we come back to our Formulas tab, and come to our Calculation group, you'll see there's an option to Calculate Now. And when I click that, it forces Excel to immediately recalculate all the values in your workbook. But it means I can choose when I want to calculate, rather than it having to recalculate every time. So that is how the manual calculation works. You'll have to press Calculate Now when you actually want to see the calculation change. Now to set it back to automatic, come to your Calculation options, click Automatic. And now if we go and change something, you can see it re-calculates automatically. So when I change this back to Connor you will see it immediately updates as we would expect it to.
