Introduction to Spreadsheets

1
Introduction to Spreadsheets

• Spreadsheet is a computerized ledger
• Divided into rows and columns
• Columns identified with alphabetic headings
• Rows identified with numeric headings
• Cell references
• Constants--entries that do not change
• Formulas--combination of constants and functions

2
History of Spreadsheets

• VisiCalc first commercial spreadsheet
• LOTUS 1-2-3 1980s major spreadsheet.
• Early justification for the PC
• Most common Excel (Microsoft)
• All office suite packages support spreadsheets

3
Spreadsheet Use

• Financial calculations
• Statistics
• Experimental results
• Schedules

4
Introduction to Excel

• Common user interface on all Office applications
• Menus and toolbars are similar to Word and Power
  Point
• Worksheet is an Excel spreadsheet
• Workbook contains one or more worksheets
• Toolbars--Standard and Formatting
• File menu--Save, Save As, Open and Print commands

5
Excel XP
6
Excel XP Worksheet
7
Formulas

• Formula a set of instructions to produce the
  results of a calculation
• Function predefined shortcut to make
  calculations easier
• Functions can be included as part of a formula

8
AutoSum

• Most common calculation
• Has its own button
• Highlight the column or row to sum
• Allow 1 or more empty cells at end of the
  highlighted area

9
Necessary Formula Prereqs

• Mathematical order of operations
• How cells are referenced
• Available functions
• How to enter a formula

10
Order of Operations

• Certain math operations are more important than
  others
• Exact order of evaluation is
• 1 - - divide by 100
• 2 - - raise number to a power
• 3 - - multiply
• 3 - / - divide
• 4 - - add
• 4 - - - subtract

11
Order of Operations

• Operation with the same precedence evaluated from
  left to right
• Can control evaluation order by using ()s
• 3 8 10 / 2 - 3 26
• 3 (8 10) / 2 - 3 24
• 3 (8 10)/(2 - 3) -54

12
Built-in Functions

• Excel provides functions for your use
• Available Functions
• Business/Financial
• Date and Time
• Information (on spreadsheet)
• Logical (including IF tests)

13
Built-in Functions

• Numeric
• Statistical
• Text
• Trigonometric
• Look at Help for information about specific
  functions

14
Inserting a Function

• Use the Insert Function command from the Insert
  menu
• Use the list box to select the name of the
  function
• functions categorized by function
• Let the Wizard help you enter the arguments

15
Getting the most from Excel

• Relative versus absolute addressing in a
  worksheet
• Knowing the difference and when to use each when
  copying makes setting up your worksheet more
  efficient and more accurate
• Mixed references
• Either the row or the column is absolute the
  other is relative

16
Using Functions in Excel

• Statistical Functions MAX,MIN, AVERAGE, COUNT
  and COUNTA
• Use functions instead of arithmetic expressions
• IF function enhances decision making
• allows for different results based on different
  conditions
• VLOOKUP(vertical lookup) Function
• assigns a value to a cell based on a numeric
  value in another cell

17
The IF function

• Allows for different results, based on a
  condition
• for example, if you work over forty hours in a
  week, you will receive overtime pay
• Requires three arguments
• a condition, which Excel must be able to evaluate
  as true or false
• a value if true
• a value if false
• The value if true and value if false may contain
  additional (nested) IF functions for more complex
  decisions.

18
The VLOOKUP function

• Allows Excel to look up a value in a table and
  return a related value
• for example, Excel can look up your average in
  this class and return your grade
• Requires three arguments
• the numeric value (or cell) to look up
• the range of the table
• the column number containing the value you want
  to return

19
Finished Grade Book
20
Summary

• Financial functions (PMT and FV)
• Statistical functions (MAX, MIN, AVERAGE, and
  COUNT)
• Decision making functions (IF and VLOOKUP)
• Scroll Freeze Panes to work with large
  worksheets
• Use Page Setup to control how the worksheet
  prints
• Use AutoFilter command to display only rows that
  meet certain criteria

21
Spreadsheet Charts

• Business graphics one of most exciting Windows
  applications
• Determine message of chart
• Create charts using Chart Wizard
• Plot multiple data sets on a single chart
• Dynamically link a chart to a memo

22
What is a Chart?

• A graphic representation of data in a worksheet
• Based on descriptive (text) entries called
  category labels and numeric values called data
  points
• Data series a grouping of data points

23
Chart Types

• Always remember to keep it simple and clear
• Pie and Exploded pie charts are effective for
  displaying proportional relationships
• Column charts are used for displaying numbers
• Bar charts show numbers horizontally

24
Pie Charts
25
Column Charts
26
Creating A Chart

• Select the cells containing your labels and data
  points
• Use the Chart Wizard a four step process
• Step one choose a chart type
• Step two review data series
• Step three final touches
• Step four where to store

27
Chart Wizard
28
Finishing the Chart

• Embedding in a worksheet or separate chart sheet
• Enhance with Drawing toolbar

29
Using the Drawing toolbar
30
Multiple Data Series

• Decide what message to convey with the chart
• What do you want to emphasize?
• Rows versus columns
• If data series are in rows the Chart Wizard will
  use first row for X axis and use first column for
  legend text
• If data series are in columns the Chart Wizard
  will use first column for X axis and use first
  row for legend text

31
Stacked Column Charts

• A column chart that depicts totals by category
  instead of each individual data point
• Useful when you want to compare totals by category

32
Object Linking and Embedding

• Create a document in one application that
  contains objects from another application
• For example, contain a Word document that
  contains objects from an Excel worksheet
• Embedded object is stored in the document
• an Excel chart becomes part of the Word document
• Linked object is stored in its own file
• any change in this file is automatically
  reflected in the main document
• Multitasking the ability to have more than one
  application open at the same time

33
Summary

• Charts are an easy to understand way to show data
• Easiest way to create charts is with the Chart
  Wizard
• Charts can be embedded or stored in a separate
  file
• Multiple data series may be stored in rows
  columns

34
Summary continued

• OLE enables creation of compound documents
• Embedding means storing an object within the
  compound document
• Linking means storing the object in its own
  file, and the compound document uses this file

35
Objectives (1 of 2)

• Define a macro
• Record and run a macro view and edit a simple
  macro
• Use the InputBox and MsgBox statements
• Use a keyboard shortcut or customized toolbar to
  execute a macro

36
Objectives (2 of 2)

• Describe the function of the Personal Macro
  workbook
• Use the Step Into command to execute a macro one
  statement at a time
• Use Copy and Paste to duplicate an existing macro
• Use Visual Basic IF and DO statements to make
  decisions

37
Overview

• Use macros to automate repetitious tasks
• Macro is a set of instructions for Excel
• Macro instructions are written in the Visual
  Basic for Applications (VBA) programming language
• Use the macro recorder to create macros
• Create more powerful macros

38
Introduction to Macros

• Macro recorder stores Excel commands
• Commands are written in Visual Basic for
  Applications
• Macros can be displayed with Visual Basic Editor
  (VBE)
• Use VBE to create, edit, run, and debug (fix)
  Excel macros
• Use Project Explorer in left-hand pane to locate
  macro modules
• Statements appear in Code window

39
Macro Statements

• Sub statement, followed by the name of the macro
• Sub is short for subroutine
• Comments begin with apostrophe ()
• Comments provide useful information about the
  purpose of the macro
• With and End With statements
• Perform multiple actions on the same object
• End Sub statement
• Defines the end of the macro

40
Other Macro Features

• Statements are color-coded
• Visual Basic toolbar has seven buttons
• Customize the Visual Basic toolbar
• Step Into Command helps you debug a macro
• Opening a file with a macro will prompt a
  question about viruses

41
A Macro
Visual Basic Editor Project Explorer upper
left VBA code on right
42
Record Macro
43
Testing the Macro
44
Cell References (1 of 2)

• Relative versus Absolute References
• Critical to specify whether cell references are
  absolute or relative
• Absolute cell addresses are constant
• Macro refers to that cell every time the macro is
  run
• Relative cell addresses change
• VBA uses offset to indicate space from active
  cell every time the macro is run

45
Cell References (2 of 2)

• Relative references
• ActiveCell.Offset(1,0).Range(A1).Select
• Relative reference above means the cell one row
  below the active cell
• Offset of (0,1) means the cell one column to the
  right of the active cell
• Negative numbers are used for cells above or to
  the left of the active cell
• Absolute references
• Range(A1).Select
• Always refers to cell A1

46
Personal Macro Workbook

• A workbook that opens automatically whenever
  Excel is opened
• Any macro stored in this workbook can be used by
  any open workbook

47
Visual Basic for Applications

• VBA is a subset of Visual Basic
• Macros are converted to VBA programs
• Programs known as procedures
• Also called subroutines thus the word Sub at the
  beginning of the macro
• VBA allows you to modify macros you record with
  the Macro Recorder

48
Common VBA statements

• MsgBox statement
• Displays information to the user while the macro
  is executing
• InputBox function
• Accepts information from the user while the macro
  is executing
• Information is stored in a cell for use later in
  the procedure

49
The MsgBox Statement
MsgBox statement includes the text to be
displayed in the message box. The message box
that appears when the macro is run is displayed
below.
50
The InputBox Function
InputBox statement includes the text to be
displayed in the input box. The input box that
appears when the macro is run is displayed below.
51
Loops and Decision Making

• Including IF and Do statements allows for testing
• IF statement tests a condition
• Condition must be evaluated as true or false
• Includes a series of commands to execute if the
  condition is true
• Includes an optional else clause with commands to
  execute if the condition is false
• DO statement repeats a block of statements until
  a condition becomes true
• Commonly called a loop

52
Summary (1 of 2)

• A macro automates a repetitive task
• Macros are recorded using the Macro Recorder
• Record using either absolute or relative
  references
• Macros are written in Visual Basic for
  Applications programming language
• Generic macros should be stored in the Personal
  Macro workbook
• They will always be available to any workbook

53
Summary (2 of 2)

• Macros are run with a toolbar, keystroke or a
  button
• Comments contain reminders to the programmer
• MsgBox and InputBox VBA statements make macros
  interactive
• More powerful Excel macros can be programmed with
  IF and DO VBA statements