Images are reused AI-generated editorial illustrations, not documentary photographs or verified product screenshots.
Dollar signs in Excel formulas control which parts of a cell reference change when a formula is copied or filled. They do not protect the cell or turn a value into a constant. For a client workbook, choose the reference deliberately so repeated calculations continue using the intended rate, threshold or source row.
Read the row and column separately
B2 is relative; $B$2 locks both parts for copying; $B2 locks the column; B$2 locks the row. These forms serve different layouts. Check whether a formula will move down, across or both before choosing. A dollar sign is not a currency symbol in this context, and an absolute reference does not prevent the referenced value from changing.
Copy a small test before filling the range
An example such as =C2*$B$1 uses the changing quantity in column C and the referenced rate in B1. Copy to the next intended location and inspect the formula, not only the result. A correct-looking total can hide a reference mistake when the test values happen to be similar.
Review structural and maintenance changes
Keep the rate or assumption clearly labeled and document its owner. Insertions, moved ranges and other structural edits can affect formulas under Excel’s reference rules; dollar signs are not a universal freeze on workbook structure. Test the workbook after changes and avoid replacing references with repeated hardcoded numbers merely to make one result look right.
A practical checklist
- Decide which row and column should move.
- Use mixed or absolute references intentionally.
- Test a copy down and across where applicable.
- Label shared assumptions and their owner.
- Retest after structural workbook edits.
Worked example
Illustrative example: a quantity column uses one agreed test rate in B1. The first formula refers to C2 and $B$1; the next correctly refers to C3 and $B$1. A version without the locked rate would shift the reference during copying. The example demonstrates reference behavior, not an actual client billing calculation.
Common questions
Does $ protect a cell from editing? No. Is every reference supposed to be absolute? No. Does a locked reference mean the source value cannot change? No; it controls copying behavior, not business approval.
What to do next
Keep a few deliberately different test values in a safe copy and inspect the filled formulas. Reference checks are more reliable when a mistaken shift cannot produce an accidentally plausible result.
Sources and further reading
- support.microsoft.com: switch between relative absolute and mixed references
- support.microsoft.com: overview of formulas in excel