COMP 2 PREFINAL

• At the end of the topic the learners will be able to: enter text and data into spreadsheet; and perform basic mathematical operations in a spreadsheet.
• References are provided for Introduction to Internet, Internet Technologies, and Reading the Internet
• Excel


  An electronic spreadsheet used to manipulate numerical data with formulas and built-in functions

  
• Excel is a spreadsheet application software from Microsoft

  
• Some spreadsheet programs are also available free of charge, such as Google application suite and WPS Office (for smart phone users)
• Richard Mattessich's paper on Budgeting Models and System Simulation started the concept of an electronic spreadsheet


  1961

  
• Rene K. Pardo and Remy Landau filed the patents for an algorithm based spreadsheet software called LANPAR


  1971

  
• LANPAR


  LANguage for Programming Arrays at Random
• Bricklin


  Dubbed as the Father of the Electronic Spreadsheet
• Getting Familiar with Excel


  • Start screen
  • Excel Interface elements
  • Backstage view
  • Workbooks and worksheets
  • Moving around in a worksheet
  • Data in Excel
  • Excel Formulas
  • Formatting

  
• Starting Excel


  1. Click on the Excel icon on your desktop

  2. Click on the Start button, point to All Programs, point to Microsoft Office, and click on Excel
• Ribbon


  Common across all applications in Microsoft Office for consistent look and feel
• Formula Bar


  Displays the formula in the active cell
• Backstage view


  Appears if you click the File tab
• Only one tab is active at any time, and the active tab is highlighted
• Mouse Pointers Used in Excel

  • Not provided
• Workbook


  Made up of worksheets and chart sheets
• Worksheet


  Has 16,384 columns and 1,048,576 rows
• Cell reference


  The letter for the column and the number of the row holding the cell, e.g. B5
• Excel Data Types


  • Text or label
  • Value
  • Formula
• Formatting Cells A1 to F4

  Not provided
• Merge Cells A1 to F4

  Click the Merge and Center button
• Wrap Text & Alignment

  1. Click the Wrap Text button

  2. Click the Center button

  3. Click the Middle Align button
• Setting Font and Fill Color

  1. Click on the Font Selection button and select Arial font

  2. Set font size to 24

  3. Select Bold as the font style

  4. Select a background color

  
• Change the worksheet Name

  1. Right click on Sheet1 tab and select Rename

  2. Enter a new name
• Saving your Worksheet


  1. Click on the File tab and select Save As

  2. Select the Excel Workbook option

  3. Enter an appropriate name for your workbook
• Printing a Worksheet


  1. Click the Office Button and select the Print command

  2. Select the print settings through the Print dialog box

  3. Use the Print Preview option to preview your sheet before printing
• Excel 2013 File Formats

  • Default format is .xlsx
  • Saving in .xls (Excel 2003) format is advised when sharing
  • Excel templates have .xltx format
  • Workbooks with macros are saved with .xlsm extension
• Writing Formulas


  1. Point calculation for a course: =C9*D9

  2. Formula for Total Credit Hours in cell C15: =SUM(C9,C10,C11,C12,C13)

  3. Formula for GPA in cell E15: =(E9+E10+E11+E12+E13)/C15
• Arithmetic Operators


  • Parentheses ( )
  • Exponentiation ^
  • Multiplication *
  • Division /
  • Addition +
  • Subtraction -
• Common Excel Functions


  • MAX
  • MIN
  • SUM
  • AVERAGE
  • ROUND
  • TRUNCATE
  • MODE
  • MEDIAN
  • COUNT
  • COUNTIF
  • COUNTIFS
  • SUMIF
• MEDIAN


  Returns the median of the given numbers. The median is the number in the middle of a set of numbers.
• COUNT


  Counts the number of cells that contain numbers and counts numbers within the list of arguments. Use COUNT to get the number of entries in a number field that is in a range or array of numbers.
• COUNTIF


  Counts the number of cells within a range that meet the given criteria. Range is one or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• COUNTIFS


  Counts the number of cells within a range that meet multiple criteria.
• SUMIF


  Adds the cells specified by a given criteria. Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. Sum_range are the actual cells to add if their corresponding cells in range match criteria.
• The columns are labeled A through Z; the rows are numbered in order.
• The block at which a row and column intersect is called a cell and its address is defined by the letter of the column and the number of the row.
• On the spreadsheet, cell A1 is outlined, or selected. This is the cell in which numeric data and or text can be entered.
• Across the top of the spreadsheet is a menu bar which will be utilized when necessary.