INTRODUCTORY MICROSOFT EXCEL Lesson 4 Worksheet Formulas

1
INTRODUCTORY MICROSOFT EXCELLesson 4 Worksheet
Formulas
2
Objectives

• Enter and edit formulas.
• Distinguish between relative, absolute, and mixed
  cell references.
• Use the AutoSum button and the point-and-click
  method to enter formulas.
• Display formulas in the worksheet.
• Perform immediate and delayed calculations.

3
Terms Used in This Lesson

• Absolute cell reference
• Formulas
• Mixed cell reference
• Operand

• Operator
• Order of evaluation
• Point-and-click method
• Relative cell reference

4
Enter and Edit Formulas

• A worksheet formula consists of two components
• An operand is a number or cell reference used in
  formulas.
• An operator tells Excel what to do with the
  operands.
• For example, in the formula B35, B3 and 5 are
  operands. The plus sign () is an operator.

5
Order Evaluation

• Contents within parentheses are evaluated first.
• Mathematical operators are evaluated in order of
  priority, as shown in Table 4-2.
• Equations are evaluated from left to right if two
  or more operators have the same order of
  evaluation.

6
Order of Evaluation Priority
7
Relative, Absolute, and Mixed Cell References

• Relative cell references adjust to a different
  location when copied or moved.
• Absolute cell references describe the same cell
  location in the worksheet regardless of where
  they are copied or moved.
• Mixed cell references contain both relative and
  absolute cell references.

8
Use the AutoSum Button

• The AutoSum button is used to sum long columns of
  numbers.
• Place the highlight in the cell where you want
  the total to appear.
• Click the AutoSum button.
• Excel then displays an outline around the range
  it has selected.
• Press Enter to display the sum in the cell.

9
Display Formulas in the Worksheet

• When creating a worksheet with many formulas, it
  might be easier to organize formulas and detect
  formula errors when you can view all formulas at
  once.
• To view formulas, choose Options on the Tools
  menu.
• Ctrl (accent key located next to the 1! Key)
  is the shortcut keystroke to turn on formulas

10
View tab in the Tools, Options dialog box
11
Perform Immediate and Delayed Calculations

• Formula calculations can be performed at a
  specific moment instead of when a worksheet is
  changed.
• Delayed calculation is performed by clicking the
  Manual button on the Calculation tab of the
  Options dialog box.
• Manual calculation is performed by pressing the
  F9 key.

12
Summary

• Worksheet formulas perform calculations on values
  in the worksheet.
• Relative, absolute, and mixed cell references are
  used to create formulas.
• Formulas may be created quickly by using the
  point-and-click method.

13
Summary

• A group of cells may be summed quickly by using
  the AutoSum button on the toolbar.
• You may view the formulas used to create values
  in a worksheet by making selections in the
  Options dialog box.
• You may delay calculation by choosing Manual
  Calculation in the Options dialog box.