Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.
Practice Video: Formula Calculation Options
Loading...
來自 Macquarie University 的課程
Excel Skills for Business: Intermediate II
418 個評分
從本節課中
Formula Auditing and Protection
If you are worried that errors have crept into your worksheet, this module will show you how to check for errors, trace precedents and dependents, resolve circular references, and finally, protect your worksheets and workbooks from further harm.
與講師見面
Dr Yvonne BreyerDirector, Learning and Teaching
Faculty of Business and Economics
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.
