What else can I do with Excel?
As you have seen so far from the previous section, entering data is not all that terribly enthralling. Entering data into cells is only useful when you need to use Excel to sort the data or to keep a record of something important. Any database can do that! But what makes Excel particularly useful is the ability to enter formulas into cells and watching Excel automatically calculate the results of all your formulas and display the results in the appropriate cells.
In other words, you can tell Excel what formula you want it to calculate for you, tell Excel to hide the formula after you have typed it, and then let Excel show the results in the cell you want.
You may not think this is the most exciting thing since sliced bread but to people who use spreadsheets all the time to make a living or for personal use, this is considered an absolute godsend!
Here, let us explain what we mean by this. Begin by typing a formula into a cell. Don't know how to type a formula? Remember the good (or bad) old days in the maths class when you were shown a bunch of letters, numbers and other symbols on the blackboard with an equal (=) sign in the middle of them? It kind of went like this,
formula=answer
or
answer=formula
where a formula may look like,
1 + 2
or
x + y
and the answer would be,
3
when you remember the symbol "+" means to add two numbers together. In the case of x + y, x could be 1 and y has to be 2, or x could be 2 and, therefore, y must be 1 in order to get an answer of 3.
Okay, enough of the mathematics lesson as we are sure you would want to move on as quickly as possible.
Now, in the world of Microsoft Excel, we tell this application we want to enter a formula by first typing the symbol known as the equal (=) sign followed by the actual formula you want Excel to calculate automatically.
Try it! Find yourself an empty cell and begin typing the equal (=) sign. Now type 1 + 2 like you see below:
What do you get after pressing the Return key?
That wasn't so bad after all!
But I already know what 1+2 is?
It is good to see all the schooling you had in life had not gone to waste! But what if things got a little bit trickier? What if there was a formula too complicated to calculate at the top of your head and you wanted the formula calculated straight away and perhaps many times as you change the data in a cell or various cells? Would you want to rely on your brain to constantly calculate the formula?
Or would you be smart enough to let Microsoft Excel do the work for you?
For example, we all know that 234 x 234.1223=54,784.6182 (did you have to use your calculator for that one?). But suppose we didn't know. If you were placed under enormous time constraints, wouldn't you let Microsoft Excel do the work for you? Of course you would! How? All you have to realise is that the number 234 exists in cell A2 and the number 234.1223 is available in cell A5. So all you do is type the following formula:
= A2*A5
The only tricky part about this formula is realising that in the world of Microsoft Excel, the asterick (*) sign is the application's way of understanding what a multiplication (x) sign is. Yeah, a little pedantic, but that's what happens when you use a computer. Never mind, just remember "*" is "x" and you will be fine. Now notice how we avoided typing the actual numbers into the formula? Sure, we could have typed the numbers, but why? Let Excel do some thinking for us. We merely told Excel where the numbers are located (i.e. the names of the cells) and how the numbers should be processed (i.e. the multiplication sign). Then we see the answer automatically shown in the cell carrying the formula. The formula is still there, not lost on you. You are seeing the result while in the background, Excel remembers the formula.
Just keep in mind that in order to tell Excel to start calculating, always press the Return key once you have entered a formula. You will always get the answer from Excel faster than you can say, "WTF!".
This is all good and fine, but what if your boss came over to you and said, "Oh! Guess what? I've just got a new figure from sales and the number 234 is really in fact 512. Can you tell me what is happening now?"
No sweat. All you have to do is go into cell A2 and change the number 234 to 512. Press the Return key and presto! You have the answer instantly in front of you.
Of course, this is a very basic scenario, but you can see what we are trying to do here. In the real world, a spreadsheet can contain hundreds or even thousands of formulas. If you had to do these calculations by hand just to make one tiny adjustment to a number to one formula and explain how this has changed the results in all the other formulas, you would probably be spending days or even weeks doing the work, or eventually end up in a mental institution if you did!
Fortunately, your sanity is saved thanks to a useful application called Microsoft Excel.
But what if I don't know what the formula is?
Unless it is some specialised formula from the rocket science textbooks, most people will find the most useful formulas stored in Microsoft Excel via "Function..." under the Insert menu command.
What happens if I want to copy a number from another spreadsheet?
If it is just an ordinary number from another spreadsheet and typing it into the cell you want is too difficult (which would be understandable if you have to write the number pi to 20 or more decimal places), you can open the second spreadsheet, highlight the cell containing the number you want, choose the Copy command, switch to your first spreadsheet, and Paste the result into the cell you want.
However, if the number being copied is part of a formula and you want this number to automatically change on the other spreadsheet while your current spreadsheet changes at the same time, copy the number as usual from the second spreadsheet. When you are ready to paste the number into your first spreadsheet, choose "Paste Special..." under the Edit menu command.
Then click the Paste Link button in the Paste Special window.
This creates a link to the cell in the second spreadsheet containing the number. This link information and the cell name it refers to can be copied into other cells or used as part of another formula you may want to create.
How do I copy the same formula across consecutive cells in a row or column?
Now comes a time-saving feature. When you highlight a cell, there is a small square box appearing in the lower right corner of the cell. This box is called a fill handle.
With your mouse, move the cursor over this box, click the mouse button down, and drag the box to the left, right, up or down.
When you have all the cells selected, let go of the mouse button. Microsoft Excel will then fill each cell with an identical formula.
Easy, isn't it?
Not only can you fill multiple cells with the same formula or number, but if you don't want the letter or number forming the "name" of the cell in a given formula to change when you do this, place a dollar ($) sign in front of the letter or number in your formula such as $A1, A$1, or $A$1. How about that for being a bit trickier this time?
I have a lot of cells to add up. Is there an easier way?
You may have noticed how time consuming it can get to type the addition (+) sign followed by the name of the cells containing the numbers you want summed up. Our beloved Microsoft Corporation has been thinking long and hard about this and has provided a really useful feature called the Auto Sum button. This is represented on the toolbar as follows:
All you have to do is click the cell you want to see the results in, click the Auto Sum button, and start clicking all the cells to be included in Microsoft Excel's own unique summation formula known as SUM().
If the cells are in consecutive order along a row or column, don't bother about clicking each individual cell. Do a click on the first cell to be included in the formula and with the mouse button down, drag the cursor over the cells you want to include. This will highlight the cells you want. When you reach the last cell, let go of the button. Microsoft Excel provides a unique and compact way of presenting all the cells to be incorporated into the Sum() formula. If you are happy with the formula, press the Return or Enter key to see the answer.