Exploring Microsoft Excel 2003

1
Exploring Microsoft Excel 2003
Chapter 2 Gaining Proficiency The Web and
Business Applications Robert Grauer and Maryann
Barber
Committed to Shaping the Next Generation of
IT Experts.
2
Objectives

• Gain proficiency in using relative and absolute
  references
• Isolating the assumptions in a worksheet
• Use the fill handle
• Use pointing to enter a formula
• Insert a hyperlink into an Excel worksheet
• Save a worksheet as a Web page and then view the
  page in a Web browser
• Describe the Today() function and its use in date
  arithmetic

3
Cell Referencing

• Absolute reference remains constant throughout
  a copy operation
• Specified with a dollar sign before the column
  and row, i.e. B4
• Relative reference adjusts during a copy
  operation
• Specified without dollar signs, i.e. B4

4
Absolute and Relative Cell References
Use absolute cell references for withholding rate
and FICA rate
Use relative cell references for each employees
gross pay
5
Isolate Assumptions

• Base your formulas on cell references, not values
• The cells containing the values (assumptions)
  should be clearly labeled and set apart
• Change the assumptions in the worksheet and see
  the effects instantly
• Also minimizes the chance for error you change
  the assumptions in one place

6
Example of Isolated Assumptions
Assumptions are isolated and clearly labeled
7
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
• Insert comments

8
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
9
Using the Fill Handle
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
10
Inserting Comments
Comments provide explanation for values and/or
descriptions of formulas
11
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
• Download information from the Web through a Web
  query

12
A Web Page
Page is viewed through a Web browser
Clicking the hyperlink will take you to the
designated Web site
13
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
Enter the Web address (URL) of the site you wish
to open
14
The Save As Web Page Command
Enter the name for your Web page
By default, the Single File Web Page format is
selected.
15
Web Queries

• Allows Excel to retrieve information from the Web
• Requires an active Internet connection
• Created with the Import External Data command
• Can be updated anytime with the Refresh command

16
Web Queries
Formulas in cells refer to the values in the Web
query
Results of the query are displayed here.
Clicking a hyperlink takes you to that companys
Web site
Stock ticker symbols of the companies in the
portfolio
17
Refreshing the Query
Context-sensitive menu appears when you
right-click in the query area
External Data toolbar
Refresh External Data command retrieves the
latest data from the Web
18
Selecting a Non-Contiguous Range
Drag through cells to select destination range
Hold the Ctrl key, then select additional cells
19
Conditional Formatting
Use this dialog to set criteria, in this case lt0
Select cells to apply conditional formatting
20
Date Arithmetic

• Excel stores all dates as integers
• Serial numbers, beginning with January 1, 1900
• The difference between dates is determined by
  subtracting one number from another
• Today() function always returns the current date

21
Summary

• Absolute, mixed, and relative references
• Isolate assumptions from the rest of the
  worksheet
• Enter cell references into formulas by pointing
  to them with the mouse
• Use the fill handle to copy a formula to adjacent
  cells
• The Insert Comment command creates the equivalent
  of a screen tip

22
Summary (continued)

• Insert hyperlinks into Excel worksheets
• Save workbooks or worksheets as Web pages
• The Single File Web Page format stores all of the
  elements of a web page as a single file.
• Use Web queries to retrieve information from the
  Web
• Dates stored as serial integers
• Today() function always returns the current date