Sheets Cell References – Absolute & Relative

Cell references in formulas allow you to create dynamic calculations which can save you time and enable you to use the formulas across different cells.

It’s important to learn that there are two main types of referencing, Relative and Absolute, along with a third which is a mixture of the two. As we’ll see in the examples below, there are times when locking references onto specific cells is essential and there are times when we want the references to change if we copy the formula somewhere else.

Relative Cell Referencing

Relative referencing is a time-saving tool when working with similar calculations across rows or columns. Let’s say you’re tracking exam scores for multiple students across various subjects. Instead of writing separate formulas for each student, enter your formula for one cell and simply copy it across the row or column.

For example, if your formula in cell B7 adds up scores in cells B3 to B6, using the SUM function, you can drag it horizontally (click on and drag the little blue square) across cells C7, D7, and E7 to add up those columns too. Sheets adjusts the formula automatically to reference cells in each new column, making C7 reference C3:C6, D7 reference D3:D6, and so on.

This method—known as relative cell referencing—is ideal for calculations that repeat across similar data sets without requiring unique cell references for each entry.

Absolute Cell Referencing

While relative references adjust automatically, absolute referencing is used to “lock” a specific cell in place. This is helpful when a formula relies on a constant value that shouldn’t change as it’s copied elsewhere.

For instance, to calculate percentages, you might divide each student’s score by a fixed total score stored in cell H7.

If we enter =B7/H7 in cell B15 and then copy that across the row to E15, we see that an error appears, and that’s because the reference to the total score as changed and is looking at empty cells.

This is where absolute references come in. By typing =B7/$H$7 in a formula, the dollar signs keep H7 fixed as the total reference, even if the formula is dragged across multiple rows or columns.

When you drag this formula from cell B17 across other columns C to E, Sheets maintains the reference to H7, avoiding errors and ensuring that the fixed value is applied in every instance.

Absolute references make it easy to use constants in your formulas without needing to rewrite them each time.

Mixed Cell Referencing

Mixed referencing combines relative and absolute references, letting you fix either the row or the column while allowing the other to vary. This technique can be especially handy in tables where one value needs to stay constant across rows or columns.

For example, using $B3 fixes the column while the row remains flexible, allowing you to apply a formula down a column but still change rows as needed.

Similarly, B$3 fixes the row while the column can change as the formula is dragged horizontally.

Mixed referencing is great for building dynamic formulas that require both fixed and variable inputs, saving you from constant manual adjustments.

More details in this video taken from my Google Sheets course:

Find a more detailed post here.