Microsoft Excel 2002

The purpose of this tutorial is to provide some basic information on using Microsoft Excel 2002. It assumes that you already know the extreme basics such as entering numbers and data into cells as well as navigation in excel.

Formatting cells

The format of the cell refers to the way that Excel presents the data in the cell. This can be anything from the font of text, to the way it displays dates or numbers. If the format for a cell is not what you want it to be, it may display a number of items as a dollar amount or vice versus.

The way you can format a cell is to select the cell and right click. Choose format cells. A dialog box pops up with different sections such as: Number, Alignment, Font, Border, Patterns, and Protection.

With the Number section you can tell the worksheet to display a number as Currency, Date, Time, etc., etc. When you click one of these options another box will pop up and allow you to choose exactly how you want the information to appear.

With the Alignment section you can tell the worksheet if you want the data aligned on the right, top, bottom, or left of a cell, whether to show the info horizontally or at a degree, etc. One personally very helpful aspect is the choice to center information over a row of cells. To do this all you have to do is select all the cells above a table you have created, right click to format them, then on the Alignment section, pull down the arrow button in the box under Horizontal. Choose Center Across Selection and you will be able to quickly center a title over a table.

I’m not going to cover the other sections as they are pretty obvious and I don’t want to bore everyone here to death.

Copying a Format to Other Cells

After you have set up the format for a cell the way you want it, you can quickly copy the same information to other cells. The way to do this is to first select the cell you have formatted. When you select a cell it has a bold outline around it. On the bottom right corner of the outline you will notice a little box in the outline cut off from the rest of it. Hover your pointer over this little box and you will notice your pointer change. Click on the box once your pointer has changed and drag it down or across the other cells you wish to format. When you do this you will notice a little box appear next to the selected cells. Click on this and it will give you the option to copy the data you entered in the first cell or only the format of it.

Entering a Formula

All formulas start with the = sign. To enter a formula into a cell, all you have to do is type = as the first character in a cell. Then you have several different formulas you can enter such as:

Addition_______+

Subtraction____-

Multiplication___*

Division_______/

Exponentiation_^

So let’s say you have a number value in cell B4 and F8 that you want to multiply in cell F10. All you have to enter in cell F10 is =B4*F8 and it will do the calculation for you. And you don’t have to type in the cell address either. To do this type = in cell F10 then click cell B4 add the * in cell F10 and then click cell F8. Hit enter and the formula is complete. You can copy this formula to other cells the same way you did with the format feature above. However you need to know about Relative and Absolute References first.

Relative and Absolute References

Let’s say you have a list of number in column B right next to another list of numbers in column C that you want to add together for each row into column D. If you use the drag option to take the formula from the first row to all following rows in column D if will work fine. Excel will automatically adjust the formula so that each row in column D is adding the correct numbers. A formula in D4 that is =B4+C4 will be changed to =B5+C5 in cell D5.

However, what if you are using a number in one specific cell for all the formulas in the row such as an interest rate? Excel will adjust the formula each time it moves to a new row or column and screw everything all to hell. To get around this you need to know how to change that relative reference into an absolute reference.

To do this all you have to do is add a $ in front of the row or column reference or both. So if you are multiplying all these numbers by a number in A1 you would write: =B4+C4*$A$1. This will change it to an absolute reference that Excel will not change if you copy the formula to another cell.

Selecting a Range

Let’s say you have a list of numbers in cells B4 thru B38 that you want to add up in cell B40. Now clicking on each one of these cells and entering the + sign in cell B40 would be a major bitch. Instead all you do is type, =SUM(B4:B38) and hit enter.

There is also an option on the toolbar to do this. It looks like a Greek symbol that looks faintly like an E. I forgot what it is called but what the ****, really? It has a down arrow next to it with a drop down menu for different functions such as SUM, COUNT, AVERAGE, etc.

Select the box you want the formula in, choose the function and it will do it for you. However, by default it always chooses all the cells directly above the cell you are entering the formula starting with the first box with a number value to the cell directly above the cell you are entering the formula in. If no numbers are above it, it will look to the cells on the left.

Functions!!!

*yawn* Ok, now we have the functions you can use in Excel. I will use the IF function as an example, because it is the one I have used the most. Excel has instructions in it for how to use each function, so you can learn whichever ones you want.

Let’s say…..that…..urm….for some reason you want a cell to display a certain value if another cell is one thing, but another type of value if it’s another thing. Like…….well, if you employed some people and gave them a bonus if they worked more than 30 hours in a week but if they didn’t you gave them a keychain. (Give me a break, it’s good enough to get the idea across.) Let’s say the bonus is beer. Why? Cause I really like beer.

Ok, so in column E you have all the total hours listed for each employee for the week. You want a formula in column F to show you whether they get beer or a keychain. Just above the spreadsheet and just to the left of that long white bar that displays the contents of whatever cell you have selected is a button that looks like fx. Click on this button and a box pops up with all the different functions. Pick IF and hit ok. You come up with another box that has three lines of information for you to input.

Logical_test

Value_if_true

Value_if_false

Now somewhere on the spread sheet you need to put Beer in one cell and Keychain in another.

So for this first function you are calculating for F10 and the hours worked for this employee is in E10. Beer is in A30 and Keychain is in B30.

So next to Logical_test you would put E10>30.

Value_if_true would have $A$30. Beer!!!

Value_if_false would have $b$30. A ****ing keychain.

So the completed formula looks like =IF(E10>30,$A$30,$B$30).

Pretty much this says. If the contents of E10 is greater than 30 then we give him what’s in A30, if it isn’t we give him what’s in B30.

I think that’s where I’m cutting off this tut. There are a ton of other things about Excel but this is already pretty freaking long. Plus I don’t know if anyone will even give a **** about this.

If anybody does I’ll do another one to cover using colors and shading, adding backgrounds and how to make everything look all pretty and professional looking.

Hope this is useful to someone.

Watcher