Introduction to Microsoft Excel What is a Spreadsheet

Document Sample

```					Introduction to Microsoft Excel: What is a Spreadsheet?

•Why do I need to know about it?
•Why would I want to use a spreadsheet?
Terminology
•Worksheet
–Sheet1
–Tabs
•_________________________
–speeds and feeds
•Rows (numerical, example 12)
•Columns (alphabetical, example C)
•Cells (C12)
–Active cell
Entering Data
•Constant
–Does not change
–ex. PI
•Formula
–ex. =C11-C10
•Function
–ex. AVERAGE
•Value
–Computed
____________________: The heart of Excel
•Always begins with an equal sign
•Order of operation:
–Parenthesis, exponentiation, multiplication or division (left to right
order), addition or subtraction (left to right order)
•+, - , * , / , ^
•Definition formula: combination of numeric constants, cell references, arithmetic
operators, and/or functions that produce a new value from existing values.
Toolbars
Modifying the Worksheet
•Insert command
•Delete command
–Removes existing rows or columns
•Edit Clear - does not change cell references
Operations
•Cell range - every command operates on this rectangular group of cells
–To select noncontiguous ranges use Ctrl key
•Copy - formulas adjusted as they are copied
•Move - cut and paste - formulas adjusted as they are moved
•Transpose (paste special)
References
•Absolute
–Remains constant throughout operations
–\$C\$12
•__________________________
–C12
•Mixed
–Make column relative, row absolute
–C\$12
–Multiplication Table
In Class Exercises
Formatting
•Column width
–Click and drag or double click border between columns
–Too small:
•Text - either is cut off or overflows into adjacent cell
•Numbers - error ######
•Row height
–Changes automatically as font size is increased
Formatting cells
•Alignment - Left/right/center justification
•Font, pitches, style, color
•Word wrap
•Merge and Center cells
Use Page Setup to print row and column headings when printing a worksheet
Sorting
•Alphabetically
•Numerically
•A-Z, low to high is ascending
•Z-A, high to low is descending
Numeric Formats
•General - default: integers, decimal fractions
•Number - any number of decimal places
•_____________________ \$
•Accounting - negative values in parenthesis, zero values as hyphens
•Date - September 6,1997, 9/6/97, 6-Sept-97
•Time - 10:50pm, 22:50pm
•Percentage - %
•Fraction - 1/3
•Scientific - 1.234E+04
•Text - left aligns the entry, numbers with leading 0s
•Special - hyphens or special characters, ssn
•Custom - develop own
Isolation
•Assumptions
•Initial conditions
•Easily change any of those values to see their effect on the overall spreadsheet
1. Enter row and column headings and values for initial conditions
2. Develop formulas based on initial conditions

Can change between cell contents and displayed values by pressing Ctrl + `

Function Knowledge
•Arguments
–=SUM(A3:D3,4) Arguments are A3:D3,4
–Cell range needs to be specified with a colon
•Assumptions
Functions and Commands
•AVERAGE
•COUNT
•COUNTA
•_________________________
•MAX
•MIN
•PMT
•SUM
•MOD
•VLOOKUP

•Function Wizard
•Function Palette
•Freeze Panes
•Goal Seek
•Statistical Functions
•Unfreeze
Statistical functions
•Max - highest value
•Min - lowest value
•Average - average value
•Count - number of numeric entries
•Counta - number of both numeric and text entries

•Returns these values from argument list
–individual cell references
–ranges
–numeric values
–functions
–formulas
Mod
•Returns the remainder after a number is divided by a divisor
•=MOD(number,divisor)
•=MOD(12,4) returns 0
•=MOD(12,7) returns 5
IF function
•Three arguments
–Condition that is evaluated as true or false
–The value to be returned if the condition is true
–The value to be returned if the condition is false
•Relational operators
•Logical test
In-Class Exercise
A=2
B=6
C=14
If (A>=B) THEN
G=C+B
ELSE
G=C mod B
ENDIF
What is G?____________
Nested IF’s
•The second argument and the third argument of an IF statement can be IF
statements too.

IF(<cond1>,IF(<cond2>,result if cond2 true,else),IF(<cond3>,result if cond3 true,else))
PMT
•Requires three arguments
–Interest rate per period
–Number of periods
–Amount of the loan/Principal (enter with minus sign)
–Placed in parentheses and separated by commas
=PMT(.09/12,36,-14999)
=PMT(B5/12,B6*12,-B4) *Better to enter as cell references
Goal seek
•Set an end result to determine an input to produce that result
•One parameter can be varied at a time
The Power of Excel
•Fill handle
–Tiny black square in lower right corner of cell
–Fastest way to copy
•Pointing vs. Enter mode
–Enter cell reference into a formula by pointing
•Formula Palette
–Easily specify arguments for a function
•Scrolling
•Freezing panes
•AutoFill
VLOOKUP
•Assigns an entry to a cell based on a numeric value contained in another cell
•Three arguments
–Numeric value to look up
–Range of cells containing the table in which the value is to be looked up
–The column number within the table that contains the result
•Breakpoints - matching values
Good stuff in Excel
•Template
•Spell check
•F4 - cycle through relative and absolute references
•Troubleshooting
–#NAME?
–#REF

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 61 posted: 11/21/2008 language: English pages: 5
How are you planning on using Docstoc?