The Misunderstood Absolute
One common misunderstood concept is the Absolute/Mixed Cell Reference in Excel. When writing a formula, if you are using one cell that remains the same throughout the entire formula, you would incorporate an Absolute Cell Reference. However, if you just need the row or the column to remain consistent, you apply a Mixed Cell Reference. Both references are applied using the dollar ($) symbol. Absolute would have a $ symbol in front of both the row and column, whereas, Mixed would have a $ symbol in front of either the row or column, not both. Where you put the $ symbol in the Mixed Reference, would depend on where you need your data to flow (row-by-row vs. column-by-column).
I advised students that the $ symbol holds the spot of the data in the cell. Excel knows to carry formulas across or down using a Relative Cell Reference, depending on where you are dragging your cursor. By adding the $ symbol, it prevents the row/column change over. Using these types of formulas prevents the writer from having to re-write each formula.
I am not entirely sure what analogy I can provide for this explanation. In class, we start with a Relative Cell Reference for a week or two, so students can get an idea of writing formulas. We then add the Mixed and Absolute Cell Reference with a multitude of examples and practice scenarios, which assist students in writing them correctly; however, it is the hardest formula in Level 1 Excel.
TweetExample for "The Misunderstood Absolute":
n/a