Topic 10: Spreadsheets

Topic 10: Spreadsheets

1. Cells

   • A cell reference always starts with a letter and ends with a number
     • D25
     • A range of cells is a set of cells in a row or column
     • Data entered into cells can be different data types
     • Each cell can contain
       • Labels – any text in a cell describing the data in a row or column(titles, headings that identify data)
       • Data – any values (text or numbers) that are entered for the use of calculation
       • Formulas and functions – mathematical calculations carried out automatically

2. Formulas

   • Must start with an =
   • Formulas perform calculations on cell content and appear in the formula bar.
   • The result of a formula is automatically recalculated every time the data on a spreadsheet changes.
   • =(B3*C6)+E12
     • + Add
     • - Subtract
     • * Multiply
     • / Divide
   • Functions
     • Must start with an =
     • specialised calculations built in to the spreadsheet software.
     • =SUM(D2:F7)
       • SUM, COUNT, AVERAGE, MIN, MAX, IF

3. Formatting

   • The content of a cell can be formatted
     • Font style, size, colour, border style, alignment, cell colour
       • Multiple cells can be merged in to one cell
   • Cells are often formatted to reflect the data that is held in them. Data types include:
     • Currency, number, real, integer (whole number), date, text

4. Graphs and charts

   • allows us to notice patterns & trends more easily
   • Specific data can be selected on a spreadsheet and a chart/graph of this data can be created.
   • Bar charts – to compare values
   • Pie charts – to show how a total is divided among a number of different categories.
   • Line graph – display trends

5. Cell referencing

   • Formula is copied by ‘filling down’ the cell references in cells are adjusted as you drag the pointer down the page.
   • Relative cell references
     • Uses the relative address of the cells based on their position in the worksheet.
     • Relative cell references is when a cell is used in a formula and the formula is copied to a new cell reference (address) , the cell references (cell address) changes to take account of the formula’s new position.
   • Absolute cell references
     • use the $ character in front of the letter and number that are required to stay the same.
       • Absolute cell referencing is where a reference to a cell used in a formula where, when the formula is copied to a new cell reference (cell address), the cell reference (cell address) does not change. It is glued in place by the $ character.
         • =$B3 would ‘glue’ column B
         • =B$3 would ‘glue’ row 3
         • =$b$3 would ‘glue’ that specific cell

6. Benefits of using a spreadsheet

   • Automatic recalculation
     • Every time the content of a cell is changed, calculations are performed and other cells are updated if necessary.
   • Accurate calculations
     • Providing the formulas have been set up correctly, the formulas will always produce a correct calculation
   • Graphs/Charts
     • Can be drawn using data in selected cells
   • Replication
     • Once the first formula is correctly entered, it is possible to drag across to adjacent cells in a row or column so the computer copies the pattern of the calculation.
   • What if calculations
     • Formulas can be reused and stored, calculations involving large numbers are possible
     • Changing the data in a spreadsheet model allows you to make and test predictions
       • They allow you to use a spreadsheet to see what would happen if certain figures changed in a spreadsheet.