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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.