Lecture Demonstration

1
Lecture / Demonstration

• Microsoft Excel Basics

2
Anatomy of the Excel Window
3
Basic Vocabulary
Cell Address
Worksheet
4
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

5
Typical Spreadsheet
6
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
7
Using the Mouse

• Clicking on Cells
• Right-Clicking on Cells
• Check Out the 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

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

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

11
Labels

• Set of characters used to identify data
• Left justified in cell (default setting)
• Examples
• Column headings
• Row headings
• Notes

12
Text Within a Cell
Cell not large enough, but adjacent cell is filled
13
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
14
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!)
15
Functions

• Built-in formula that provides shortcut for
  complex calculations
• Function Name
• Parentheses enclose the argument list
• Cells or other expressions needed for the
  calculations

16
Excel Functions

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

17
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

18
Column Size Indication

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

19
Adjusting Column Width

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

20
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

21
Creating Formulas

times
times
450
22
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)

23
Arithmetic Operators

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

24
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
Division
Subtraction
Addition
25
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

26
Absolute References
B9B8
27
Absolute References
B2 B8 B3 B9 B4 B10
B2 B8 B3 B8 B4 B8
28
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