How do you copy and paste a formula in Excel while changing one cell reference?
This is a little lesson that teaches the difference in Relative and Absolute Cell References: Legs, Eggs and Pigs. It is a spreadsheet that will calculate the daily sales for three products. There is one sheet for each product: Legs, Eggs and Pigs. There is a summary sheet that adds up the sales for all products on each day. Each sheet is set up the same. There are only three parts to a spreadsheet: labels, data, and formulas. Start with the labels.
Try This: Enter the Labels
Click on Cell A1 and type: Date
In Cell B1 type: Product
In Cell C1 type: Net
In Cell D1 type: Quantity
In Cell E1 type: Revenue
If these are labels–and they are–select Row 1 and make them Bold.
Fill the Dates with AutoFill
AutoFill is a quick method for filling in data. It is not quite the same as copy and paste.
Try This: Use the AutoFill Handle
Please select Cell A2, first. Column A is formatted for Short Date. The sample date is July 1, 2013.
Look at the bottom right corner of Cell A2.
The AutoFill Handle is a small square. When you run your mouse over the Handle, your mouse will become a thin black cross.
Hold the AutoFill handle and drag down to Row 24.
What Do You See? The dates will increment (add another day to each Row) as you Fill Down Cell A2.
AutoFill a Series
This little web site sells about 100 egg baskets a day. Suppose we sold another 5 baskets every day. What would the sales revenue be?
Try This: Enter the Data
In Cell D2 type 100.
In Cell D3 type 105.
And Do This: AutoFill a Series
Select Cell D2 AND D3: both the 100 and the 105. Now you have selected enough of a Range for Excel to recognize that this is a Series, incrementing by 5s.
Double click the AutoFill handle and the Series will fill down to match Column C on the left.
What Do You See? When you AutoFill this Series, Microsoft Excel adds 5 more to the quantity in each Row.
When you created the sales spreadsheet, you selected a range of cells to AutoFill. If we put 100 for the quantity in the first cell and 105 in the next, Excel fills down the series and adds 5 more to each cell. If you wanted a different forecast, you could enter new quantities in the first and second cells and use the AutoFill again. This could get old very fast. It is also a rather inflexible method of changing the data.
There is a better method: reference cells. Reference cells set up one place to enter the data. All of the other equations and spreadsheets that depend on that data look it up in the reference cells.
Create a Relative Reference
Try This: Create a Relative Reference
All good equations begin with “equals.”
Select Cell C6 and type: =
Click on Cell B1.
Type: ENTER on the keyboard.
What Do You See? The formula in Cell C6 says that C6 equals B1. If you change the number in Cell B1, it should automatically update in Cell C6.
Autofill a Relative Reference
In the previous step, we created an equation in Cell C6 and tested it. Please Autofill that equation to all of the dates in Column C.
Try This: Autofill a Relative Reference
Select Cell C6. Double Click the AutoFill handle in the lower right corner of the Cell.
What Do You See? The formula in Cell C6 will be copied to all of the dates in Column C. However, instead of copying $3.25, the price in Cell B1, Cell C10 says “Product” and Cell C11 says “Pigs.”
What Else Do You See? The numbers in Column E also indicate a problem. The first equation in Cell E6 calculates correctly.
#VALUE means the data doesn’t make any sense: there may be a mismatch. In this example, Excel can’t multiply Pigs*205 (Text times a Number).
Review the Relative References
You can double click any Cell that has a formula and see an outline of the Cell (s) that are used in that equation. Let’s audit this spreadsheet.
Try This: Audit a Relative Reference
Double Click Cell C6. You should see the formula displayed in the Formula Bar at the top of the spreadsheet. Cell B1 is outlined in Blue.
Double Click Cell C10. Cell B5 is outlined.
What Does That Mean? The formula in Cell C6 was =B1.
Instead of copying $3.25, the formula (=B1) was AutoFilled.
What Else Do You See? When the formula in Cell C6 was AutoFilled, the Cell Reference was updated for each Row.
This is an example of a Relative Cell Reference. It is Relative because it was updated for each Row.
Create an Absolute Reference
There is another way to manage copying formulas so that they point to the right data.
An Absolute Reference is defined as a specific Row or Column. The Absolute Reference will NOT update if it is copied to another Row with Autofill.
Try This: Create an Absolute Reference
Select Cell C6.
Click your cursor in the Formula Bar on B1.
On your keyboard, click on F4.
What Do You See? The formula in Cell C6 is now: =$B$1, where the dollar $ sign means that it is Absolute.
Try This Too: AutoFill the Absolute Reference
Select Cell C6.
Double click the Autofill handle.
It looks like it worked this time.
Using Reference Cells
The next task is to calculate the daily sales. In this example, we will make one Cell Reference in the formula Relative and the other Absolute.
Before You Begin: Edit the Data
In Cell B2 type: 100
In Cell B3 type: 5
Thoughts to Consider
Cell D6 is the initial quantity sold.
Cell D7 should be whatever we sold on the first day, plus the quantity we are forecasting for each day’s sales increment.
Try it: Create the Formulas
Select Cell D6 and enter this equation:
Select Cell D7, and enter this equation:
When you need to work with one particular cell you need an Absolute Reference.
Try it: Create an Absolute Reference
Select Cell D7.
The Formula bar shows =D6+B3.
Go to the Formula Bar and click on B3.
Click the F4 function key on the top row of the keyboard.
What Do You See? The cell reference becomes $B$3. This means “go to B3 only, and no place else,” to get the data.
Try This, Too: AutoFill the Formula
Select Cell D7 and AutoFill the revised equation to the rest of the rows.
A Mixed Reference uses Absolute and Relative cell references.
Good question. .
eBeth, Elizabeth Nofs