Microsoft Excel Part 2: Opening Files, Entering and Copying Formulae

OPENING FILES

This tutorial assumes that you have already entered and saved the sheet produced in the tutorial Microsoft Excel Part 1

 To Open and Existing Spreadsheet file from your U:\drive
  • Open Excel
  • Click on the File Open icon i.e.
  • Find your U:\ drive directory
  • Double Click on your saved file, Mysheet, which should load

The opened spreadsheet should display data something similar to

ENTERING FORMULAE

Formula cells create calculations on the contents of other cells (e.g. Totals or Averages)

All formulae begin with an = sign

The result of the calculation is seen in the cell.

The formula can be seen in the Edit Bar where it can be altered if needed.

 To enter a formula

Example 1: Adding up a column

  • Select cell A8 - (move to it with the mouse and click in the cell)
  • Enter the word TOTAL . Then

Either

  • Move to cell B8. Click on the cell.
  • Enter = SUM(B4:B7) and press Enter

Or

  • From the ToolBar Select . This symbol means "Sum of".
  • Press Enter
  • You will see:


Example 2: Averaging a Column

  • Move to cell A9. Enter the word "Average"
  • Move to cell B9. Enter = AVERAGE(B4:B7) press return

B9 should show the average of the three items in column B (22.3333)

The Edit Bar should display the formula.

Example 3: Direct Entry (e.g. GST calculations)

  • Move to cell A11. Enter GST
  • Move to cell B11. Enter = B8*7.0%

B11 should show the GST on the amount in cell B8. (4.69)

The Edit Bar should display the formula.

COPYING FORMULAE

The equivalent formula for summing column B needs to be replicated in cells C8 to E8. It is very common to require repeating formulae in spreadsheets, so Excel makes it easy for us by using using a technique called a COPY HANDLE

The value of any cell or groups of cells can be copied in this way. Try it with dates

 To copy formulae

Example Copying the Summing Columns formulae to all four columns

  • Move the cursor to cell B8
  • Place the cross of the pointer on the COPY HANDLE (small square on the bottom corner of the cell) The pointer should change to a small black cross.
  • Click and hold down the left button on the mouse
  • Keeping the button depressed drag the mouse across row 8 up to and to include E8. Release the button. (Click and drag)


C8 to E8 should contain the results of the formulae SUM(C4:C8) to SUM(E4:E8). This form of copying is called RELATIVE REPLICATION - notice how the formula has changed in each column.

  • Use the same technique to copy the formula in cell B9 to C9, D9 and E9 to work out the average for each column.
  • Use the same technique to copy the formula in cell B11 to C11, D11 and E11 to work out the GST for each of the column totals.