Excel Lesson 4 Entering Worksheet Formulas

1
Excel Lesson 4Entering Worksheet Formulas

• Microsoft Office 2007 Introductory

2
Objectives

• Enter and edit formulas.
• Distinguish between relative, absolute, and mixed
  cell references.
• Use the point-and-click method to enter formulas.
• Use the Sum button to view summary calculations.

3
Objectives (continued)

• Preview a calculation.
• Display formulas instead of results in the
  worksheet.
• Manually calculate formulas.

4
Vocabulary

• Absolute cell reference
• Formula
• Manual calculation
• Mixed cell reference
• Operand

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

5
What are Formulas?

• The equation used to calculate values in a cell
  is called a formula. Each formula begins with an
  equal sign ().
• The results of the calculation appear in the cell
  in which the formula is entered. The formula
  itself appears in the formula bar.
• For example, if you enter the formula 86 in
  cell B3, the value 14 appears in the cell, and
  the formula 86 appears in the formula bar when
  cell B3 is the active cell.

6
Entering a Formula

• Worksheet formulas consist of two components
  operands and operators.
• An operand is a constant (text or number) or
  cell reference used in a formula. You can type
  cell references in uppercase (A1) or lowercase
  (a1).
• An operator is a symbol that indicates the type
  of calculation to perform on the operands, such
  as a plus sign () for addition.

7
Entering a Formula (continued)

• Formulas can include more than one operator. The
  sequence used to calculate the value of a formula
  is called the order of evaluation.
• 1. Contents within parentheses are evaluated
  first. If there are multiple sets of
  parentheses, the innermost set of parentheses is
  evaluated first.
• 2. Mathematical operators are evaluated in the
  order of priority Exponentiation, Positive or
  negative, Multiplication or division, Addition or
  subtraction.
• 3. If two or more operators have the same order
  of evaluation, the equation is evaluated from
  left to right.

8
Editing Formulas

• If you enter a formula with an incorrect
  structure, a dialog box appears, explaining the
  error and providing a possible correction. You
  can accept the correction or choose to correct
  the formula yourself.
• If you discover that you need to make a
  correction, you can edit the formula. Click the
  cell with the formula you want to edit. Press the
  F2 key or double-click the cell to enter editing
  mode.

9
Comparing Relative, Absolute, and Mixed Cell
References

• Three types of cell references are used in
  formulas relative, absolute, and mixed.
• A relative cell reference adjusts to its new
  location when copied or moved.
• Absolute cell references do not change when
  copied or moved to a new cell. To create an
  absolute cell reference, you insert a dollar sign
  () before the column letter and before the row
• number.
• Cell references that contain both relative and
  absolute references are called mixed cell
  references. When formulas with mixed cell
  references are copied or moved, the row or column
  references preceded by a dollar sign do not
  change.

10
Creating Formulas Quickly

• To use the point-and-click method, simply click
  the cell instead of typing the cell reference.
• To use the Sum button, click the cell where you
  want the total to appear, and then click the Sum
  button. An outline appears around the range Excel
  selects. If you want to add numbers in a
  different range, drag to select those cells.
  Press the Enter key to complete the formula. The
  active cell displays the sum.

11
Previewing Calculations

• Whenever you select a range, the status bar shows
  the results of common calculations for the
  selected cells. These summary calculations appear
  without your having to enter a formula.
• By default, Average, Count, and Sum appear in the
  status bar. You can also display Numerical Count,
  Minimum, and Maximum.

12
Showing Formulas in the Worksheet

• When creating a worksheet with many formulas, you
  may find it simpler to organize formulas and
  detect formula errors when all formulas are
  visible.
• To do this, click the Formulas tab on the Ribbon,
  and then click the Show Formulas button in the
  Formula Auditing group.
• The formulas replace the formula results in the
  worksheet.

13
Calculating Formulas Manually

• When you need to edit a worksheet with many
  formulas, you can specify manual calculation,
  which lets you determine when Excel calculates
  the formulas.
• The Formulas tab on the Ribbon contains all the
  buttons you need when working with manual
  calculations. To switch to manual calculation,
  click the Calculation Options button in the
  Calculation group on the Formulas tab, and then
  click Manual.

14
Summary

• Formulas are equations used to calculate values
  in a cell, based on values referenced in other
  cells of the worksheet. Each formula begins with
  an equal sign and contains at least two operands
  and one operator.
• Formulas can include more than one operator. The
  order of evaluation determines the sequence used
  to calculate the value of a formula.
• If you enter a formula with an incorrect
  structure, Excel can correct the error for you,
  or you can choose to edit it yourself. To edit a
  formula, click the cell with the formula and then
  make changes in the formula bar. You can also
  double-click a formula and then edit the formula
  directly in the cell.

15
Summary (continued)

• Relative references adjust to a new location when
  copied or moved. Absolute references do not
  change, regardless of where they are copied or
  moved. Mixed references contain both relative and
  absolute references.
• Formulas can be created quickly using the
  point-and-click method. With this method, you
  insert a cell reference in a formula by clicking
  the cell rather than typing its column letter and
  row number.
• The Sum button in the Editing group on the Home
  tab inserts a formula with the SUM function,
  which adds the value of cells in the specified
  range.

16
Summary (continued)

• The status bar shows a preview of common
  formulas, including Average, Count, and Sum, when
  you select a range of cells. You can choose which
  formula previews to show or hide.
• You can view the formulas in a worksheet, instead
  of the formula results, by clicking the Show
  Formulas button in the Formula Auditing group on
  the Formulas tab.
• Excel calculates formula results in a worksheet
  when you enter the formula, and recalculates the
  results whenever the cells used in that formula
  change.
• When you need to edit a worksheet with many
  formulas, you can click the Calculations Options
  button in the Calculation group on the Formulas
  tab, and then click Manual. When you want to
  calculate the formula results, click the
  Calculate Now button.