Introduction to Excel 2010

Spreadsheets

Spreadsheets are made up of columns, rows, and cells.

Columns have alphabetical headings, starting with A, B, C, and so on. After the first 26 letters of the alphabet, Excel starts the column headings with AA through AZ, and then starts again with AAA through ZZZ and so on. Excel has 16,384 columns altogether.

Rows have numerical headings, starting with 1, 2, 3, and so on.

A cell is where one column and one row meet, and a cell is where you type. When you click in a cell, it becomes the active cell, with a black border around the cell. Anything you type will go into the active cell.

If you use cell addresses instead of values in formulas, for example =A+B instead of =1+2, formula results automatically update each time you change the values in cells A and B.

When you copy a formula from one cell to another, different types of cell references produce different results.

A relative cell reference looks like this: F2. An absolute reference to the same cell looks like this: $F$2. A mixed reference could be either $F2 (an absolute column, with a relative row), or F$2 (a relative column, with an absolute row). Press the F4 key on your keyboard to change a relative reference to an absolute or mixed reference (or you can type the dollar signs yourself). Each time you press F4, you will cycle through different combinations of absolute and mixed.

Use math operators in formulas to do math in Excel. All formulas start with an equal (=) sign.

• To add, type =93+14
• To subtract, type =93-14
• To divide, type =93/14
• To multiply, type =93*14

If you use multiple math operators in a formula (add and multiply, for example), calculate by using the rules of operator precedence. Although Excel will do the math for you, you need to know how to write the formula correctly.

For example, in =3+6*2, the multiplication is done before the addition. First 6*2=12, and then 3+12=15.

1. Do things in brackets or parentheses first.
2. Exponents (roots and powers) come next.
3. Multiply or divide before you add or subtract.
4. Otherwise, go from left to right.

Use the functions (predefined formulas) available on the AutoSum button to let Excel do calculations for you.

1. Place the cursor directly under numbers in a column, or directly to the right of a row of numbers.
2. On the Home tab, in the Editing group:

• To add, click the AutoSum button
• To do other kinds of math, click the arrow on the AutoSum button and then click Average, Count Numbers, Max, or Min.

1. To complete the formula, in the spreadsheet, press ENTER or the TAB key.

Excel has many predefined formulas, or functions. They can do all sorts of things from calculating monthly payments to tidying up a list of incorrectly capitalized names.

• To find a function, on the ribbon, click the Formulas tab. Then do one of the following:
• In the Function Library group, click the arrow on any of the Function Library books to see a list of functions. Scroll through the list, and rest your pointer on any function to see a ScreenTip with a description of the function.
• Click the Insert Function button in the Function Library group. In the Search for a function box, type what you are looking for, and then click Go. For example, type calculate payment.
  In the Select a function list, click any function to see its description beneath the list. When you find the function you are looking for, click OK to open the Function Arguments dialog box, where you can fill in the arguments for the function. Then click OK to enter the formula in your spreadsheet.

If you know the name of a function, you can type it directly into the spreadsheet and use Formula AutoComplete. Note that you do not need to have the Formulas tab selected in order to enter a function. You can type a formula regardless of which tab on the ribbon is selected. The following is an example of using the PMT function:

1. Click in an empty cell. Type =P. Formula AutoComplete displays a list of functions that start with P. Scroll down the list until you see the function you want, which is PMT in this example.
2. Click PMT. A ScreenTip displays a description of the function. Double-click the function to enter it into the formula.
3. If you need help filling in the arguments, click the name of the function in the ScreenTip that appears beneath the formula.
4. Fill in the arguments as in the previous example. Type a comma between arguments. At the end of the formula, enter the closing parenthesis, and then press ENTER.

The PROPER function cleans up incorrect capitalization by capitalizing the first letter in each proper name, leaving the rest of the letters in lowercase. This example assumes that you have a one-column list of names.

1. Click in an empty cell next to the first incorrectly capitalized name. Type =PROPER( .
2. Click in the cell with the first name to enter the cell into the formula.
3. Type the closing parenthesis ), and then press ENTER. The name is properly capitalized.
4. Copy the formula down the column (see the steps below for "Copy a formula from 1 cell to another").Now you have two columns, one incorrectly capitalized, and one with proper capitalization that contains formulas you no longer need.
5. Right-click the new column, and select Copy. Select the first column, right-click, and then click Values under Paste Options (to paste only the content, not the formulas).
6. Finally, delete the column with the formulas.

Using the fill handle

1. Select the cell that contains the formula you want to copy. Move the pointer to the bottom right of the cell until the pointer changes into a black plus sign .
2. Hold the mouse button and drag right, to the adjacent cell. Release the mouse button.

Using copy and paste

1. Select the cell that contains the formula you want to copy. On the Home tab, in the Clipboard group, click Copy.
2. Click in the cell you want to copy the formula to.
3. To paste the formula, on the Home tab, in the Clipboard group, click the arrow on the Paste button, and then click Formulas.

# # # # # The column is not wide enough to display the contents. Increase the column width, shrink the contents to fit the column, or apply a different number format.

#REF! A cell reference is not valid. Cells might have been deleted or pasted over.

#NAME? You might have misspelled a function name.

Cells with errors such as #NAME? might display a color triangle. If you click the cell, an error message appears to give you some error correction options.

Cecile Bendavid  

CEO Computers, Inc.