Teaching "Relative and Absolute" in Excel

EducationWorld is pleased to present this article contributed by Suzan Spitzberg, an adjunct faculty member at Rasmussen College in Romeoville, IL. Spitzberg is currently teaching Computer Applications and Business Systems Concepts and has worked in the field of computers for over 20 years as a help desk analyst and Microsoft™ Office instructor.

Most of us learn very quickly how to use the fill handle to copy a formula across or down a range of data in a spreadsheet. When you need to use the same cell or cells in all of the copied formulas, however, you have to tell Microsoft™ Excel that you don’t want that cell (or cells) to adjust during the fill.

I have students who will create the same formula over and over to avoid using an absolute reference. This works fine if you only have to create a few formulas, but if you have 100 rows or columns, it will take a long time. Most Excel instructors would agree that this is one of the most difficult Excel concepts for students to grasp. It is also a difficult concept to teach.

I use the following exercise to help clarify the concept of "relative and absolute" in Excel:

Materials Needed:

• One sheet of 8.5 x 11 spreadsheet for every three students in the class
• One sheet of 8.5 x 11 spreadsheet addressed to the president

Scenario:

I am a postal worker in Washington, D.C. One day my supervisor drives up while I’m on my route and hands me a pile of flyers. He tells me that there aren’t enough for each house and I should only give them to every third house. I then count off and present every third student with a flyer.

I then ask:  "Does it matter what the addresses are? Why not?" (It’s relative to where I’m standing.) I then collect the flyers and move over one or two desks and go through the whole thing again. I ask the same questions and also ask why most of the houses are not the same as before. (They are relative to where I’m standing, and I’ve moved.)

Next, I show students the flyer addressed to the president and ask, “Does it matter where I’m standing?” (No, it has an address, so it absolutely has to go to 1600 Pennsylvania Avenue.) I ask them to remember the house and president flyers and open an Excel file where we proceed to put in a formula that really needs an absolute value.

When we get the error messages after copying the formula down, I remind them of the president’s flyer, and the fact that one particular cell needs to have an absolute value just like the flyer had an absolute address. We fix and recopy. Now the formula works. Throughout the Excel lesson, I refer to the values as the flyers for the houses or the flyer for the president.

Education World®             
Copyright © 2011 Education World