Have you ever created a formula and then have the cell references change unexpectedly when you copied the formula to another cell? It may be a problem with the type of cell references you used in your formula. To help you nail cell references, Belleyre Books has published a new video showing you just how to create different reference types and what changes to expect when your formula gets copied.

0:00 Excel 2016 Relative, Absolute, and Mixed Cell References

0:04 From the book Microsoft Excel 2016 In 90 Pages by Beth Brown

0:14 In this video you will learn

0:16 How relative, absolute, and mixed cell reference behave when copied to another cell.

0:18 How to use the F4 key to easily convert between reference types when creating a formula.

0:31 Cell References

0:32 Are used in formulas to refer to data stored in a spreadsheet cell.

0:37 The type of cell reference you use depends on

0:42 where you want to copy the formula.

0:45 Relative cell references, such as B12, are the default. They change relative to the new cell location they are copied to.

0:46 Absolute cell references, such as $B$12, use dollar signs to indicate a cell reference should not change when copied to another location.

0:59 Mixed cell references, such as $B12 and B$12, use dollar signs in front of either a column letter or row number to indicate which part of the reference is to remain unchanged when copied to a new location.

1:22 In this spreadsheet, I will demonstrate the various cell reference types.

1:31 I want to create a formula to calculate the taxes on the menu items.

1:37 To do this, I enter a formula in cell C4 to multiply the price times the tax rate.

1:47 To copy this formula to other cells in this column,

1:51 I use the Fill handle.

1:56 But wait, there’s something wrong.

2:00 Because the reference to the tax rate is relative,

2:03 it changed when copied to the other cells.

2:14 Let’s delete the formulas and create a new one that uses an absolute cell reference that won’t change when copied.

2:26 When we get to the reference that is to remain unchanged,

2:30 we can type dollar signs in front of the column letter and row number

2:34 or we can just press the F4 key to cycle through reference types.

2:40 I’m pressing the F4 key now

2:42 to cycle through the reference types.

2:45 I’ll stop at absolute reference with dollar signs in front of both the column letter and row number.

2:52 Now let’s use fill down to copy the formula to the remaining column cells.

3:02 As you can see the absolute reference remains unchanged and the relative reference changed as needed.

3:18 Next, let’s total the price and tax amounts.

3:28 The formula for the first item is =B5+C5. Now let’s fill down.

3:38 As you can see the relative referencing changed to refer to the appropriate item

3:46 calculating individual totals.

3:49 To demonstrate mixed cell references, I’m going to complete a table that calculates prices for up to 5 orders of an item.

3:59 The formula needs to multiply the total by the number of orders.

4:06 The first reference should always refer to column D. Let’s use the F4 key to get to the correct reference.

4:25 The second reference should always refer to row 3.

4:38 Now let’s fill across. As you can see, the mixed cell references allow the formula to correctly calculate.

4:52 Next, let’s fill down to calculate the other items.

4:57 Mixed references allow the same formula to be copied across columns and down rows without error.

5:19 Tips

5:20 Using cell references where possible in formulas is a good practice.

5:26 Use the appropriate type of cell reference (relative, absolute, or mixed) in formulas to avoid errors when copying.

5:35 Press the F4 key when entering a cell reference to change the reference type quickly and easily.

5:46 Although not demonstrated, to create a reference to a cell on another sheet, include the sheet name, as in ‘Sheet 2’!B4.

5:54 Notice the single quotation marks around the sheet name.

5:58 You will need to include those when your sheet name contains a space.

6:07 All this information and more can be found in Microsoft Excel 2016 In 90 Pages by Beth Brown

6:14 available at Amazon.com

Cell references and much more are covered in Microsoft Excel 2016 In 90 Pages.

### Like this:

Like Loading...

*Related*

## Published by Belleyre Books

Publisher and Bookseller.
View all posts by Belleyre Books