ISTN101

• A spreadsheet is an electronic tool used to process numbers
• The first spreadsheets appeared in the early 1980's and helped make personal computers popular
• All spreadsheet software like Microsoft Excel, Lotus 1-2-3 and Quattro Pro have many common features

  
• Spreadsheets


  Can be used to organise, store and analyse data

  
• Managers


  Can use a spreadsheet to help make decisions about their business
• Spreadsheets


  Can perform complex as well as simple mathematical and financial calculations quickly
• Spreadsheets


  Can be used to keep track of household income and expenses

  
• A manual spreadsheet using pen and paper is tedious, time-consuming and prone to errors
• Electronic spreadsheets can be updated easily and quickly
• Microsoft Excel


  A popular type of electronic spreadsheet
• Excel 2016 workbook


  A collection of worksheets
• Worksheet


  Consists of a grid of cells divided into rows and columns
• Cell


  The intersection of a row and column
• Cell reference


  Unique address of a cell determined by column and row identifiers
• Data can only be entered or edited in the active cell
• Gridlines


  Horizontal and vertical lines on a worksheet
• Name Box


  Displays the cell reference of the active cell
• Formula bar


  Shows the contents of the active cell
• Selecting cells


  1. Click cell to make it active

  2. Click row/column heading to select row/column

  3. Click top-left corner to select entire worksheet
• Navigating worksheet


  1. Use mouse, arrow keys, Tab, Page Up/Down, Home, Ctrl+Home, Ctrl+End

  2. Press F5 to go to specific cell
• Opening a workbook


  Click File tab, select Open, browse to file location, click Open
• Creating a new workbook

  Click File tab, select New, choose Blank Workbook
• Saving a workbook


  1. Click Save button on Quick Access Toolbar or use Ctrl+S

  2. First save uses Save As dialog to name file and choose location
• Workbook file types


  • Excel 2016 (.xlsx)
  • Template (.xltx)
  • Text file (.txt or .csv)
• Cell entry


  • Text
  • Values
  • Formulas
• Formula


  Equation that performs mathematical operations on data in worksheet
• Formula components


  • Constants
  • Cell references
  • Mathematical operators
  • Functions
• Editing cell contents


  1. Select cell, click formula bar, make changes, press Enter

  2. Double-click cell, make changes, press Enter

  3. Select cell, press F2, make changes, press Enter
• Cell references in formulas

  Refer to cell addresses rather than actual values, enabling automatic recalculation when values change
• Cell range


  Group of two or more cells treated as one unit
• Adjacent cell range


  Rectangular group of cells defined by addresses of top-left and bottom-right cells
• Non-adjacent cell ranges can also be selected
• If the Sales figure for Week 2 is increased to 1500, the profit for Week 2 will remain the same. This is incorrect so that the formula in C4 will need to be changed to =1500-800 and then the profit will be correct.
• Cell references


  Should be used in the formula to calculate the profit. Therefore cell C4 should contain the formula =C2-C3. Then whenever a Sales or Costs figure is changed, the profit calculation is automatically updated.
• Cell range


  A group of two or more cells that are treated as one unit during an operation
• Adjacent cell range


  • Cells occurring in a rectangular shape, defined by the cell address of the cell in the upper-left corner and the cell address of the cell in the lower-right corner separated by a colon
• Selecting an adjacent cell range with the mouse

  Position the mouse pointer over the first cell, press and hold the left mouse button as you drag the mouse over the remaining cells, release the mouse button
• Selecting an adjacent cell range with the keyboard

  Select the first cell, press the Extend Selection key F8, use the arrow keys to extend the selection, press the F8 key again to switch off the extend mode
• Non-adjacent cell range


  • A group of cells that are dotted around the worksheet and could contain a number of smaller sub-groups of adjacent cells, written with commas to separate the individual cells or smaller sub-groups
• Selecting a non-adjacent cell range with the mouse

  Select the first cell (or sub-group of adjacent cells), hold down the Ctrl key and then select further cells