6 Relative, absolute, and mixed cell references

Cell References

Cell References are a way to refer to the contents of a cell. This is more efficient and more accurate than typing numbers in each formula.

  • Cell reference: A reference to a cell that contains the value (item) you want to use in your Excel formula, e.g. =SUM(A1, B2, C3). In this formula, A1, B1, and C1 are all cell references. This allows you to create formulas that can be copied and reused, rather than requiring manual input of the numbers each time. Cell references can be relative, absolute, or mixed.
  • Relative: Relative references change when a formula is copied to another cell. This allows a formula to be reused and to pick up the contents of the record around it so that you don’t have to manually input all numbers (constants) yourself.
  • Absolute: Absolute references remain constant, no matter where they are copied. References are made absolute by adding $ to the cell reference: $A$1.
  • Mixed: Mixed references lock either the row or the column, allowing the other part to change when copied. This is where the $ is before either the row or column, but not both. For Example A$1, this would change to B$1 because the column is relative (A to B), but the row is fixed (1 stays the same).
  • Named: You can Define a Name of a cell so that the cells remain stable in a formula that you copy and paste for further use in other cells in a column. This would be an Absolute reference. Otherwise, the pasted formula will change the cell’s location reference to be relative to the new row it is seeking to create the formula for. Example: =Fee*20, where the cell C5 has been named FEE, is more stable than =C5*20. Other formulas needing the contents of cell C5 would use the named reference FEE instead.
  • Circular Reference: This is an error. It happens when a formula is trying to calculate itself, and you have an Excel feature called iterative calculation turned off. It can also happen during indirect references in a formula. It results in error messages.

Note: Formulas can include a mix of relative, absolute, and mixed references as needed. For Example =(A1+B1)*$C$1. In this formula, if you autofilled the formula down, it would change to =(A2+B2)*$C$1.


We will focus on Relative, Absolute, and Mixed Cell References for the rest of this lesson.

Relative References

Relative: Relative references change when a formula is copied to another cell.  What this means is that if you create a formula to sum up the total numbers of a row, then paste that same formula down one cell to sum up the total numbers of the next row, Excel will assume you want the references to the cells with the numbers in the new row to change inside the pasted formula.

Row 1’s Cell D1: cell A1 + cell B1 + cell C1 would equal the total of the numbers in those 3 cells. The formula in cell D1 would be =A1+B1+C1

Then, pasting  =A1+B1+C1 into Row 2’s cell D2 would automatically change it to =A2+B2+C2, because Excel assumes you want the formula to calculate the proper total for Row 2 information. This is what a “relative” reference in a formula in Excel means.

Relative references in a formula allow a formula to be reused and to pick up the contents of the record around it so that you don’t have to manually input all numbers (constants) yourself.

Absolute References

Absolute references remain constant, no matter where they are copied. This means that the number inside a specific cell (like a tax rate or a specific discount percentage) should remain the exact same info in every instance of a formula it is used in. This is useful if you need one value (number) in a formula to remain the same no matter what rows of data are being calculated against it. We’ll do this with the Tax Needed cells in column G.

An absolute reference is referred to differently in a formula / function. The idea is the tell Excel to freeze the reference so that it remains static – the same cell, no matter how it is used in a column that might otherwise auto-calculate relative references for you.

The Absolute reference format uses a dollar symbol ($) before the cell’s coordinates to make the cell reference fixed/absolute. An example would be referring to the Cell G12 (a fixed tax amount) as $G$12 in a calculation.

TIP: Use Universal Constants. There will be times when you are writing formulas that you will need to use universal constants, or numbers that do not change, such as the number of days in a week, weeks or months in a year, and so on. For example, if you are calculating the monthly cost of an item when you know the yearly cost, you will always divide by 12 since there are 12 months in a year. In this case, you can use the constant of 12 instead of an absolute cell reference because the number of months in a year never changes.

Mixed References

Mixed references lock either the row or the column in a formula, but not both. This means that part of the cell reference remains constant while the other part adjusts when copied across rows or columns. Mixed references are useful when you want one part of the formula to stay fixed—like a column of weights or a row of constants—while the other part changes dynamically as the formula is copied.

In Excel, mixed references are written with a dollar symbol ($) before either the column letter or the row number. For example:

$A1 locks the column (A stays fixed, row changes)
A$1 locks the row (1 stays fixed, column changes)
This is helpful when you’re working with tables or grids where one axis (row or column) contains fixed values, such as grading weights or conversion factors, and the other axis contains variable data.

As an example, in a sheet that calculates student grades, we use mixed references in the Weighted Score cells, where the weights are fixed in row 2, but the student scores vary by row.

Mixed references are especially useful in two-dimensional calculations, like multiplying a row of scores by a row of fixed weights. They allow formulas to adapt intelligently while keeping key parts anchored.


The learning content from this assignment came from the Pressbook: Chapter 13: Formulas – Operations – MS Excel Bootcamp.