Formulas

A main use of a spreadsheet is to calculate numbers. The basics of creating a calculation can be as simple as adding two figures in a single cell. To do this we create an expression called a formula in the cell that will display the result.

Using numbers

Important – there are no spaces in Formulas

  • Click in the cell where you want the result of your formula. A3
  • Type equal symbol = this alerts Excel that you are starting a formula
  • Type in the first number 456
  • Type the + plus symbol
  • Type in the next number 239
  • To complete the formula, press the Enter key

Note: The result is displayed in the cell but in the Formula Bar you can still see the Formula.

Using Cell References

When a formula includes figures from other cell, its called a Cell Reference.

  • Type the figures you want to tally into separate cells e.g. at the top of column A.
  • Click the mouse in the cell where you want the result e.g. A3
  • Type equal symbol  =
  • Type in the first Cell Reference “A1B”
  • Type the plus symbol +”
  • Type in the next Cell Reference “A2”

  • To complete the formula, press Enter

Using Point and Click

  • Click in the cell where you want the result of your formulag. A3
  • Type equal symbol =
  • Click your mouse on the first CELL to be referenced in the formula (A1)
  • Type the plus symbol +
  • Click in the second cell to be referenced (A2), continue adding extra cells as required.
  • To complete the formula, press the Enter key

Functions

Efficient formulas don’t always rely on expressions with arithmetic operators (addition, subtraction etc). Instead Excel provides many prebuilt Functions that can be quickly typed or added to a formula. Such as the function ‘SUM’, which calculates the sum of a range of cells. For example, to add the range of cells between A1 to A10 the Formula expression is this:  “=SUM(A1:A10)”.

Functions can be added to a formula by clicking the fx symbol on the Formula Bar or selected from the Formulas ribbon. Some of the most common Functions can be quickly tallied using  AutoSum, found on the Home ribbon in the Editing tool-group.

AutoSum

  • Click in the cell where you want the answer
  • Without releasing the mouse, move it up or across the range of cells to be included in the answer
  • Release the mouse
  • Click on the Home Tab
  • Click on the AutoSum tool in the Editing Group

  • The formula in the above example will appear in the cell ie, =SUM(A1:A3)

Tip: you can use the AutoSum tool or type this formula in yourself, remember – no spaces