Transcript Exploring Microsoft Excel 2003

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
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
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
Exploring Office 2003 - Grauer and Barber
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
Exploring Office 2003 - Grauer and Barber
6
Example of Isolated Assumptions
Assumptions are isolated and
clearly labeled
Exploring Office 2003 - Grauer and Barber
7
Alternate Payroll Example

Demo -- use of Assumptions
Exploring Office 2003 - Grauer and Barber
8
Assumptions:





Gross Pay = Regular Pay + Overtime Pay
Regular Pay = Regular Hours * Rate
Overtime Pay = Overtime Hrs. * Rate * Overtime Rate
Overtime Rate is an Assumption
Other Assumptions:



Deduction per Dependent
Social Security Tax (FICA)
Income Tax rate
Exploring Office 2003 - Grauer and Barber
9
DEMO
OF PAYROLL
Using Chapter 2,
Practice 1
Alternate Payroll
Exploring Office 2003 - Grauer and Barber
10
Using Excel Pointing Method

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
11
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
12
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
13
Inserting Comments
Comments provide
explanation for values
and/or descriptions of
formulas
Exploring Office 2003 - Grauer and Barber
14
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
15
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
16
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
17
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
18
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
19
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
20
Round-Trip HTML
Edit with Microsoft Excel
button lets you start
Excel and edit the
worksheet
Exploring Office 2003 - Grauer and Barber
21
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
22
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
23
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
24
End of Session 1
Chapter 2
Exploring Office 2003 - Grauer and Barber
25
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
26
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
27
Creating a Web Query
Enter cells containing the
criteria for your web query,
in this case, A5:A10
Exploring Office 2003 - Grauer and Barber
28
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
29
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
computer date
Exploring Office 2003 - Grauer and Barber
30
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
31
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
32
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
33
End-of-chapter Exercises




Lab Assignments
Page 378 #1. (Use Figure 2.7 on page 378 as a
guide) Use the Chapter 2 Practice 1 workbook from
your data disk. Print both the computed values and
the cell formulas.
Page 382 #5. (Use Figure 2.11 on page 382 s a
guide) Use the Chapter 2 Practice 5 workbook from
your data disk. Print both the computed values and
the cell formulas.
Page 384 #7. (Use Figure 2.13 on page 384 as a
guide) Use the Chapter 2 Practice 7 workbook from
your data disk. Print both the computed values and
the cell formulas.
Exploring Office 2003 - Grauer and Barber
34
Questions?
Exploring Office 2003 - Grauer and Barber
35