Microsoft Excel

1
Microsoft Excel

• Joe Komar

2
Microsoft Excel

• Spreadsheet Program with graphics and data base
• View as collection of rows and columns -- table,
  matrix, spreadsheet
• Rows are numbered 1-65,635
• Columns are lettered A-IV
• Intersection is a cell (A23, HI435, etc)

3
Microsoft Excel

• Provides 3-D spreadsheets
• Can link to other applications
• Macro capability
• Programming capability

4
Building Spreadsheets

• Determine and specify purpose
• Design and Build
• Test
• Document

5
Moving Around a Spreadsheet

• Point and click
• Page Up, Page Down, AltPage Up, AltPage Down
• CtrlArrows -- Move to limits
• CtrlHome, CtrlEnd

6
Entering Data

• Data is either Labels or Values
• Values -- start with ,_at_,,-,., or number
• Labels -- start with any other character
• Values you do arithmetic with
• Edit contents by pressing F2

7
Building a Personal Budget

• Just a simple one with a few categories and a few
  months

8
Hands On Exercise

• Create the following spreadsheet

Cells in yellow all contain formulas
9
Quick Review of Buttons

• Buttons are shortcuts to menu choices

10
Excel Homework

• Do Chapter 1 in text
• Do Practice Exercise 1, p. 336
• Turn in on due date

11
Cell References

• Normal -- A14, BA51, etc.
• Column and/or row references are changed during
  copy
• Absolute -- A14, BA51, etc.
• Neither are changed during copy
• Mixed -- A14, BA51, etc.
• Whatever doesnt have the dollar sign is changed
  during copy

12
Working with Entire Rows or Columns

• Click on column letter or row number to select it
• Can then perform operation on that entire row or
  column

13
Moving Things

• Highlight the area to move
• Use Copy to make a copy of it
• Use Cut to remove it
• Use Paste to put it where you want
• Use Insert menu to insert blank columns or rows

14
Working with Sheets

• Copy one sheet to another
• Use Ctrl Drag from one sheet to another
• Can name sheets with up to 31 alphanumeric
  characters Right click and choose rename
• Print Preview to see what it would look like when
  printed

15
Excel Homework

• Do Chapter 2
• Do Practice Exercise 2, p. 379
• Turn in on due date

16
Common Excel Functions

• SUM(range)
• AVERAGE(range)
• MAX(range)
• MIN(range)
• COUNT(range)
• IF(relationship,true,false)
• VLOOKUP(value,range,offset)

17
IF Function

• IF(relationship,true,false)
• relationship is a comparison of two things
  using ,lt,lt,gt,gt,ltgt,AND,OR
• relationship is either true or false
• true is what operation to perform if true
• false is what operation to perform if false

18
IF Function (contd)

• Examples
• IF(G184,600Q9,0)
• IF(B1gtC1,D112.5,D134.8)
• IF(A1gt10,IF(A1gt20,.5A1,.3A1),.1A1)

FALSE
TRUE
19
Hands On Exercise
Taxes are 30 of gross if gross is less
than 20,000, otherwise it is 50 of gross.
Tax Table 0-10,000 10 10,001-25,000
20 25,001-50,000 30 Over 50,000 50
20
LOOKUP Functions

• There are two such functions
• HLOOKUP -- for horizontal tables
• VLOOKUP -- for vertical tables
• VLOOKUP(value,table,offset)
• value is value to look up in table
• table is the range of the table
• offset is how many rows to go over

21
VLOOKUP Example

• Tax table example

22
Hands On Exercise
Complete the following spreadsheet using VLOOKUP
to find the Discount Amount from the table at the
right.
23
Large Spreadsheets

• Freezing Headings - Window, Freeze
• Multiple Views - Window Arrange Tiled
• Spell Checking - Tools, Spelling
• Linking Workbooks workbooksheet!cells

24
Large Spreadsheets (contd)

• Goal Seek - change one cell to achieve goal
• Scenarios - Tools, Scenarios
• Borders - Highlight cells, then Format, Cells,
  Borders
• Text Boxes - use button then type text (Drawing
  Toolbar)
• Page formatting - File, Page Setup

25
Homework

• Do Chapter 3
• Do Exercise 1, p. 430
• Turn in on due date

26
Excel Charts

• Many different types
• Terminology
• X Axis -- Horizontal
• Y Axis -- Vertical
• Plot Area -- Where data is charted
• Legend -- Tells what symbols mean
• Titles -- X axis (category), Y axis (value),
  chart, etc.

27
Excel Charts (contd)

• Creating a Chart
• Select Data Series
• Choose Insert, Chart
• Use the Chart Wizard
• Editing a chart -- double click on chart or
  component thereof
• Can create combination Charts

28
Excel Homework

• Do Chapter 4
• Do Exercise 4, p. 489
• Turn in on due date