Transcript exp2003_exl_ppt_02.

Exploring Microsoft
Excel 2003
Chapter 2 – Gaining
Proficiency: The Web and
Business Applications
Robert Grauer and Maryann Barber
Committed to Exploring
Shaping
the Next Generation of IT Experts.
Office 2003 - Grauer and Barber
1
Objectives




Gain proficiency in using relative and
absolute references
Explain the importance of isolating the
assumptions in a worksheet
Use the fill handle to copy a range of cells
Use pointing to enter a formula
Exploring Office 2003 - Grauer and Barber
2
Objectives (continued)




Insert a hyperlink into an Excel worksheet
Save a worksheet as a Web page and then
view the page in a Web browser
Import data from a web query into a
workbook; refresh the query to obtain current
information
Describe the Today() function and its use in
date arithmetic
Exploring Office 2003 - Grauer and Barber
3
Case Study: The Proper Tip
The opening case study focuses on how to use
a spreadsheet to calculate the proper tip.
Students will create a table, with bill amounts
ranging from $10 to $200 along the side and
percentages representing service levels
ranging from 10% to 20% along the side
Exploring Office 2003 - Grauer and Barber
4
Cell Referencing

Absolute reference: remains constant
throughout a copy operation


Specified with a dollar sign before the column
and row, i.e. $B$4
Relative reference: adjusts during a copy
operation

Specified without dollar signs, i.e. B4
Exploring Office 2003 - Grauer and Barber
5
Absolute and Relative Cell References
Use absolute cell references
for withholding rate and FICA
rate
Use relative cell references for
each employees gross pay
Exploring Office 2003 - Grauer and Barber
6
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
Exploring Office 2003 - Grauer and Barber
7
Example of Isolated Assumptions
Assumptions are isolated and
clearly labeled
Exploring Office 2003 - Grauer and Barber
8
Using Excel Effectively

Enter cell addresses in formulas and
functions by pointing



Use the fill handle to copy


Use the mouse to select the cell(s)
More accurate than typing cell references
Select the cell(s) and drag to copy to a destination
range
Insert comments
Exploring Office 2003 - Grauer and Barber
9
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
Exploring Office 2003 - Grauer and Barber
10
Using the Fill Handle
Select cells E2:H2. Dragging
the fill handle will copy all four
cells to lower rows.
Border around selected
area. Release the mouse
and formulas are copied
Exploring Office 2003 - Grauer and Barber
11
Inserting Comments
Comments provide
explanation for values
and/or descriptions of
formulas
Exploring Office 2003 - Grauer and Barber
12
Hands-on Exercise 1


Title of Exercise: Payroll
Objective: Develop a spreadsheet for a
simplified payroll to illustrate relative and
absolute references. Use pointing to enter
formulas and the fill handle to copy formulas.


Input file: Payroll
Output file: Payroll Solution
Exploring Office 2003 - Grauer and Barber
13
Excel and the Internet

Insert a hyperlink into a worksheet


Save a workbook as a Web page


Hyperlink: a reference to another document
A “web page” is another name for an HTML
document
Download information from the Web through
a Web query
Exploring Office 2003 - Grauer and Barber
14
A Web Page
Page is viewed
through a Web
browser
Clicking the hyperlink
will take you to the
designated Web site
Exploring Office 2003 - Grauer and Barber
15
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
Exploring Office 2003 - Grauer and Barber
16
The Save As Web Page Command
Enter the name for
your Web page
By default, the Single
File Web Page format
is selected.
Exploring Office 2003 - Grauer and Barber
17
Some Internet Terms


HTML (HyperText Markup Language): a
standard language for creating Web pages
Round trip HTML: allows you to edit a web
page in the application that created it


An Excel document can be saved as a Web
page, then edited in Excel.
Single File Web Page: all elements of a
Web page are saved as a single file.
Exploring Office 2003 - Grauer and Barber
18
Round-Trip HTML
Edit with Microsoft Excel
button lets you start
Excel and edit the
worksheet
Exploring Office 2003 - Grauer and Barber
19
Selecting a Non-Contiguous Range
Drag through cells to
select destination
range
Hold the Ctrl key, then
select additional cells
Exploring Office 2003 - Grauer and Barber
20
Conditional Formatting
Use this dialog to set
criteria, in this case <0
Click Format
button to open
Format Cells
dialog
Select cells to apply
conditional formatting
Exploring Office 2003 - Grauer and Barber
21
Hands-on Exercise 2


Title of Exercise: Creating a Web Page
Objective: To insert a hyperlink into an Excel
workbook; to save a workbook as an HTML
document, then subsequently edit the Web
page


Input File: Statement of Earnings
Output File: Statement of Earnings Solution
Exploring Office 2003 - Grauer and Barber
22
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
Exploring Office 2003 - Grauer and Barber
23
Web Queries
Formulas in cells refer
to the values in the
Web query
Stock ticker
symbols of the
companies in
the portfolio
Results of the query are
displayed here. Clicking a
hyperlink takes you to that
company’s Web site
Exploring Office 2003 - Grauer and Barber
24
Creating a Web Query
Enter cells containing the
criteria for your web query,
in this case, A5:A10
Exploring Office 2003 - Grauer and Barber
25
Refreshing the Query
Context-sensitive menu
appears when you rightclick in the query area
External Data
toolbar
Refresh External Data
command retrieves the
latest data from the Web
Exploring Office 2003 - Grauer and Barber
26
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
Exploring Office 2003 - Grauer and Barber
27
Hands-on Exercise 3


Title of Exercise: Web Queries
Objective: Include a Web query into a
worksheet to retrieve current stock prices
from the Internet. Use the Today() function to
illustrate the use of data arithmetic


Input file: Stock Portfolio
Output file: Stock Portfolio Solution
Exploring Office 2003 - Grauer and Barber
28
Summary





Absolute, mixed, and relative references
Isolate your 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
Exploring Office 2003 - Grauer and Barber
29
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
Exploring Office 2003 - Grauer and Barber
30
End-of-chapter Exercises


Multiple Choice
Practice Exercises








Exercise 1 – Alternate Payroll
Exercise 2 – The Sports Statistician
Exercise 3 – Web Pages and Hyperlinks
Exercise 4 - The Workout Schedule
Exercise 5 – An Exercise in Conversion
Exercise 6 – Web Queries
Exercise 7 – Buying a PC
Exercise 8 – Mixed References
Exploring Office 2003 - Grauer and Barber
31
End-of-Chapter Exercises
(continued)

Practice Exercises (continued)



Exercise 9 – NBA Statistics
Exercise 10 – Financial Forecast
Mini Cases




Accounting 101 – Straight Line Depreciation
Wishful Thinking CD Portfolio
Your Net Worth
The Birthday Problem
Exploring Office 2003 - Grauer and Barber
32
Questions?
Exploring Office 2003 - Grauer and Barber
33