For this lesson you need to open the sample file: First Quarter Sales and Profit-7 from your Sample Files folder.
I'll begin this lesson by typing the words: USA Sales, into cell A15 and: European Sales, into cell A16.
The challenge for this lesson will be to place a formula in cell B15 that will compute the sales in January for the New York and Los Angeles offices. In other words, the addition of the values in cells B4 and B5.
And we also need to enter a formula into cell B16 that will calculate the value of European sales. In other words, the addition of the numbers in cells B6 to B8.
Now you could use your AutoSum skills, developed in earlier lessons, to simply add a AutoSum to cell B15. You'd do that by selecting Home on the Ribbon, Editing group, AutoSum, then selecting the USA Sales cells, and then pressing the Enter key. And this would give you the correct answer, but the focus of this lesson is upon teaching you how to create your own functions manually. And once you've perfected this technique it will give you access to all of the functions in the Excel Function Library.
So let's see how this is done. To begin with you type the = button, because all formulas begin with =.
And this formula is going to contain a SUM function. So I'll press S (being the first letter in the SUM function), and notice that a list has popped up showing every function in the Excel Function Library beginning with S. And you can see that there are a lot of them. And you can also see that the SUM function is included there about halfway down the list.
But to make things easier for myself, I'll type the second letter of the SUM function, that's U. And now the list has shrunk to only show functions that begin with SU. And you can see that the SUM function is there, three down on the list.
I'll press the Down Arrow key twice to move to the SUM function. And notice that a single sentence tip has popped up next to the function. This briefly states what the function does. In this case: Adds all of the numbers in a range of cells.
But of course, there's a lot more to know about the SUM function than just that. So if you press the F1 key on the keyboard, you can obtain detailed help on any function.
I'm going to press the F1 key now, and the Microsoft help system pops up with detailed help about the SUM function. If you scroll down that help box, you'll see there's a lot of information there, along with examples of how the SUM function can be used. I'll close that box now.
And because I opened the help, I've lost my little list, so I'll need to type M to finish off the SUM function, and an opening bracket.
Notice that a different sort of tip has now appeared beneath the SUM function. And this is called the Syntax Box.
The Syntax Box tells you what needs to be placed between the opening and closing brackets of the SUM function. These values are separated by commas, and they're called arguments.
Now you can see that the first argument: number1, doesn't have square brackets around it. And that tells you that this is a required argument. In other words, you couldn't simply close the bracket and use the SUM function without any arguments. (Though there are some functions in which you can do this). You need to have at least one argument.
Then there's a comma and number2 is shown in square brackets. That means it's an optional argument. So you could have several ranges, all separated by commas, and the SUM function would give you the total of the values in all of those ranges.
The three dots at the end, (the ellipsis), tells me that I can have any number of arguments separated by commas. But for this requirement we just need one range, that's: B4 to B5. So I'll use the mouse technique to select cells B4 to B5.
Now, I should really type a closing bracket, but it's good to know that Excel's clever enough to know that you're going to want a closing bracket, so it will add it for you if you forget to type it. So I'll just press the Enter key and I get the correct result of 64,000 for USA Sales during January.
Now let's do the same thing for European Sales. I'll type = on the keyboard, SU, to see functions beginning with SU, Down Arrow key twice to move to the SUM function.
This time I'll press the Tab key, and I'll use the keyboard technique this time to select the range. The range will be B6 to B8. So I'll press the Up Arrow key to move to London, B6, hold down Shift on the keyboard and press the Down Arrow key twice to select the range B6 to B8. And when I press the Enter key, you can see that the European Sales figure is being correctly calculated.
All that remains now is to save your work. And I'm going to save in the folder above my Sample Files folder as usual. And I'm going to call this file: First Quarter Sales and Profit-8.
I click the Save button, and you've now completed Lesson 2-13: Create Functions Using Formula AutoComplete.