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

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

**Cell References**

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

Keep going…

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

Keep going…

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

Keep going…

**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:

=B2

Select Cell D7, and enter this equation:

=D6 +B3

**Absolute References**

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.

That is a brief explanation of the difference in Relative and Absolute References. There is a YoutTube video that shows all the steps that you can watch if you wish.

**Microsoft Excel 2013 Intermediate: Legs, Eggs and Pigs in a Basket**

Good question. .

eBeth, Elizabeth Nofs

This lesson is included in our Top Ten Tools in Microsoft Excel.

It is a free course that we are testing the new DRIP programing.

**New Subscribers Welcome: **https://thecomputermama.us

**Buy the books at Amazon: **https://thecomputermama.us/books-page/

**Visit our school at: **https://thecomputermama.teachable.com/