Excel 2016 Relative, Absolute, and Mixed Cell References

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.