means that if the formula is looking at a cell reference that is three cells to the “Relatively” left, when you copy the formula to any other cell, the cell reference will still be looking three cells to the left. Absolute Cell References Example: $A$1 Dollar signs before both: Column designation = A Row designation = 1 means that if the formula is looking at a particular cell reference, when you copy “Absolute” the formula to any other cell, the cell reference will still be looking at that particular cell reference. If the Absolute Cell reference is $A$1, the formula will always look at cell A1. It is as if the formula is locked on the cell A1 throughout copy action cell reference when copying it horizontally and “Locks vertically” Cell Reference with Column Example: $A1 “Mixed Locked” Dollar sign before column designation Remains absolute or locked when copying across columns Remains relative when copying across rows cell reference when copying it horizontally, but not “Locks vertically” Cell Reference with Row Example: A$1 “Mixed Locked” Dollar sign before row designation Remains absolute or locked when copying across rows Remains relative when copying across columns cell reference when copying it vertically, but not “Locks horizontally” Keyboard shortcut F4 key: Toggles between the four types of cell references When creating formulas with cell references, ask two questions of every cell reference in formula: Q1: What do you want it to do when you copy it horizontally? Is it a relative reference? OR Is it an absolute or locked reference? Q2: What do you want it to do when you copy it vertically? Is it a relative reference? OR Is it an absolute or locked reference? Here are the steps to learn about the four cell references: Go to the sheet tab named and click in cell C3 and create the formula shown in Figure “Cell References” 141. The formula calculates a proportion or percentage of the whole (depending on how it is formatted). In our example we are parts of the 100 whole) and comparing it to the total comparing Tina’s score (82 possible points (100 points the whole). If you look ahead to creating the formulas for Sue and Chin and – Hien, all their will have to be compared to the “parts” “whole”. Figure 141 Hold Ctrl, then tap Enter. You should see that the proportion of points that Tina earned is .82 (Figure 142): Figure 142
“Relatively” means that if the formula is looking at a cell reference that is three cells to the left, when you copy the formula to any other cell, the cell reference will still be looking three cells to the left. Absolute Cell References Example: $A$1 Dollar signs before both: Column designation = A Row designation = 1 “Absolute” means that if the formula is looking at a particular cell reference, when you copy the formula to any other cell, the cell reference will still be looking at that particular cell reference. If the Absolute Cell reference is $A$1, the formula will always look at cell A1. It is as if the formula is locked on the cell A1 throughout copy action “Locks cell reference when copying it horizontally and vertically” “Mixed Cell Reference with Column Locked” Example: $A1 Dollar sign before column designation Remains absolute or locked when copying across columns Remains relative when copying across rows “Locks cell reference when copying it horizontally, but not vertically” “Mixed Cell Reference with Row Locked” Example: A$1 Dollar sign before row designation Remains absolute or locked when copying across rows Remains relative when copying across columns “Locks cell reference when copying it vertically, but not horizontally” Keyboard shortcut F4 key: Toggles between the four types of cell references When creating formulas with cell references, ask two questions of every cell reference in formula: Q1: What do you want it to do when you copy it horizontally? Is it a relative reference? OR Is it an absolute or locked reference? Q2: What do you want it to do when you copy it vertically? Is it a relative reference? OR Is it an absolute or locked reference? Here are the steps to learn about the four cell references: Go to the sheet tab named “Cell References” and click in cell C3 and create the formula shown in Figure 141. The formula calculates a proportion or percentage of the whole (depending on how it is formatted). In our example we are comparing Tina’s score (82 parts of the 100 whole) and comparing it to the total possible points (100 points – the whole). If you look ahead to creating the formulas for Sue and Chin and Hien, all their “parts” will have to be compared to the “whole”. Figure 141 Hold Ctrl, then tap Enter. You should see that the proportion of points that Tina earned is .82 (Figure 142): Figure 142
Excel2010IsFun!.docx