CA ICA IExcel Lessons 3 and 4

Excel Lesson 4Excel Lesson 4√ Cell References √

Formula Features

√Other

Terminology

 absolute cell

reference  AutoSum

insert rows/columns

 mixed cell

reference  formulas delete rows/columns

 relative cell

reference  operand filling

      operatorCopycut paste

order of evaluation (order of operation)

freezing

SUM splitting

create formulas

solve formulas

Insert and DeleteInsert and DeleteInsert Rows and Columns is

used to add a row or column in a worksheet.

Delete Rows and Columns is used to remove a column or row in a worksheet.

Filling, Splitting and Filling, Splitting and FreezingFreezingFill or Filling copies data to adjacent

cells; is especially good for copying formulas.

Split or Splitting divides the screen into two or four parts.

◦ It allows you to view different parts of a large worksheet at the same time.

Cut is used to remove or move a cell.

Copy is used to make a duplicate of a cell.

Paste is used to move a cell from the clipboard to the worksheet.

Freezing keeps row or column titles on the screen no matter where you scroll in the worksheet.

Cell ReferencesCell References Relative adjusts the row and column to its

new location when copied or moved. ◦ D14 is an example of a relative cell reference.

Mixed adjusts either the row or column to its new location when copied or moved.

◦ D$14 is an example of a mixed cell reference. Absolute does not adjust the row and

column to its new location when copied or moved.

◦ $D$14 is an example of an absolute cell reference.•To change a cell so that the row or column does not change, you should put a $ in front of either the row number or column letter.

•To change a cell so that it does not change at all, you must put a $ in front of both the column and row.

•A quick and easy way to change a cell or range from relative to absolute to mixed and back to relative is F4.

Formula FeaturesFormula Features Formulas are equations that perform

calculations on values in your worksheet.

◦ In Excel, formulas begin with =.

Autosum adds the values that are contained in a range of cells.

◦ ∑ is the symbol that indicates autosum feature.

Sum is a function used to add the values that are contained in a range of cells.

◦ =SUM(B4:B8)

Formula FeaturesFormula FeaturesOperator - Symbols such as *, +, -, and / are

examples of operators.Operand – The numbers or cell references

used in formulas. ◦ = B4 * 8 B4 and 8 are operands; * is an operator.

Order of Operations or Order of EvaluationThe order of evaluation (order of operation) in order is 1.Parenthesis P2.Exponents E3.Multiplication/division MD4.Addition/subtraction AS

Please Excuse My Dear Aunt Sally!

Be able to write formulas:Be able to write formulas:Write the formula to multiply the contents of

A3 by B3.

= A3 * B3

Add contents of cells C4 through C14.

= SUM (C4:C14)

Subtract the contents D10 from D8.

= D8 – D10

Divide the contents E4 by F7.

= E4 / F7

Raise the contents of cell G3 to the 3rd power.

= G3 ^ 3

Be able to solve formulas:Be able to solve formulas: =5 * 4 +2

= 22

=5 * (4 + 2)= 30

=2 + 10 / 2= 7

=(2 + 10) / 2= 6

=2^2 + 4= 8