Spreadsheet Applications

• Spreadsheet


  A spreadsheet contains one or more worksheets
• Worksheet


  A worksheet is presented as a grid of cells
• Spreadsheets


  • Designed to allow applications to perform calculations and recalculations automatically
  • Cells can store data in a variety of forms, e.g. text, number, date, formula etc.
  • Spreadsheet data can be formatted as general, number, currency, date, time, percentage etc.
• Formatting


  • Can control the appearance of a cell, e.g. aligning text, changing the font or adding a border or background colour
  • Conditional formatting allows cell shading or font colour to be applied to a cell or group of cells if a specified condition is met
• Data validation


  • Involves the automatic checking of data at the input stage to ensure it is reasonable, sensible and within acceptable limits
  • Guarantees that data is present, and of the correct type, range or length
  • The computer displays checks data and either accepts the data, or rejects it and displays an error message
• Lookup function (VLOOKUP)


  • Uses the value on a selected cell to lookup a match in a column of a lookup table (vertical lookup)
  • Returns a value from a specified column from the same row of the lookup table
• Absolute cell reference


  When a formula is copied to other cells, part of the cell reference does not change as the formula is modified
• Macro


  A small program written to perform a repetitive task automatically
• Validation types


  • Presence check
  • Length check
  • Type check
  • Format check
  • Range check
• Simple functions

  • SUM
  • AVERAGE
  • MAX
  • MIN
• Formulas and functions allow a spreadsheet to perform calculations and automatic re-calculations
• When the formula or function is entered into a cell it can be replicated quickly down a column or across a row using the 'Fill' function