Exploring Microsoft Excel 2000 2003

1
Exploring Microsoft Excel 2000 2003

• Part II
• 
  Natalia Mosina-2005

2
BOOKS
Robert Grauer and Maryann Barber
3
Using Excel Effectively

• Enter cell addresses in formulas and functions by
  pointing
• Use the mouse to select the cell(s)
• More accurate than typing cell references
• Use the fill handle to copy
• Select the cell(s) and drag to copy to a
  destination range
• Shape of the cursor must be a crosshair to use
  the fill handle to copy cells.
• Insert comments

4
Pointing
Use the mouse to select the cells to be included
in the formula
Notice the color coding between the borders
around the selected cells and the formula in the
formula bar
5
Using the Fill Handle (instead of copy/paste) to
copy formulas
Select cells E2H2. Dragging the fill handle
will copy all four cells to lower rows.
Border around selected area. Release the mouse
and formulas are copied
6
Practice with fill handle to copy

• To master any technique it is important to
  practice.
• Lets see how this fill handle works using simple
  sample data.

7
Use pointing instead of typing to enter the
formula
8
Select C1. Point to the low right corner and
notice how the pointer Changed to the crosshair.
Click and drag down till C5. Release mouse.
9
Copy by dragging. Watch out for
crosshair. Release mouse to finish copying.
10
Inserting Comments
Comments provide explanation for values and/or
descriptions of formulas
11
Office and Internet

• The Internet and World Wide Web are thoroughly
  integrated into Office through two basic
  capabilities.
• You can insert a hyperlink into any Office
  document, then view the associated Web page from
  within the document.
• You can also save any Office document as a Web
  page, which in turn can be displayed through a
  Web browser.

12
Excel and the Internet

• Insert a hyperlink into a worksheet
• Hyperlink a reference to another document
• Save a workbook as a Web page
• A web page is another name for an HTML document

13
A Web Page
Page is viewed through a Web browser
Clicking the hyperlink will take you to the
designated Web site
14
Inserting a Hyperlink
If the cell is blank, type the hyperlink text in
the Text to Display box
Select the cell that will contain the hyperlink
1
Enter the Web address (URL) of the site you wish
to open
15
The Save As Web Page Command
Enter the name for your Web page
By default, the Single File Web Page format is
selected.
16
Exercise Internet and Excel.

• This exercise requires that you have an Internet
  connection to test the hyperlink.
• You will hide first column in your Better Grade
  Book file and will insert Student ID column.
• You will insert a hyperlink into an Excel
  workbook, then follow the link.
• Next, you will save a workbook as an HTML
  document (under the new name Better Grade Book as
  Web Page) and view it using your browser.
• Use a Handout as a guide in this exercise.

17
http//acc6.its.brooklyn.cuny.edu/nmosina Enter
the above URL
18
(No Transcript)
19
Round-Trip HTML
Edit with Microsoft Excel button lets you start
Excel and edit the worksheet
20
Selecting a Non-Contiguous Range
Drag through cells to select destination range
Hold the Ctrl key, then select additional cells
21
Conditional Formatting
Use this dialog to set criteria, in this case lt0
Select cells to apply conditional formatting
22
Spreadsheets in decision making.

• Excel can help you in decision making.
• We will continue working with predefined
  functions.
• We will consider financial functions such as PMT
  function that determines monthly payments on a
  loan, and FV future value.
• We will introduce statistical functions and
  conditional functions.
• We will introduce the Goal Seek command.

23
Using Functions

• Function a predefined computational task
• a predefined formula.
• Requires (0 or more) arguments(separated by
  commas) as input.
• Arguments - values the function uses to calculate
  answers
• Returns a value as output.
• Excel has more that 100 different functions in
  various categories. Financial functions are very
  important in business.

24
The PMT Function

• Calculates a periodic payment, such as a car or
  mortgage payment
• Based on
• Amount financed
• (amount of loan)
• Interest rateper period
• (annual rate divided by 12)
• Number of periods
• (n years12 months/year)

25
Using the PMT function
Amount financed expressed as a negative
number (Banks view) The money is lent to you
and represent an outflow of cash from the bank.
Number of (monthly) payments
(monthly) Interest rate
Amount financed, (yearly) interest rate, and the
term (in years), are all isolated as assumptions.
One or more assumptions can be changed
26
The FV function

• Returns the future value of a series of payments
• For example, contributions to your 401K or IRA
  (under either plan, an individual saves for his
  or her retirement by making a fixed contribution
  each year. The money is allowed to accumulate
  tax-free until retirement).
• FV based on
• Number of periods
• Expected rate of return per period
• Amount invested each period
• FV deals with constant periodic payments and a
  constant interest rate.

27
Using the FV Function
Amount of contribution, rate of return, and years
contributing are all expressed as assumptions
28
Inserting a Function

• Use the Insert Function command from the Insert
  menu
• Use the list box to select the name of the
  function
• Functions are categorized
• Let the Wizard help you enter the arguments
• Point to enter cell references
• Use the Collapse button to collapse the dialog box

29
The Function Wizard
Enter arguments into text boxes
Collapse button shrinks dialog box if necessary
Value returned by the function (answer) is
displayed
30
The Goal Seek Command

• Allows you to set an end result and vary an input
  (assumption) to produce that result
• Only one input can be varied at a time
• All other assumptions remain constant
• For example, set a desired monthly car payment
• Vary the amount financed
• Interest rate and number of months remain the same

31
Using the Goal Seek Command
Enter the cell containing the desired result
Enter the desired value
Enter the cell containing an input to change
32
Hands-on Exercise 1

• Title of Exercise Basic Financial Functions
• Objective To illustrate the PMT and FV
  functions to illustrate the Goal Seek command.
• Input file None
• Output file Basic Financial Functions
• Use Handout for step by step instructions

33
Statistical Functions

• MAX, MIN, and AVERAGE functions
• Return highest, lowest, and average values from
  an argument list
• Argument list may include cell references, cell
  ranges, values, functions, or formulas
• Cells that are empty or contain text are not
  included
• COUNT and COUNTA functions
• COUNT returns number of cells containing numeric
  entries or formulas that return a number
• COUNTA also includes cells with text

34
(No Transcript)
35
(No Transcript)
36
Using Functions versus Formulas

• In general, use functions instead of formulas
• Functions can use ranges that are adjusted as
  rows or columns are deleted or added within the
  range referenced by the function
• With formulas
• Adding a row adjusts the cell references in the
  formula, but does not include the new row in the
  formula
• Deleting a row may cause a REF error message (it
  means that a referenced cell has been deleted)

37
The IF Function

• Enables decision making in a worksheet
• Requires three arguments
• A condition
• A value if the condition is true
• A value if the condition is false
• Condition must be able to be evaluated as true or
  false
• Uses relational operators (, lt, etc.)

38
Using the IF Function
Value_if_true entered as a value. Value_if_false
entered as a cell reference
39
The VLOOKUP function

• Allows Excel to look up a value in a table and
  return a related value
• 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

40
Using the VLOOKUP Function
Look up the value found in cell I4, in this case,
the semester average
This argument tells the function where to look.
Absolute references used for the table
Look in the second column of the table, NOT in
column J
41
Working With Large Worksheets

• Scrolling causes the screen to move horizontally
  or vertically as you change the active cell
• Drag the horizontal or vertical scroll bars
• Click above or below vertical scroll bars
• Click to the left or right of horizontal scroll
  bars
• Freezing Panes allows row and column headings to
  remain visible while scrolling
• Hiding rows and columns makes rows and columns
  invisible on the monitor or when printed

42
Freezing Panes
As you scroll back up, rows 4-8 will become
visible again
43
Printing Large Worksheets

• Page Preview command (View menu) lets you see
  where the page breaks are
• Page Setup command (File menu) lets you change
  how the sheet prints
• Change from portrait (8 ½ x 11) to landscape (11
  x 8 ½)
• Change margins
• Scale the worksheet to print on one sheet

44
The AutoFilter Command

• Allows you to display a selected set of rows
  within a worksheet
• Displays rows that meet selected criteria
• Other rows are hidden, not deleted
• Select Filter then AutoFilter from the Data menu
• Select criteria from the dropdown

45
Using the AutoFilter Command
Click the dropdown on the Homework column, then
select Poor as the criteria