Excel Guides

Non-adjusting References in Formulas in Excel

When creating formulas in Excel, you can use cell references to refer to other cells in the worksheet. These references can be absolute or relative, and you can use a mix of both types in a single formula. In some cases, however, you may want to create a formula that refers to a cell without the reference changing when you copy or move the formula.

To do this, you can use a non-adjusting reference, also called a mixed reference. A mixed reference has both an absolute and a relative component. The absolute component is denoted by a dollar sign ($) before the column letter and/or row number.

For example, if you want to refer to cell B5 without the reference changing when you copy the formula, you would use a mixed reference: $B$5. This would be an absolute reference to column B and row 5. If you just wanted an absolute reference to column B (so that the row would change when you copied the formula), you would use B$5. Similarly, if you just wanted an absolute reference to row 5, you would use $B5.

You can also use mixed references in ranges. For example, if you want to refer to the range B5:C7 without the references changing when you copy the formula, you would use $B$5:$C$7. This would be an absolute reference to columns B and C, and rows 5 through 7.

If you want the column part of the reference to be relative but not the row part (or vice versa), you can use mixed references without the dollar signs around both parts of the reference. For example, B$5 would be a relative reference to column B and an absolute reference to row 5. You could also use $B5, which would be an absolute reference to column B and a relative reference to row 5.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.