Hello again and welcome back to our course on Excel 2013.
In this section, we’re going to finish looking at Formulas and Functions in general and I’m
going to cover some of the general tools both to help you working with formulas and to locate
the functions you might need in your workbooks.
So, we’ll start by taking a quick look at Excel Options.
The key page here within Excel Options is the Formulas Page.
And if you look at the very top there, Change options related to formula calculation performance
and error handling.
Now at the very top we have a section, Workbook calculation options.
I can’t remember the last time I switched this off.
But generally speaking what this means is that as you work on your workbook all the
calculations are done for you.
If you change a cell and other cells depend on it, totals change, multiplications, other
formula and function evaluations change.
It all happens automatically, instantly and in the background.
And that is really the way that the vast majority of people work all the time in Excel.
The only situation you may come across where it will be worth switching this off and perhaps
setting it to manual is if you’re dealing with a very large and complex workbook and
the calculations are so complex that they significantly slow you down while you’re
working.
So you’ve maybe got dozens of worksheets.
You’ve got hundreds or thousands of calculations on each one using some very complex functions
and every time you type, there’s a delay while it recalculates the contents of many
cells and many worksheets.
If that were the case and to be fair it doesn’t happen very often, it might be appropriate
to switch automatic calculation and revert to manual calculation which means you could
make all the changes you wanted to make and then click a button to say Calculate and wait
for it all to happen.
It’s quite unlikely.
It could possibly happen, but it’s worth knowing that it is an option.
So moving on to the next section, Working with Formulas.
We’ve already seen this what we can use to use the R1C1 Reference Style instead.
The next one, Formula AutoComplete is also very important.
It’s actually set on here and that is the thing that means that as I type, so when I’m
entering a formula, as you saw earlier if I start to type the name of a function it
will come up with suggestions about what that function name will be.
And, of course, when I’ve got names it will come up with names that match that as well.
Now AutoComplete is a fairly consistent feature in Office 2013, as in earlier versions of
Office, and if you’ve used any of the components of Office before you’ll understand AutoComplete.
You can switch it off quite easily here and then as you type you won’t get those messages.
Some people fine those suggestions rather annoying and I must admit that a lot of the
time that I’m using it, I find the AutoComplete facility can be a bit annoying as well and
I do occasionally switch it off.
In case, you haven’t quite worked out how that would look.
If I switch it off now, click on OK, I’ll start a new workbook.
Let me just a Control-N for a new empty workbook.
Now let me say put a couple of cells, a couple of content in there.
If I want to now put equals and then I’m going to say the sum of E2 and F2.
I now put equals S, there’s no suggestion.
U, there’s no suggestion.
It’s not suggesting that that might be a sum function.
So that’s really what I am eliminating if I switch off the AutoComplete feature.
So within the Excel Options for Formulas they are the key settings.
There are at the bottom of this page a selection of error checking rules and you can selectively
decide whether you want these.
I’m not going to go through them all because some of them take quite a bit of explanation.
Each of them as you can see has a screen tip which will explain more what the particular
error check does.
And you may find, particularly in certain circumstances that you may want to switch
off one or more of these error checking rules.
But I’ll leave those for the moment and I’ll return to the question of evaluating
formulas.
Now a couple of sections ago, we put in a formula for the wall area of a room.
I’m just going to click on this cell in this worksheet and you can see that formula
now.
Now there are some facilities within Excel 2013 that help us when we’re having problems
with a formula.
Now in this particular case, I don’t think that’s a particularly complex formula.
But it’s a very good one for illustrating one or two of these facilities.
So having selected the formula, if I now go to the Formulas tab, there is a section on
the Formulas tab called Formula Auditing.
And Formula Auditing gives us a way of looking at what formulas are doing, how they’re
being evaluated and so on, to try to identify problems or perhaps even just to better understand
what’s going on.
One of the buttons on the right there, this lower one here, is called Evaluate a Formula.
And if you look at the screen tip it says “Debug a complex formula.”
Now what we’re going to do is we’re going to use this particular one here.
This is the formula that says 2 times B4 times E4.
B4 is the length of the room.
E4 is the height.
C4 is the width of the room, E4 the height.
So we’re going to work through that formula and we’re going to see how that Evaluate
Formula function can help us.
So if I click on Evaluate Formula, it brings up the formula itself: 2 times B4 times E4
plus 2 times C4 times E4.
Now, one thing about this is that the next thing that it’s going to do is always underlined.
So if I click the Evaluate button at the bottom of this dialog, just watch what happens.
Look at the Formula area, that white area in there.
It substitutes 15 for E4.
Now if I just pull that over to the side so you can see the numbers.
Now click Evaluate again.
It puts the 9.
Now it does what’s in the braces, so it does the 15 times 9, evaluate, that’s 135.
What’s the next step going to be?
It’s going to multiply 2 by 135.
And so on.
So you can step through a complex formula.
It doesn’t really matter how complex it is.
A step at a time and see exactly what Excel 2013 is doing.
This is also particularly helpful if you’re not quite sure that you’ve got the brackets
in the right place or you’re not quite sure how the operator precedents are working.
So that’s Evaluate Formula which I think is pretty useful.
Now another feature of Excel 2013 that can help, particularly when you’re dealing with
very complex formulas, is the ability to trace precedents and dependents.
Let me give you a particular example.
Let’s suppose that I’m looking at this total here.
I can see the formula sum F4, F12.
But let’s suppose that I’ve got a much more complex formula than that.
It’s referring to other cells and each of those in turn is referring to other cells
using perhaps complex functions and so on.
Whatever cell I’ve got selected in the Formula Auditing Group, if I look at Trace Precedents,
I will see arrows to all of the cells that in some way contribute to its value.
Now if I click Trace Precedents here, I can see blue arrow that covers basically all of
the cells from the top one there, the wall area of the dining room and all of the others
as well.
And what it’s saying to me is that the value that goes into, note the arrow going into
this field, is a function of all of those.
If I were to choose one of those and say where does it get its value from?
Click the same thing, Trace Precedents.
I would see that it gets its value from three other Cells; that one, that one, and that
one.
And they’re, of course, to the three Cells that are involved in this formula.
Now to remove the arrows at any time just click on Remove arrows.
I can also use it to do the opposite.
I could say, for instance, here’s the width of the kitchen.
What’s that used in?
And to do that, I do Trace Dependents; find the other fields that depend on the value
in the width in the kitchen; so Trace Dependents.
That says that feeds through to that number.
Okay what are the dependents of that number?
Dependent of that number is that number.
So I can basically trace forwards or backwards how one element of a worksheet affects other
elements of a worksheet.
And just one other useful thing there as well.
In the Formula Auditing Group on the Formulas Tab, if you look at that top button it is
Show Formulas.
And this is a good option when that it will actually show you all of the formulas that
are used on a sheet.
Obviously, this can get pretty complicated.
But it’s very useful for finding where you have formulas and when you can see all of
the formulas in view as here it’s sometimes easier to just make sure that what each of
them doing appears to be correct.
To stop showing the formulas, you just click the button again.
Now one other thing I’d like to point out to you about the Formulas tab is that there
is a Group on the right, the Calculation Group, which is involved in the thing I talked about
earlier on about switching between Automatic and Manual Calculation.
Now the chances are that you won’t need manual calculation but if you do you don’t
actually have to go into Excel Options to switch between.
You can switch here.
There are three options.
We’re mainly concerned here with Automatic and Manual.
There is one in the middle, Automatic accept for Data Tables, but until you find out about
Data Tables that won’t mean very much to you.
So just think of it as Automatic and Manual.
If you switch to manual which means the calculations are not done as you type and as you work on
your worksheets.
These two buttons on the right are the ones that actually do the manual calculation.
The top one, Calculate Now, which has a keyboard shortcut of F9, calculates the entire workbook.
The lower one which has a keyboard shortcut of Shift plus F9 calculates the active sheet.
Now with both of these, you’re only going to need them if you have Automatic Calculation
turned off and the chances are that you won’t have it turned off.
But if you need to, you need to know about those.
I’m going to switch it back on to Automatic.
Now finally in this section I’m going to take a quick look at the functions that are
available in Excel 2013.
First of all, you should remember earlier on that we looked at the Insert Function dialog
and that will work perfectly well when you’re working on a formula to help you to find the
right function.
You should recall we have a search facility.
We can select by Category.
And when we’re selecting if we click on a particular function, we get a description
of what the function does at the bottom.
So that’s usually a way that you’ll find the function that you want.
But there are some other options and one of the options is if you’re working with functions
in a particular category the functions are categorized here on the Formulas tab in the
Function Library Group, the main groups are there.
So one important group, of course, is Recently Used, which are the most recently used functions.
You’ll probably see that these ones at the top are the ones that we’ve been using quite
a bit so far in the course.
And then we also have things like Financial Functions.
We have Logical Functions, If, Not, True, and so on.
We have Date and Time Functions.
Things like looking at what the time is now, that sort of thing.
And then we have Math and Trig Functions.
The sort of things you’d normally expect: Signs, Co-signs, Factorials, and so on.
Now all in all Excel 2013 has about 400 or so functions.
So there are a lot of them and finding the one you want is usually a bit of a challenge
if it’s not one you use all the time.
Apart from the categories that are in view here, we also have More Functions and the
More Functions list includes categories such as Statistical, Engineering, Cube, Information,
Compatibility, and Web.
Now one very useful source of information about the functions is to use Microsoft.com
itself.
And if you look at the What’s New in Excel 2013 Page that we looked at earlier on, one
of the features highlighted there is New Excel Functions.
And as it says here, you’ll find several new functions in the Math and Trigonometry,
Statistical, Engineering, Date and Time, Lookup and Reference, Logical, and Text Function
Categories.
That’s which is most of the categories actually.
And if you follow the link there, Excel Functions by category it takes you to this very useful
page on Microsoft.com which categorizes all of the functions.
Obviously, it’s got the same ones that you see in Excel itself.
But if you go down there, you get a good explanation of each one and then a link for each of the
functions through to a lot more information about that function.
And that also includes all of the new functions as well.
So that’s it on Formulas and Functions for now.
I’ll see you in the next section.