Excel – Working with Cell References in Formulas

In this lesson you will practice writing formulas that use cell references as variables.

This is a two-part assignment. The first part, steps 1-12, 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, in-class 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:

Cell References

Formula Bar and Name Box

Formulas for the MAC

https://www.youtube.com/watch?v=xOU_hL2_zBo

 

This is a two-part 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 pull-down 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 right-hand­ 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

Absolute Cell Reference   1:26

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.