Mixed references ($A1, A$1) in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula when copied?

You have the formula =B$2 + $A3 in cell C3. What will be the formula in cell D4 after copying it from C3 to D4?

A =C$3 + $B4

B =C$2 + $B3

C =B$3 + $A4

D =C$2 + $A4

💡 Hint

Remember that $ before a letter locks the column, and $ before a number locks the row.

The original formula is =B$2 + $A3 in cell C3.

When copied one column to the right (C to D), the column B changes to C because it is relative (no $ before B). The row 2 is locked ($2), so it stays 2.

The second part has $ before A, so column A is locked, but row 3 is relative and changes to 4 because we moved one row down (3 to 4).

So the formula in D4 is =C$2 + $A4.

❓ Function Choice

intermediate

Which formula correctly sums values with mixed references?

You want to sum values in row 5 from columns B to E, but always add the value in cell A5. Which formula correctly uses mixed references when copied across columns?

A =SUM(B5:E5) + $A5

B =SUM($B5:$E5) + $A$5

C =SUM(B$5:E$5) + $A5

D =SUM(B5:E5) + A$5

💡 Hint

Think about which parts should stay fixed when copying across columns.

The sum range B5:E5 is relative and will adjust if copied, which is usually desired.

The value in $A5 locks the column A but allows the row to change, so when copied across columns, it always refers to column A in the same row.

Option A correctly uses mixed references to keep the column fixed for the added value.

❓ data_analysis

advanced

How many unique cells are referenced after copying?

You have the formula =SUM($B2:B$5) in cell C3. You copy this formula to cells C4, D3, and D4. How many unique cells are referenced in total by all these formulas?

A 8 unique cells

B 12 unique cells

C 10 unique cells

D 14 unique cells

💡 Hint

Analyze how the mixed references change when copying across rows and columns.

Original formula in C3: =SUM($B2:B$5)

- $B2 locks column B, row 2 is relative.

- B$5 locks row 5, column B is relative.

Copy to C4 (one row down): =SUM($B3:B$5)

Copy to D3 (one column right): =SUM($B2:C$5)

Copy to D4 (one row down, one column right): =SUM($B3:C$5)

Counting unique cells referenced:

• From C3: B2, B3, B4, B5 (4 cells)
• From C4: B3, B4, B5 (3 cells, B3-B5 overlap with above)
• From D3: B2, C2, B3, C3, B4, C4, B5, C5 (8 cells)
• From D4: B3, C3, B4, C4, B5, C5 (6 cells)

Combining all unique cells: B2, B3, B4, B5, C2, C3, C4, C5 (8 unique cells)

🎯 Scenario

advanced

You want a formula that always sums the same row but adjusts columns when copied

Which formula should you enter in cell B2 so that when copied across columns, it always sums row 1 from column A to the current column?

A =SUM($A$1:A$1)

B =SUM(A$1:$A1)

C =SUM($A1:A1)

D =SUM(A1:$A1)

💡 Hint

Think about locking the row but allowing the column to change.

Formula =SUM($A1:A1) locks the first column to A but allows the second column to change as you copy across.

Row 1 is locked because of the 1 in the reference, and since the formula is copied across columns only, the row remains the same.

This way, in B2, it sums A1:B1; in C2, it sums A1:C1, and so on.

📊 Formula Result

expert

What is the value of cell D5 after copying this formula?

Cell B3 contains the formula =SUM($A$1:A$1) + $B3. You copy this formula to cell D5. If A1=2, B3=5, B5=7, and C1=3, what is the value in D5?

A 10

B 12

C 14

D 17

💡 Hint

Check how the mixed references change when copying from B3 to D5 and sum the correct cells.

Original formula in B3: =SUM($A$1:A$1) + $B3

- $A$1 is absolute, always A1 (value 2).

- A$1 locks row 1 but column is relative.

In B3, SUM($A$1:A$1) sums A1:A1 = 2.

- $B3 locks column B, row relative.

In B3, $B3 is B3 = 5.

When copied to D5 (2 columns right, 2 rows down):

• $A$1 stays A1 (2)
• A$1 column moves 2 right: A->C, row locked 1 → C1 (3)
• $B3 column locked B, row moves 2 down: 3->5 → B5 (7)

Sum range is A1:C1 = 2 + (B1) + 3. B1 is not given, assume 0.

Sum = 2 + 0 + 3 = 5

Add $B5 = 7

Total = 5 + 7 = 12