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.
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.
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.