Transcript PPT

Exploring Microsoft
Excel 2003
Chapter 2 – Gaining
Proficiency: The Web and
Business Applications
Robert Grauer and Maryann Barber
Office
2003
Vol 1 2/eGrauer and of IT Experts.
Committed toExploring
Shaping
the
Next
Generation
Barber
1
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 Vol 1 2/e- Grauer and
Barber
2
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 Vol 1 2/e- Grauer and
Barber
3
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 Vol 1 2/e- Grauer and
Barber
4
Example of Isolated Assumptions
Assumptions are isolated and
clearly labeled
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
5
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 Vol 1 2/e- Grauer and
Barber
6
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 Vol 1 2/e- Grauer and
Barber
7
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 Vol 1 2/e- Grauer and
Barber
8
Inserting Comments
Comments provide
explanation for values
and/or descriptions of
formulas
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
9
Excel and the Internet



Insert a hyperlink into a worksheet
Save a workbook as a Web page
Download information from the Web through
a Web query
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
10
Round-Trip HTML

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.
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
11
Round-Trip HTML
Edit with Microsoft Excel
button lets you start
Excel and edit the
worksheet
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
12
Selecting a Non-Contiguous Range
Drag through cells to
select destination
range
Hold the Ctrl key, then
select additional cells
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
13
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
Format  Conditional Formatting
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
14
Web Queries

Allows Excel to retrieve information from the
Web




Requires an active Internet connection
Data  Import External Data
Created with the Import External Data
command
Can be updated anytime with the Refresh
command
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
15
Creating a Web Query
Enter cells containing the
criteria for your web query,
in this case, A5:A10
Exploring Office 2003 Vol 1 2/e- Grauer and
Barber
16
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 Vol 1 2/e- Grauer and
Barber
17
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 Vol 1 2/e- Grauer and
Barber
18