Mixed references in Google Sheets - Step-by-Step Guide

Introduction

Mixed references help you fix either the row or the column in a cell address when copying formulas. This lets you control how formulas adjust when you copy them across rows or columns, saving time and avoiding errors.

When you want to copy a formula across columns but keep the row fixed, like applying a tax rate to different products in the same row.

When you want to copy a formula down rows but keep the column fixed, like multiplying sales numbers in different rows by a fixed commission rate in one column.

When you create a multiplication table and want to lock either the row or column reference to get correct results.

When you have a price list in one row and quantities in a column and want to calculate totals by mixing fixed rows and columns.

When you want to avoid manually changing formulas after copying them to many cells.

Steps

Step 1: Click

- a cell where you want to enter a formula

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula starts appearing in the formula bar

💡 Start with = to begin a formula

Step 3: Enter

- a cell reference with a $ sign before the column letter only (e.g., $A1) or before the row number only (e.g., A$1)

The reference becomes a mixed reference fixing either the column or the row

Step 4: Complete

- the formula and press Enter

The formula calculates the result in the cell

Step 5: Copy

- the cell with the mixed reference formula

The formula is copied with the fixed row or column as specified

Step 6: Paste

- to other cells across rows or columns

The formula adjusts only the unfixed part of the reference, keeping the fixed row or column constant

Before vs After

Before

Formula in cell B2 is =A1 and copying it to C3 changes it to =B2

After

Formula in cell B2 is =$A1 and copying it to C3 changes it to =$A2 (column fixed, row relative)

Settings Reference

Absolute column reference

📍 Formula bar when typing a formula

Fixes the column so it does not change when copying the formula

Default: No $ sign means relative reference

Absolute row reference

📍 Formula bar when typing a formula

Fixes the row so it does not change when copying the formula

Default: No $ sign means relative reference

Common Mistakes

Using $ before both column and row (e.g., $A$1) when only one should be fixed

This makes the reference fully absolute, so it never changes when copied, which may not be desired

Use $ only before the column or only before the row to create a mixed reference

Not using $ at all, causing all references to change when copying

This can cause formulas to refer to wrong cells after copying

Add $ before the part you want to keep fixed (column or row)

Summary

Mixed references fix either the row or the column in a cell reference using $.

They help formulas adjust correctly when copied across rows or columns.

Remember to place $ only before the column letter or row number, not both, for mixed references.

Practice

1. What does a mixed reference like $A1 mean in Google Sheets?

easy

A. Neither the column nor the row is fixed; both change when copied.

B. The column A is fixed, but the row number changes when copied.

C. Both the column and row are fixed and do not change when copied.

D. The row 1 is fixed, but the column letter changes when copied.

Solution

1. Step 1: Understand the dollar sign placement

   In $A1, the dollar sign before A means the column is fixed.

2. Step 2: Analyze the row number

   The row number 1 has no dollar sign, so it changes when copied.

3. Final Answer:

   The column A is fixed, but the row number changes when copied. -> Option B

4. Quick Check:

   Mixed reference $A1 fixes column A [OK]

Hint: Dollar before column fixes column; before row fixes row [OK]

Common Mistakes:

• Thinking $ fixes both row and column
• Confusing which part the $ fixes
• Assuming no change happens when copying

2. Which of the following is the correct syntax for a mixed reference that fixes the row but allows the column to change?

easy

A. A$1

B. $A$1

C. $A1

D. A1$

Solution

1. Step 1: Identify the dollar sign position for fixing row

   To fix the row, place $ before the row number, like A$1.

2. Step 2: Check other options

   $A$1 fixes both row and column, $A1 fixes column only, and A1$ is invalid syntax.

3. Final Answer:

   A$1 -> Option A

4. Quick Check:

   Dollar before row number fixes row [OK]

Hint: Put $ before row number to fix row only [OK]

Common Mistakes:

• Placing $ after the row number (invalid)
• Fixing both row and column unintentionally
• Confusing column and row positions

3. Given the formula =B$2 + $C3 in cell D4, what will be the formula in cell D5 after copying it down one row?

medium

A. =B$2 + $C4

B. =B$3 + $C4

C. =B$3 + $C3

D. =B$2 + $C3

Solution

1. Step 1: Analyze the first reference B$2

   Column B is relative, row 2 is fixed by $. Copying down one row keeps row 2 fixed, so it stays B$2.

2. Step 2: Analyze the second reference $C3

   Column C is fixed by $, row 3 is relative. Copying down one row changes row 3 to 4, so it becomes $C4.

3. Final Answer:

   =B$2 + $C4 -> Option A

4. Quick Check:

   Mixed references adjust only unfixed parts [OK]

Hint: Only parts without $ change when copied [OK]

Common Mistakes:

• Changing fixed rows or columns
• Not updating relative row numbers
• Confusing which part is fixed

4. Given the formula =SUM($A1:B$2) in cell C3. When copying this formula to cell D4, what will be the formula?

medium

A. =SUM($A1:B$2)

B. =SUM($A1:C$2)

C. =SUM($A2:B$2)

D. =SUM($A2:C$2)

Solution

1. Step 1: Understand how references change when copied

   Copying from C3 to D4 moves one column right and one row down. $A1 changes to $A2 (row changes), B$2 changes to C$2 (column changes).

2. Step 2: Check the resulting range

   The range becomes $A2:C$2.

3. Final Answer:

   =SUM($A2:C$2) -> Option D

4. Quick Check:

   Mixed references adjust range correctly [OK]

Hint: Trace relative column/row shifts separately [OK]

Common Mistakes:

• Forgetting to adjust relative row from 1 to 2
• Not updating relative column from B to C
• Assuming no changes when copying
• Confusing fixed vs relative parts

5. You want to create a formula in cell B2 that multiplies the value in column A of the same row by the value in row 1 of the same column, and then copy this formula across and down the table. Which formula uses mixed references correctly?

hard

A. =$A$2*B1

B. =A2*$B$1

C. =$A2*B$1

D. =A$2*$B1

Solution

1. Step 1: Fix the column for the value in column A

   The value in column A of the same row means column A fixed, row relative: $A2.

2. Step 2: Fix the row for the value in row 1 of the same column

   The value in row 1 of the same column means row 1 fixed, column relative: B$1.

3. Step 3: Combine for multiplication

   The formula is =$A2*B$1, which copies correctly across and down.

4. Final Answer:

   =$A2*B$1 -> Option C

5. Quick Check:

   Fix column for A, fix row for 1 [OK]

Hint: Fix column for A, fix row for 1 to copy correctly [OK]

Common Mistakes:

• Fixing both row and column incorrectly
• Not fixing the correct part of the reference
• Using absolute references that prevent copying