Spreadsheet Fundamentals

Introduction to Computers Part III: Excel

• Excel is a spreadsheet application within the Microsoft Office suite
• Microsoft Office suite also includes Word (word processor), Access (database management), PowerPoint (presentation software), and Outlook
• Spreadsheets are electronic equivalents of accounting worksheets
• Spreadsheets organize data into rows and columns, cells at the intersection
• Spreadsheets are used for a wide range of manipulations: simple arithmetic (totals, products, percentages), complex automated calculations, and analysis

Spreadsheet Uses

• Widely used in diverse fields (research, industry, business)
• Used for storing, manipulating, and comparing data
• Used for planning and forecasting
• Specific uses in accounting include:
  • Budget preparation
  • Balance sheet preparation
  • Trial balance
  • Cash flow analysis
  • Project costing
  • Inventory management
  • Payroll processing
  • Financial planning
• Other uses include:
  • Scientific computations (averages, standard deviations, variance, R-squared, etc.)
  • Statistical analysis (regression coefficients, ANOVA, solving simultaneous equations, creating frequency distribution tables)
  • Data representation (pie charts, histograms, line graphs)
    • Used by hospitals and statistical divisions for mortality and morbidity rates, to track trends in disease outbreaks
  • Currency conversions in forex bureaus and multi-national companies
  • Database management (sorting data, extracting information, pay vouchers)
  • Answering "what if" scenarios in business (e.g., builder design of storm drains, effect of storm drain diameter on water flow rates, business profits based on income/expenditure)

Spreadsheet Packages

• Several packages exist, including AS-EASY-AS, SUPERCALC, LOTUS 1-2-3, SYMPHONY, QUATROPRO, FRAMEWORK IV, and EXCEL, among others.

Spreadsheets (Computer Program vs Worksheet)

• Excel can refer to both the computer program and the worksheet itself
• Some programs use "worksheet" to refer to the sheet where work is done

Overview of Spreadsheet Programs

• Parts of a spreadsheet include sections/areas:
  • Cell Address: location of cell
  • Cell: individual box for data entry
  • Workbook: entire spreadsheet file
  • Status bar: lower tool panel
  • Column: vertical area
  • Row: horizontal area
  • Working Sheet: active/current sheet

Starting and Quitting Excel

• To start:
  1. Click the Start button on the task bar
  2. Locate and click on the Excel program
• To quit:
  1. Ensure your work is saved
  2. Click the 'Close' button on the title bar

Cells

• Cells are referenced by column and row letters/numbers (e.g., B10)
• Active cell reference is displayed in the name box

Working in a Spreadsheet

• Input data by selecting a cell and typing
• To replace data, click on the specific cell and then type the new data.
• The three types of data are Text, Numbers and Formulas.

Entering Text & Numbers

• Character limit in a cell is 255
• Text data aligns left, numerical data right
• Entering numbers as text (e.g., '25,000 or ="25,000").
• Numbers exceeding cell width may be displayed with # signs or scientific notation.

Entering Dates & Times

• Excel recognizes most common date & time formats
• Internally, dates and times are stored as serial numbers
  • A date is represented as the number of days since 1/1/1900
  • A time is a decimal fraction of a 24-hour day
• Entering dates with formats: m/d/yy dd/mm/yy d-mmm-yy or mm/dd/yy etc.
• Correctly entered dates are of format mm/dd/yyyy (month/date/year)
• Default formats of dates / times accessed by pressing appropriate shortcut keys
• Valid date entry aligns to right edge of cell (Numerical Format), unformatted data to right also.

Cutting & Pasting Data

• To copy data:
  1. Select the cell
  2. Select the "Home" tab
  3. Click "Copy" from Clipboard Group OR
  4. Use Ctrl+C keyboard shortcut.
• To paste copied data:
  1. Select target cell
  2. Select the "Home" tab
  3. Click "Paste" from Clipboard Group. OR
  4. Use Ctrl+V keyboard shortcut.

Selecting Cells

• Highlight cells by clicking and dragging across desired cells.

Adding & Deleting Rows/Columns

• Add:
  1. Select row/column heading
  2. Go to "Home" tab
  3. Click "Insert" in "Cells" group
• Delete:
  1. Select row/column heading
  2. Go to "Home" tab
  3. Click "Delete" in "Cells" group

Finding the Right Size

• Expand column width/row height by placing cursor over the column/row edge, and dragging to desired width/height.

Inserting a Chart

• Select data to be charted
• Use "Insert" tab to choose a chart type

Formulas

• Formulas are the core of spreadsheet calculations, essential for performing virtually all calculations
• They generate results instead of being data directly in the cell
• May involve arithmetic, comparison and text evaluation operations
• Must begin with an equal sign (=)
• Incorporate operators (+, -, *, /, ^,…), values or constants, functions, parentheses, cell references, and names
• Allows automatic recalculation when dependent data is changed.

Cell Referencing

• Cell references (addresses) refer to other cells (e.g., A5, B10, C15)
• Different cell reference types (absolute, relative, mixed)
• Relative reference: adjusts when copied to different cells (e.g., A1+B1 in one cell might become A 2 +B2 when copied down)
• Absolute reference: doesn't change when copied or transferred (e.g A$1 +$B$1 stays A1+B1)
• Mixed reference: either column or row stays fixed (e.g.,$A1+B1 becomes $A2+B2)
• Circular reference: a formula that references itself, to be avoided

Three-D Referencing

• Using cell ranges across multiple sheets within a workbook.

Operators in Formulas

• Arithmetic operators (+, -, *, /, ^,%) perform calculations
• Comparison operators (=, >, <, >=, <=, <>) compare values (result = True or False)

Functions

• Functions (e.g., SUM, AVERAGE, MAX, MIN) are pre-defined for performing specific tasks.
• They accept arguments placed in parenthesis and separated by commas
• Types
  1. No arguments (e.g., PI(),NOW())
  2. Fixed number of arguments (e.g., SUM())
  3. Variable number of arguments (e.g., SUM())
• Entering functions: use "insert function command".

Logical Functions

• Logical functions test conditions
  • TRUE() - returns TRUE
  • FALSE() - returns FALSE
  • NOT() - reverses logical result
  • AND() - returns TRUE only if all arguments are TRUE, FALSE otherwise.
  • OR()- returns TRUE if at least one argument is TRUE.
  • IF()-evaluates a logical test and returns one value if true and another if false

Protecting Work Sheets

• Methods to protect the worksheet content and formatting by restricting editing.

Exercises and Assignments

• Several exercises/assignments relating to practical applications of the concepts covered in the materials. Specific tasks related to filling in formulas in designated cells, calculations, and determining appropriate logical conditions for various activities.