Excel – Working with Cell References in Formulas
In this lesson
you will practice writing formulas that use cell references as variables.
This is a twopart assignment. The first part, steps 112, is to be done in Word. The second part is done
in Excel.
If you have never used Excel before, please complete this lesson
first:
http://bus91L.altervista.org/Assignments/ExcelBasics.htm
Instructions:
Read
through the set of
learning objectives. This is what you should know when you finish the lesson.
Watch the
YouTube videos before starting the lesson.
Complete every
step in the assignment, in the order given. Do not skip steps.
When you have
done all of the steps, fill out the Check for Understanding (CFU) form.
A completed
CFU for every lesson, inclass and homework, is due to be
turned in during your next class.
The CFU forms
are not to be submitted by email.
Where there
are differences between software versions, the instructions for Excel 2010 are
in Black, for the Mac are in green and for Excel 2013 are in blue.
Summary of Key Points:
Practice with reading and writing Excel formulas.
Understand what happens to cell references when a formula is copied.
Understand when and why to use the dollar sign ($) symbol in cell references.
How to create a range name and use it in a formula.
Videos:
Formulas for the MAC
https://www.youtube.com/watch?v=xOU_hL2_zBo
This is a twopart assignment.
Here is the Word part:
Download
this file. Copy and paste the text and tables into Word. Complete the Word
document. When you are finished, complete the Excel part.
http://bus91l.altervista.org/Assignments/CellReferences.docx
Part Two of the assignment is to be done n
Excel. Here are the instructions.
Project: Create a times
table using dollar signs as part of the cell references.

A 
B 
C 
D 
E 
1 
1 
2 
3 
4 
5 
2 
2 
=A2*B1 



3 
3 




4 
4 




5 
5 




1. Enter the numbers 1, 2, 3, 4, and 5 in the row A1:E1. 
2.
Enter the numbers 2, 3, 4, and 5 in the column
A2:A5. 
3.
Format the cells in the range A1:E5 to horizontally center the text. 
4.
In cell B2 enter the formula = A2 * B1. You
should see a 4 in the cell 
5.
If you copy the formula in B2 across into C2, D2
and E2 you do not get 6, 8, and 10. Try it. Press
Control + Z to remove the copied formulas. 
You did not get the right results because the cell
reference A2 was changing to B2, C2, and D2 as you copied the formula across.
To fix this add a $ in front of the A to freeze it.
6. Change the formula to = $A2 * B1. This formula will work when you copy
across. Try it. 
7.
If you copy the formula in B2 down into B3, B4 and
B5 you do not get 6 8 and 10. Try it. Press Control + Z to remove the copied
formulas. 
You did not get the right results because the cell
reference B1 was changing to B2, B3, and B4 as you copied the formula down. To
fix this add a $ in front of the 1 to freeze it.
8. Change the formula to = $A2 * B$1. This formula will work as you copy
down. Try it. 
9.
Copy the formula into the rest of the times table (You
should have formulas in cells B2:E5 after completing this task.) 
What does the correct formula look like? = $A2 *
B$1. Why does it work? We have the dollar signs in front of the
A and in front of the 1 because we always want to
multiply a value in row one times a value in column A. The dollar sign makes
sure that the A and the 1 will always be part of the
formula as it is copied into other cells.
10. Click and drag over the range A2:E5 to select it. Use the AutoFill
handle to copy the table down into rows 6 through 10. Did the formulas give
the correct results? 
Using Named Ranges
Example of ranges: Range 1 B3:C4 Range
2 C6:E6 Range
3 E2:E4

A 
B 
C 
D 
E 
F 
1 






2 




Range 3 

3 

Range1 
Range 1 

Range 3 

4 

Range1 
Range 1 

Range 3 

5 






6 


Range 2 
Range2 
Range 2 

7 






You don’t have
to do anything with this table. It is just an illustration of how cell
references are used to refer to ranges.
Instead of using
cell references in a formula you can use a descriptive
a name for a cell, or for a range of cells. To assign a name to a range, mouse
over the range and type a description in the name box. The name box is just
above the worksheet. It is the short box on the left. I labeled the Name Box in
the illustration. The one on the right is called the
formula bar.
Watch the video: Formula Bar and Name Box

11. Click and drag over range A1:E5 (the times table) to select it. Click
in the name box, then type the word “times” and press Enter. You
have just created a range name. 
12.
Click in cell G1. Type =SUM(Times)
and press Enter. You should see the total of all of the cells in the A1:E5
table. 
13.
Click in the pulldown arrow in the Name box to
see a list of all the named ranges. 
A Range Name Manager tool is
found in the middle of the Formulas tab on the ribbon. You can use this tool to Add, Delete, or
Edit range names. The keyboard shortcut to launch this dialog box for
PC’s is Control + F3. There is no special
button for the Name Manager in any version of Excel for Mac. However, you can
call it by pressing ⌘ + fn + F3.
14. Press Control + F3 now. Delete the range name. For the MAC you can do the following: Insert > Name > Define In the dialog box window, choose the name you
would like to edit and press "Delete" from the buttons on the
righthand side. For the MacBook,
go to the Formulas Tab. Click on the Name Manager icon/Define Name. In the
dialog box window, you will see a list of the range names on the left. To
remove a range name from the list, click on it and then click on the minus
sign below the list. 

Review – Fine Points
Instructions for the Check for Understanding (CFU) form.
A CFU form is turned in for every assignment.
Starting with the line for your name, copy and paste the CFU form into a Word document.
Fill
in your name, date, section number
Circle
(or indicate by highlighting) a Y or N response for each statement. I do not
subtract points for No answers.
Below the list
of Y/N statements, insert ONE screenshot
of the EXCEL part of the lesson. I just need to see something to prove that you
did the assignment. I don’t need to see all of
the assignment. Resize the screenshot, if necessary, so both the form and
graphic can fit on one page.
Note:
For this assignment, I want to see the screenshot of the Excel work. I do not
need to see a screenshot of the Word document.
A copy of the CFU is due to be turned in
during the next class session. I do
not accept CFU’s through email.
Name _______________________________ Date___________ Section _______
Check for
Understanding – Excel Working with Formulas
Y/N I can read and write Excel formulas.
Y/N I understand what happens to cell references when a formula is copied and pasted.
Y/N I understand when and why to use the dollar sign ($) symbol in cell references.
Y/N I can create a range name and use it in a formula.