For this lesson you need to open the sample file: First Quarter Sales and Profit-6 from your Sample Files folder.
The AutoSum tool is very useful for quickly inserting Sum, Average, Count, Max and Min formulas into cells. Many Excel users never get any further with their formulas than this. But in this lesson you'll create your own formulas without the use of AutoSum. You'll be amazed at how easy it is.
I'll begin this lesson by deleting the contents of cell A10 to B10. So I select cells A10 to B10 with the mouse and then press the Delete key on the keyboard.
I'm going to add some text now to cells A11 and A12. In A11, I'll type: Costs. And in A12, I'll type: Profit.
I'm now going to invent a cost figure of 83,000 and type that into cell B11.
My challenge for this lesson is to compute the profit for January in cell B12, and that will of course be sales of 129,000 minus costs of 83,000, and I'll expect that to produce a profit of 46,000.
So I need a formula in cell B12 that will subtract the 83,000 in cell B11 from 129,000 in cell B9. That formula will be = on the keyboard (because you start every formula with the = sign), then B9 (that's the sales value), next the Minus key on the keyboard, and then B11 (because that cell contains the Costs value).
When I press the Enter key you see the correct result of 46,000 for profit.
Now the method you've just used to enter the formula does work just fine, but it isn't the best method. Sooner or later you'll make a mistake. For example, you could easily type: =B8-B11, resulting in an incorrect answer.
To eliminate such errors you should always select cell references visually rather than simply typing them in. You can visually select cells using either the mouse method or the keyboard method.
We'll begin by using the mouse method. So I'll click in cell B12 and then press the Delete key on the keyboard to remove the old formula.
Now I press the = key on the keyboard to start the formula, click on cell B9 (that's where the Sales are), Minus key on the keyboard, click on cell B11 (that's where the cost is), and I press the Enter key. And you can see that, once again, I get the correct answer of 46,000.
But the very best Excel experts hardly use the mouse, because you waste valuable seconds every time you reach for the mouse. So here's the expert technique of visual selection using the keyboard.
First I'll press the Up Arrow key to move to cell B12, and then press the Delete key to delete the existing formula. Now I press = on the keyboard, Up Arrow key one, two, three times to move to cell B9. Minus on the keyboard, up arrow key once to move to Costs, and press the Enter key to get the correct answer of 46,000.
Let me just do that again to show you how fast that was. I'll start by deleting the value in cell B12.
So here we go: = three up, minus, one up, Enter. You can see how much quicker that was than using the mouse.
An important thing to realize is that formulas automatically recalculate whenever any of the cells in the formula change. For example, if I was to increase Paris sales in January to 45,000, this would cause the Sales figure to increase by $10,000. So the value in B9 would increase to 139,000. This in turn would cause the profit to increase by 10,000. So the value in cell B12 would also recalculate to 56,000.
Let's see that happen. I'll type: 45,000, into cell B7. When I press the Enter key you'll see the values in cell B9 and B12 update.
I'll press the Enter key now.
I think I'll now change the Paris sales back to the original 35,000 value.
Let's now enter a formula that uses the multiplication operator rather than the subtraction operator. This employer is very generous and pays the staff 10% of all profits as an incentive bonus. So in cell A13 I'll type the words: 10% Bonus, and then press the Tab key to move to cell B13.
Now 10% of 46,000 profit will of course be 4,600. You probably know that you can calculate 10% by multiplying a value by 0.1. So let's now enter the formula: = , then I'll click onto the Profit: 46,000, then the multiplication operator. That's a Shifted-8 on most keyboards. And you may also use the multiplication operator on the numeric keypad if you have one on the right of your keyboard.
And now I'll multiply it by that 0.1 in order to calculate 10%. And when I press the Enter key you can see the correct value of 4,600 is calculated as the Bonus (being 10% of 46,000).
You're going to learn more about calculating percentages later, in Lesson 4-3.
All that remains now is to save your work. And as usual I'll save into the folder above the Sample Files folder, so as not to overwrite the sample file. And I'll rename the file to: First Quarter Sales and Profit 7.
I click the Save button and you've now completed Lesson 2-12: Create Your Own Formulas.