Introduction to Excel Spreadsheets

1
Introduction to Excel Spreadsheets

• Creating Worksheets
• for Decision Makers

2
Spreadsheet Applications

• Computer program
• Analyze numeric information
• Help make meaningful business decisions

• Dan Bricklin and Bob Frankston
• Invented in 1979
• VisiCalc
• Sold and developed into Lotus 1-2-3

3
What-if Analysis

• Classic use of spreadsheets
• Involves modifying values and reviewing their
  affect on other values
• Recalculation automatically allows review
• Assumption cells
• Cells upon which other formulas depend
• Values changed to observe effect

4
Typical Spreadsheet
5
Anatomy of the Excel Window
6
Basic Vocabulary
Cell Address
Worksheet
7
Moving Around a Worksheet
Using the Keyboard
Keystroke Action
Up Arrow Moves up one cellDown Arrow Moves
down one cellLeft arrow Moves left one
cellRight arrow Moves right one
cell PgUp Moves active cell up one
screenPgDn Moves active cell down one
screen Home Moves active cell to column A of
current row CtrlHome Moves the active cell to
cell A1CtrlEnd Moves to the lower, rightmost
active corner of the worksheet F5 (function
key) Opens the Go To dialog box in which you
can enter any cell address
8
Moving Around a Worksheet
Using the Mouse

• Action
• Click the cell
• Click on scroll bars to reveal cells not showing
• Mouse pointer
• Indicates current position of mouse
• Changes shape to indicate what action can be
  performed
• Arrow select item from menu
• I-beam type text in formula bar
• White plus sign as move over worksheet surface

9
Worksheet Limits
IV
Last cell in a worksheet!
Cell address of IV65536
65536
10
Workbooks
Maximum number of Workbooks is 255.
Initial number loaded can be setby user.
Worksheet tabs
11
Excel Data Types

• Three different types of data
• Label or text
• Value
• Formula
• Function Special prewritten formula

12
Text

• Set of characters not interpreted as a value
• Cannot be used for calculations
• Left justified in cell (default setting)
• Examples
• telephone number 227-1240
• security number 372-70-9654
• column headings

13
Text Within a Cell
Cell not large enough, but adjacent cell is filled
14
Value

• Numbers, dates, times
• Can be used for calculations
• Right justified in cell (default setting)
• Examples
• 378 11/29/94 44031
  (9876)
• Recognized as number if begins with
• _at_ or any digit

Number Date Time
Negative Number
15
Formula

• Specifies calculations to be performed
• Begins with an equal sign ()
• Can contain cell references, arithmetic
  operators, values and built-in functions
• Recalculates when cell reference changes
• Example A1 B2

Note If forget the equal sign then it becomes
a label (no math done!)
16
Functions

• Built-in formula that provides shortcut for
  complex calculations
• Start with functions name followed by set of
  parentheses
• Parentheses enclose the argument list
• Cells or other expressions needed for the
  calculations
• General syntax of Excel Function
• Function name (argument1,argument2,...)

17
Excel Functions

• Identified by descriptive abbreviation or word
• examples SUM(D14D18) AVERAGE(A1A8)
  MAX(B6B13)

18
Function Categories

• Lookup Reference
• Database
• Text
• Information

• Financial
• Date Time
• Math Trig
• Statistical
• Logical

19
Common Functions

• SUM(cell range)
• Total of all cells listed as arguments
• MIN(cell range)
• Smallest number within the cell range named
• MAX(cell range)
• Largest number within the cell range named
• AVERAGE(cell range)
• Total of all the cells divided by the total
  number of cells listed

20
Cell Ranges

• More than one cell or a block of cells
• Always rectangular or square in shape
• Specify by naming the upper left cell, a colon,
  and the lower right cell
• SUM function example
• SUM(A11B14)

21
Arithmetic Operators

• Parentheses ( ) (53)/2 4
• Exponentiation 52 25
• Multiplication 52 10
• Division / 5/2 2.5
• Addition 52 7
• Subtraction - 5-2 3

22
Excels Order of Operators

• ( ) Parentheses
• Exponentiation
• / Multiplication, division
• - Addition, subtraction

• Shown from highest precedence to lowest
• Paired operators handled as you come to them
  going left to right

5 2 4 2 6 / 3
16
32
2
-27
-25
Exponentiation
Multiplication
Divisionn
Subtraction
Addition
23
Creating Formulas

times
times
450
24
Editing Cell Entries

• Select cell
• Click in formula bar or press F2
• Correct mistake
• Type in change
• Backspace key (removes character on left)
• Delete key (removes character on right)
• Highlight by dragging over characters to change,
  then type correction (will replace what is
  highlighted)
• Press Enter

25
An Excel worksheet cell can contain text, values,
formula, and _________ entries.
function
Formula
Value
Value
Text
Text
Text
Formula
26

• Planning and Creating a Worksheet

27
Objectives

• Plan and document a worksheet
• Create formulas containing cell references and
  mathematical operators
• Write functions including Sum, Average, Max, and
  Min
• Use Excels AutoSum feature to automatically
  write Sum functions

28
Entering Worksheet Title and Column Labels

• Enter labels first
• Acts as guide for value entry
• Row and Column first
• Leave space at top for titles
• Can always insert rows or columns later

29
Using Mathematical Operators

• Formulas begin with equal sign () followed by
  mixture of cell references, values and
  mathematical operators
• Mathematical operator
• symbol that represents an arithmetic operation
• Precedence order
• order to calculate each part of the formula

30
Copying Cells

• Copy and Paste using Clipboard
• Methods
• Buttons from toolbar
• Edit, Copy
• Drag and Drop using Fill Handle
• Right Click and choose Copy from menu

31
AutoSum Function
32
Copy and Paste Smart Tag
33
Copy Error Smart Tag
Smart tag indicates an error may have occurred
Copied formulas containing errors
Paste Options Smart Tag
34
Relative and Absolute Addressing

• relative address B4
• absolute address B4
• mixed address
• B4 Column is fixed, row relative
• B4 Column relative, row is fixed

35
Quick Way to Switch

• Type formula in with relative addresses
• Insert cursor in address you want to change
  format
• Press F4 key
• Toggles between options

B4E5
B4
B4
B4
B4
36
Inserting and Deleting Rows

• Excel automatically adjusts all cell references
  in formulas
• Select row, will insert row above
• Select column, will insert column in front of
  column
• To delete, Edit menu, click Delete

Hint If you select multiplerow (2), then 2
will be inserted.
37
Column Size Indication

• Pound signs in cell indicate column is too small
• Need to widen column

38
Adjusting Column Width

• Format, Column, enter new width, OK
• Or
• Format, AutoFit Selection

39
Print Options Headers/Footers

• File Page Setup
• Header
• Three sections left, center, or right
• Always appears at top of worksheet
• Footer
• Always appears at bottom of worksheet

40
Creating Worksheet Header and Footer

• Useful in identifying printed output

41
Setting Headers and Footers
42
Adjusting Page Settings

• File, Page Setup
• Page tab allows setting of Print Area
• Margins tab allows setting each margin
  individually

43
Print Preview
Print Preview Provides
44
Showing Formulas
CTRLaccent keyboard shorcut