Mixed references ($A1, A$1) in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to keep the column fixed but allow the row to change when copied.

Excel

=SUM([1]1:B1)

Drag options to blanks, or click blank then click option'

AA$

B$A

CA

D$B

Attempts:

3 left

💡 Hint

Common Mistakes

Using A$1 fixes the row, not the column.

Not using any dollar sign allows both row and column to change.

Using $A1 fixes the column A but allows the row number to change when copied.

2fill in blank

medium

Complete the formula to fix the row but allow the column to change when copied.

Excel

=AVERAGE(A[1]:D5)

Drag options to blanks, or click blank then click option'

A5

B$D

C$5

DD$

Attempts:

3 left

💡 Hint

Common Mistakes

Using $5 fixes neither column nor row correctly.

Using 5 without dollar sign allows row to change.

Using A$5 fixes the row 5 but allows the column to change when copied.

3fill in blank

hard

Fix the error in the formula to correctly fix the column and allow the row to change.

Excel

=SUM([1]1:C1)

Drag options to blanks, or click blank then click option'

A$A

BA$

CA

D$1

Attempts:

3 left

💡 Hint

Common Mistakes

Using A$ fixes the row, not the column.

Using $1 is invalid as a column reference.

The correct mixed reference to fix the column A and allow the row to change is $A1.

4fill in blank

hard

Fill both blanks to create a formula that fixes the row in the first cell and fixes the column in the second cell.

Excel

=SUM(A[1]:[2]1)

Drag options to blanks, or click blank then click option'

A$5

BB

C$C

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using no dollar signs allows both row and column to change.

Mixing up which part to fix causes wrong references.

A$5 fixes the row 5 in the first cell, and $C1 fixes the column C in the second cell.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps each column letter to a mixed reference fixing the row 10 but allowing the column to change.

Excel

{ [1]: [2][3] for [1] in ['A', 'B', 'C'] }

Drag options to blanks, or click blank then click option'

Acol

B$

C10

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variable names for key and value.

Not fixing the row number with a dollar sign.

The variable col is used as key and in the value. The value uses col$10 to fix row 10 but allow column to change. Since col is a string like 'A', the correct mixed reference is col & "$10" in Excel formulas, but here we simulate with string keys and values.

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