Mixed references in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

Column A has numbers, Column B has multipliers. Column C will calculate A times B with mixed references.

Cell	Value
A1	10
A2	20
A3	30
B1	2
B2	3
B3	4
C1
C2
C3

Formula Trace

=A$1*B2

Step 1: A$1 in row 2 refers to A1

Step 2: B2 in row 2 refers to B2

Step 3: Multiply A$1 * B2 = 10 * 3

Cell Reference Map

    A    B    C
1 | 10 |  2 |    |
2 | 20 |  3 | -> |  Formula in C2: =A$1*B2
3 | 30 |  4 |    |

Formula in C2 uses mixed reference A$1 (fixed row 1) and relative B2.

Result

    A    B    C
1 | 10 |  2 |    |
2 | 20 |  3 | 30 |
3 | 30 |  4 |    |

In cell C2, formula =A$1*B2 calculates 10*3 = 30.

Sheet Trace Quiz - 3 Questions

In the formula =A$1*B2 entered in C2, what does A$1 refer to?

A Cell B1 (wrong column)

B Cell A2 (same row)

C Cell A1 (fixed row 1)

D Cell B2 (wrong column)

Key Result

Mixed references fix either row or column with $ to keep part of the reference constant when copied.

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