Transcript Reference and Name in Excel 2003

Working with Formulas
in Excel 2003
Part 2
Reference and Name
1.
2.
3.
4.
5.
Identifying reference types
Applying reference type
Using names in formulas
Using 3-D reference
Summary
Identifying reference types
• Cell reference identify a cell or range of cells.
You can think of cell references as directions
that tell Excel where to look for the data that
you want to use in a formula.
• They allow you to write formulas that refer to
other cells in the same worksheet, to cells in
other worksheets, or to cells in other
workbooks.
• Reference to cells in other workbooks are
called LINKS.
The four types of cell references
1. Relative: references in a formula are used by
default - Cell A1 and cell range A1:C4. This means
when you move/copy a formula the cell references
adjust accordingly.
2. Absolute: references are denoted by a dollar sign $A$1. An absolute cell reference refers to a
specific location, which doesn’t change when you
move or copy the formula.
3. Mixed: contains both absolute and relative
reference elements. A$1 uses a relative column
and an absolute row. If you want to move or copy,
the formula will always refer to row 1, but the
column reference will adjust to the location of the
formula.
4. 3-D: is used to consolidate data from different
worksheets in the same workbook.
Applying reference types
Relative and Absolute reference
Question: Consider the formula=A1+$B$1+C1. What will the
formula look like if you copy it from cell D1 to D2?
1. =A1+$B$1+C1
2. =A2+$B$2+C2
3. =A2+$B$1+C2
Applying reference types
Copy the formula
Using Names in Formula
• Excel allows you to name a cell, a range of
cells. Then you can use this name in
formulas.
• The name it becomes available to all
worksheets in the workbook.
• It’s a good idea to name all key cells or
ranges that you think will be used frequently
and will improve the readability of formulas.
• You can do this using the Name box, or you
can use menu options – Insert – NameDefine.
Assign a name for a range of cells using Name boxExercise
1. Select the cell range that you want to assign
a name - B5 to B8
2. Click the Name box to the left of the
Formula bar and type a name, “January”
3. Once you press Enter the range is named.
4. Go to Insert menu – Name – Apply
• Now the formula =SUM(B5:B8) is replace
with formula=SUM(January)
• Now you can access the name from the
Name drop-down list on the Formula bar.
Assign a name to an individual cell using Insert –
Name - Define
Exercise
•Suppose you want to assign the
name “Total_SalesQ1” to cell
E10 and then replace any
occurrences of the original
reference with the new name.
Using 3-D references
•When you want to calculate
values from the same cell or
range on multiple worksheets
within a workbook, you can use
a 3-D reference in your formula.
•A 3-D reference spans two or
more worksheets in a workbook.
Using 3-D reference
• An exclamation point ! Always follows a
reference to a worksheet.
– Example: to refer to cell A1 in a worksheet
called Sheet1, you use the reference Sheet1!A1
• You can refer to a range of worksheets in
the same way as you refer to a range of
cells, by separating the first and last
worksheets in the range with a colon.
– Ex.: to reference cell A1in all three worksheets
in a workbook you use the reference
Sheet1:Sheet3!A1
Question: Which of the following formulas
use 3-D references?
1. =A2*$B$12
2. =AVERAGE(Sheet1:Sheet5!C213)
3. =JanuaryTotal*Commission_Rate
4. =SUM(Sheet1:Sheet4!A3)
Test
Q1:
Say you want to analyze data in
the same cell or range of cells on
different worksheet within a
workbook. What type of reference
enables you to do this?
1. Relative
2. 3-D
3. Absolute
Test – Q2
Which type of reference
represents the same cell when
copied across multiple locations?
1. 3-D
2. Absolute
3. Relative
Test – Q3
In which of the following is a
newly created range name
available?
1. All Excel workbooks
2. All worksheets in the workbook
3. Only the current worksheet
Test – Q4
Which of the following statements
about cell references are true?
1. Absolute ref. and relative ref.
behave the same way
2. Absolute ref. remain fixed even
when copied
3. Relative ref. adjust to different
cells when copied
4. Relative ref. are denoted by a
dollar sign
Summary
• Excel uses different types of references –
relative, absolute, mixed, and 3-D. You
choose the reference type you wish to apply
according to how it is to be used in a formula.
• When you move or copy a relative reference,
the cell references adjust accordingly. An
absolute cell reference, which is denoted by a
dollar sign ($), refers to a specific location.
• Absolute cell references do not change when
you move or copy the formula containing
them. You can also create a formula that
contains both relative and absolute
references.
Summary (continued)
• You can use a descriptive name to refer to a
cell, a range of cells, or a group of nonadjacent
selected cells. You can then use the name in
formulas and cell references.
• When you want to calculate values from the
same cell or range on multiple worksheets
within a workbook, you can use a 3-D
reference in your formula. A 3-D reference
spans two or more worksheets in a workbook.
Hands-On Exercise:
Entering a formula with references
(file:EnteringAFormulaWithReference.xls)
• You're required to calculate first-quarterly
sales totals and commission for Travel
offices, based on figures that have been
entered into multiple worksheets in a
workbook.
This involves the following tasks:
1. entering a formula with a relative reference
2. entering a formula with an absolute reference
3. entering a formula with a 3-D reference
Task 1
• You want to calculate the quarterly
sales for each city in North worksheet.
• Type a SUM function in cell C5 that
adds together Boston’s totals for
January, February, and March. Do the
same for the rest of the cities.
• Type a SUM function in cell B10 that
adds January’s totals for all the cities in
the North worksheet.
Task 2
• Type a formula in cell B11 that
calculates the amount of commission due
to the each city’s office for January. Use
an absolute reference for the commission
rate, which is in cell B13, as you want to
copy the formula to other cells later.
Task 3
• You have now entered quarterly sales
figures for all cities on separate
worksheets (labeled North, Central, and
South)
• Type a SUM function in Summary
worksheet in cell B5 that adds the totals
on each of these worksheets. The total
figure is in cell E10 of each worksheet.