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

Practice

1. What does the mixed reference $A1 mean in Excel?

easy

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

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

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

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

Solution

1. Step 1: Understand the $ symbol before the column letter

   The $ before A means the column A is fixed and will not change when the formula is copied across columns.

2. Step 2: Understand the absence of $ before the row number

   The row number 1 is not fixed, so it will change when the formula is copied across rows.

3. Final Answer:

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

4. Quick Check:

   Mixed reference $A1 fixes column only [OK]

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

Common Mistakes:

• Thinking $A1 fixes the row instead of the column
• Confusing $A1 with A$1
• Assuming both row and column are fixed

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

easy

A. $5A

B. A$5

C. $A$5

D. 5$A

Solution

1. Step 1: Identify the correct placement of $ for fixing row

   To fix the row 5, the $ must be placed before the row number: A$5.

2. Step 2: Check the options for correct syntax

   A$5 uses A$5 which fixes row 5 and allows column to change. Other options have incorrect order or fix both row and column.

3. Final Answer:

   A$5 -> Option B

4. Quick Check:

   Row fixed with $ before number = A$5 [OK]

Hint: Put $ before row number to fix row, before column letter to fix column [OK]

Common Mistakes:

• Placing $ after the row number
• Swapping column letter and row number
• Using $ before both column and row when only one should be fixed

3. If cell B2 contains the formula = $A1 + A$1 and you copy this formula to cell C3, what will be the resulting formula in C3?

medium

A. = $B2 + B$2

B. = $A3 + C$1

C. = $A2 + B$1

D. = $A3 + B$1

Solution

1. Step 1: Analyze the $A1 part when copied from B2 to C3

   $ before A fixes the column A, so column stays A. Row 1 changes relative to row move: from row 2 to 3 is +1, so row 1 becomes 2. So $A1 becomes $A2.

2. Step 2: Analyze the A$1 part when copied from B2 to C3

   $ before 1 fixes the row 1, so row stays 1. Column A changes relative to column move: from B to C is +1, so A becomes B. So A$1 becomes B$1.

3. Final Answer:

   = $A2 + B$1 -> Option C

4. Quick Check:

   Fixed column stays, fixed row stays, others shift [OK]

Hint: Fixed parts stay; unfixed parts shift with copy [OK]

Common Mistakes:

• Changing fixed column or row incorrectly
• Not adjusting relative parts when copying
• Mixing up column letters and row numbers

4. You have a formula in cell D4: =SUM($B2:B$5). When copying this formula to cell E6, the formula becomes =SUM($B4:C$5). Is this correct? If not, what is the correct formula?

medium

A. =SUM($B4:C$5)

B. =SUM($B4:B$5)

C. =SUM($B4:C$7)

D. =SUM($B2:C$7)

Solution

1. Step 1: Trace the start reference $B2 from D4 to E6

   Copy is +1 column (D->E), +2 rows (4->6). $B fixes column B; row 2 relative +2 -> $B4.

2. Step 2: Trace the end reference B$5

   Column B relative +1 -> C; $5 fixes row 5 -> C$5.

3. Step 3: Resulting formula is =SUM($B4:C$5)

   This matches the formula stated in the question, confirming it is correct.

4. Final Answer:

   =SUM($B4:C$5) -> Option A

5. Quick Check:

   $B2:B$5 -> $B4:C$5 (+1col +2row) [OK]

Hint: Shift relative parts by copy distance; fixed parts stay [OK]

Common Mistakes:

• Not shifting row or column correctly
• Assuming fixed parts change
• Miscalculating relative shifts for range endpoints

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 it across columns and rows. Which mixed reference formula should you use in B2?

hard

A. = $A2 * B$1

B. = A$2 * $B1

C. = $A$2 * $B$1

D. = A2 * B1

Solution

1. Step 1: Fix the column for the value in column A but allow row to change

   The value in column A of the same row means column A fixed ($A), row relative (2). So use $A2.

2. Step 2: Fix the row for the value in row 1 but allow column to change

   The value in row 1 of the same column means row 1 fixed ($1), column relative (B). So use B$1.

3. Step 3: Combine both parts in multiplication formula

   The formula is = $A2 * B$1, which will adjust correctly when copied across rows and columns.

4. Final Answer:

   = $A2 * B$1 -> Option A

5. Quick Check:

   Fix column for first, fix row for second [OK]

Hint: Fix column for vertical, fix row for horizontal references [OK]

Common Mistakes:

• Fixing both row and column unnecessarily
• Not fixing any part causing wrong references
• Swapping fixed parts between references