Quick Tutorial     

Creating Formulas in Excel 2004-MAC

 Formula Bar

 

 

 

 Creating Formulas

  1. Select the cell that will contain the formula.
  2. Enter an equal sign.
  3. Enter the formula into the Formula Input Area using the following guidelines:

·         The four main operators are:
Add  +                            Multiply  *
Subtract  -                      Divide  /

·         Reference cells by their cell number (e.g. A10, B1).

·         Constants (4, 6.5) can also be used.

·         Enter parenthesis around calculations that are to be performed first.

·         Click the Paste (or Insert) Function  button to display an extensive function list.

  1. Click the green checkmark on the Formula bar  or hit the Enter key.

Note: The #VALUE! Error occurs when the wrong type of argument or operand is used.

Using the Insert Function Feature

  1. Select the cell that will contain the formula.
  2. Click the Insert Function  button on the formula bar.
  3. Select the desired function. The Function Argument window appears.
  4. Enter the arguments for the function. Arguments are the values a function uses to perform a calculation or operation. Arguments may include numeric values, cell references, ranges of cells, labels, or nested functions.
  5. To select a cell or range of cells as the argument for a function, click the  button to temporarily hide the dialog box. Select the range of cells on the worksheet, then click the  button to return to the dialog box.

  6. Click the OK button.

Copying Formulas to a Range of Cells

  1. Select the cell with the formula.
  2. Click on the fill handle.
  3. Drag the handle across or down, depending on which cells you want to have the same formula.

Note: Excel will automatically change the formula that you are copying to apply to the new range of cells.

 Naming a Range of Cells

  1. Select the cell(s) to be named.
  2. Enter the name of the cell(s) in the Name Box to the left of the formula bar. Valid names must start with a letter or underscored character, and cannot be the same as a cell reference.
  3. Hit the Enter key.
  4. To go to the named range at any point, click the drop-down button next to the Name Box and select the name of the range.

 Using AutoCalculate

AutoCalculate gives the results of various mathematical operations for highlighted cells. These results appear in the status bar at the bottom of the screen.  

  1. Highlight the cell(s) you wish to use the AutoCalculate function for.
  2. Right-click on the Status bar.
  3. Choose the desired function from the pop-up menu, (i.e. Average, Sum, etc). That function’s answer will appear in the Status bar.

Note: To stop displaying the AutoCalculate results, right-click on the Status bar and select None.

 Using Relative and Absolute References

A reference identifies a cell or range of cells on a worksheet and tells the formula where to look for data. 

  • A relative cell reference is relative to the position of the formula.  For example, a formula in cell C3 that contains a relative reference to the cell B2 would look for the value one cell above and one cell to the left of C3. If this formula were copied to cell D7, it would look for the value in C6 (one cell above and one cell to the left).
     
  • An absolute cell reference always refers to a specific location, regardless of where the formula is located. To indicate an absolute reference, place a dollar sign before the letter and/or number of the cell reference, such as $B$2.

 

Text Only Version