R10 Additional Excel questions

Some questions below refer to the data saved in an Excel file. The data is presented in two sheets:

Table 1: Patient consultation history (Sheet consultation_history).
A B C D E F G H I J K
1 Patient ID Week Day OR Gender Age Expected duration Order of surgery Surgery type Registered duration Surgery types
2 70 0 1 1 male 83 62.44 13 WP174 160.61 AF241
3 1215 5 3 2 male 48 58.0 10 ID810 158.21 AX939
4 1439 6 4 0 male 61 58.0 10 ID810 151.68 BP285
5 502 2 1 2 male 43 58.0 11 ID810 142.66 CP913
6 1120 5 1 1 male 65 62.44 13 WP174 142.47 GV023
Table 2: Patient names and genders (Sheet patient_data).
A B
1 Patient name Gender
2 Leslie Fields male
3 Arnold Ross male
4 Craig Vega male
5 Doyle Banks Junior male
WarningFormula separators

In some countries, the comma (,) is used as a decimal separator, and the semicolon (;) is used as a list separator. If you are using Excel in a country where the comma is used as a decimal separator, you may need to use the semicolon as a list separator in Excel formulas.

By default, Excel for Windows uses the list separator in Windows regional settings. Excel’s function tooltip shows the separator expected by your system while you type a formula. The Use system separators option under File > Options > Advanced controls decimal and thousands separators, not the formula list separator.

In the examples below, we use a comma as the list separator. If you copy a formula into an installation that expects semicolons, replace the commas between function arguments with semicolons. Colons still denote cell ranges, such as A2:A6.

R10.1 Conditional evaluation

In cell I2, there is a value “WP174” (Surgery type). If we apply the Excel formula =IF(I2="WP174", "Correct", "Incorrect") on Table 1, what will be the result?

  • WP174
  • Correct
  • Incorrect
  • Error

Answer: B) Correct

The formula will return “Correct” because the value in cell I2 is “WP174.”

R10.2 Vertical lookup

If you use the Excel formula =VLOOKUP(502, A2:K6, 9, FALSE) on Table 1, what will it return?

  • 2 (value in the “OR” column for Patient ID 502)
  • 43 (value in the “Age” column for Patient ID 502)
  • ID810 (value in the “Surgery type” column for Patient ID 502)
  • 1 (value in the “Day” column for Patient ID 502)
TipVLOOKUP Function

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: The value to search for in the first column of the table_array.
  • table_array: The range of cells that contains the data.
  • col_index_num: The column number in the table_array from which to retrieve the value (indexed from 1).
  • range_lookup: A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

Answer: C) ID810

The formula =VLOOKUP(502, A2:K6, 9, FALSE) will return “ID810” because it looks up the value 502 in the first column of the range A2:K6 and returns the value in the 9th column (Surgery type) when an exact match is found.

R10.3 Average calculation

If you enter the formula =AVERAGE(G2:G6) in Excel considering the data in Table 1, what will it calculate?

  • The average of the values in the “Age” column
  • The average of the values in the “Expected duration” column
  • The average of the values in the “Registered duration” column
  • The average of the values in the “Order of surgery” column

Answer: B) The average of the values in the “Expected duration” column.

The formula =AVERAGE(G2:G6) will calculate the average of the values in the “Expected duration” column (column G).

R10.4 Conditional counting

Considering the data in Table 1, what will the formula =COUNTIF(D2:D6, 1) count?

  • The number of surgery records assigned to OR 1
  • The number of patients who had surgery in the 1st week
  • The number of patients with a registered surgery duration of 1 hour
  • The number of patients with a surgery type ID starting with 1
TipCOUNTIF Function

COUNTIF(range, criteria)

  • range: The range of cells that you want to apply the criteria to.
  • criteria: The condition that determines which cells to count.

Answer: A) The number of surgery records assigned to OR 1.

The formula =COUNTIF(D2:D6, 1) counts the occurrences of the value 1 in the “OR” column (column D). Each row in this table is one surgery record.

R10.5 Total sum

If you use =SUM(G2:G6) in the dataset presented in Table 1, what are you summing?

  • The total number of surgeries
  • The total expected surgery duration for the selected patients
  • The total patient ages
  • The total number of patients scheduled in OR 1

Answer: B) The total expected surgery duration for the selected patients.

The formula =SUM(G2:G6) will sum the values in the “Expected duration” column (column G), giving the total expected surgery duration for the selected patients.

R10.6 Multiple criteria sum

Considering the data in Table 1, what is the formula =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50") calculating?

  • The total expected duration of surgeries for male patients above the age of 50
  • The sum of ages for male patients above 50
  • The total number of surgeries for male patients above 50
  • The total registered duration of surgeries for male patients above 50
TipSUMIFS Function

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  • sum_range: The range to sum.
  • criteria_range1: The range to evaluate with criteria1.
  • criteria1: The criteria to apply to criteria_range1.
  • criteria_range2, criteria2, …: Additional ranges and their associated criteria.

Answer: A)

The formula =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50") calculates the total expected duration of surgeries for male patients older than 50.

R10.7 Multiple criteria counting

If you use the formula =COUNTIFS(D2:D6, 1, C2:C6, 1) in the dataset presented in Table 1, what is being counted?

  • The number of surgeries that took place in OR 1 during Week 1
  • The number of surgery records assigned to OR 1 on Day 1
  • The number of male patients scheduled in OR 1 during Week 1
  • The number of surgeries that had an expected duration of 1 hour
TipCOUNTIFS Function

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  • criteria_range1: The range to evaluate with criteria1.
  • criteria1: The criteria to apply to criteria_range1.
  • criteria_range2, criteria2, …: Additional ranges and their associated criteria.

Answer: B)

The formula =COUNTIFS(D2:D6, 1, C2:C6, 1) counts the surgery records assigned to OR 1 on Day 1.

R10.8 Total surgery duration

Considering the data in Table 1, you want to calculate the total expected surgery duration for male patients over 50 years old. Which formula explicitly applies both conditions and remains correct if the dataset also contains other genders?

  • =SUMIFS(F2:F6, E2:E6, "male", G2:G6, ">50")
  • =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50")
  • =COUNTIFS(E2:E6, "male", F2:F6, ">50")
  • =SUMIF(F2:F6, ">50", G2:G6)

Answer: B) =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50")

Breakdown:

  • G2:G6: The range of values to sum (Expected duration).
  • E2:E6: The gender criteria range.
  • "male": The gender criterion.
  • F2:F6: The age criteria range.
  • ">50": The age condition.

R10.9 Surgery count

To count the number of surgeries performed in OR 1 for patients older than 60 in Table 1, which formulas give the correct result for this dataset? (Multiple answers may be correct.)

  • =COUNTIFS(D2:D6, 1, F2:F6, ">60")
  • =SUMIFS(D2:D6, D2:D6, 1, F2:F6, ">60")
  • =COUNTIFS(E2:E6, "male", F2:F6, ">60")
  • =COUNTIF(F2:F6, ">60")

Answers: A) =COUNTIFS(D2:D6, 1, F2:F6, ">60") and B) =SUMIFS(D2:D6, D2:D6, 1, F2:F6, ">60")

Breakdown:

  • Formula A counts rows for which column D equals 1 and column F is greater than 60.
  • Formula B sums the values in column D for the same rows. Because each selected row contains 1 in column D, the sum also equals the number of matching rows in this dataset.

R10.10 Surgery duration

Considering Table 1, you need to find the total registered surgery duration for surgeries in Week 2. Which formula would be correct?

  • =SUMIFS(J2:J6, B2:B6, 2)
  • =SUMIFS(G2:G6, B2:B6, "Week 2")
  • =COUNTIFS(B2:B6, 2)
  • =SUMIF(B2:B6, 2, H2:H6)

Answer: A) =SUMIFS(J2:J6, B2:B6, 2)

Breakdown:

  • J2:J6: The range of values to sum (Registered duration).
  • B2:B6: The range to evaluate with the criteria (Week).
  • 2: The criteria for Week 2.

R10.11 Relative reference copying

You are copying cell C2 to cell C3 (i.e., select C2, press Ctrl+C, then select C3, and press Ctrl+V). Cell C2 has a formula =A2+B2. After copying, what will the formula in cell C3 become?

  • =A3+B3
  • =$A$2+$B$2
  • =A2+B2
  • =$A2+$B2

Answer: A) =A3+B3

When you copy the formula =A2+B2 from cell C2 to C3, the formula will update to =A3+B3 because the relative cell references will shift down by one row.

R10.12 Mixed reference copying

Which formula keeps column A fixed when copied across and allows the row number to change when copied down?

  • =$A2
  • =A$2
  • =$A$2
  • =A2

Answer: A) =$A2

The formula =$A2 keeps column A fixed when copied across and allows the row number to change when copied down.

This is a mixed reference with a fixed column and a relative row.

R10.13 Mixed reference copying 1

You enter the formula =B2*$C$1 in cell D2 and drag it down using the fill handle ✚. The multiplier $C$1 must stay fixed, while B2 must update. What will the formula in cell D3 be?

  • =B3*C1
  • =B2*$C$1
  • =B3*$C$1
  • =B3*$C1

Answer: C) =B3*$C$1

When you drag the formula from D2 to D3, B2 changes to B3 and the absolute reference $C$1 stays fixed. The resulting formula is =B3*$C$1.

R10.14 Mixed reference copying 2

You copy the formula =$A2+B$1 from cell C2 to D3. What will the new formula in D3 be?

  • =$A3+C$1
  • =$A2+B$1
  • =$A3+B$1
  • =A2+B$1

Answer: A) =$A3+C$1

When you copy the formula from C2 to D3, $A2 becomes $A3. Column A stays fixed and row 2 changes to row 3. B$1 becomes C$1. Column B changes to column C and row 1 stays fixed.

R10.15 Relative and absolute reference

You want to create a formula that multiplies the values in column A by a constant in cell B1. If you want to copy the formula down without changing the reference to B1, which formula should you use in cell C2?

  • =A2*B1
  • =$A2*B1
  • =A2*$B$1
  • =A2*$B1

Answer: C) =A2*$B$1

By using the formula =A2*$B$1 in cell C2, you ensure that the reference to cell B1 remains fixed when you copy the formula down. A2 is a relative reference, while $B$1 is an absolute reference.

R10.16 Substring extraction 1

You want to extract the first 6 characters from the patient name in cell A2 (“Leslie Fields”) from Table 2. Which formula would you use?

  • =LEFT(A2, 6)
  • =RIGHT(A2, 6)
  • =MID(A2, 6, 6)
  • =TEXT(A2, 6)

Answer: A) =LEFT(A2, 6)

Explanation: The LEFT function extracts the first 6 characters from the string in cell A2, which returns “Leslie”.

R10.17 Substring extraction 2

In cell A3 from Table 2, the patient name is “Arnold Ross”. You want to extract the last 4 characters of the name. Which formula would you use?

  • =LEFT(A3, 4)
  • =RIGHT(A3, 4)
  • =MID(A3, 4, 4)
  • =TEXT(A3, 4)

Answer: B) =RIGHT(A3, 4)

Explanation: The RIGHT function extracts the last 4 characters from the string in cell A3, which returns “Ross”.

R10.18 Concatenating values

You want to append “is male” to the full patient name in A2 in Table 2. Which formula will correctly combine these values?

  • =CONCAT(A2, " is ", "male")
  • =A2 & " is " & B2
  • =CONCAT(A2, " ", "is", " ", B2)
  • All of the above

Answer: D) All of the above

Explanation: All three formulas will write “Leslie Fields is male”.

  • =CONCAT(A2, " is ", "male") will concatenate the patient name with " is " and "male".
  • =A2 & " is " & B2 will concatenate the patient name with " is " and B2, which is "male".
  • =CONCAT(A2, " ", "is", " ", B2) will concatenate the patient name with spaces, the word "is", and the value in B2, which is "male".

R10.19 Finding a space

You want to find the position of the space in the patient name in cell A2 (“Leslie Fields”) in Table 2. Which formula(s) will return the correct position?

  • =SEARCH(" ", A2)
  • =FIND(" ", A2)
  • =LEFT(A2, 1)
  • =MID(A2, 1, 1)
TipFIND and SEARCH Functions
  • FIND(find_text, within_text, [start_num]): Returns the starting position of a substring within a text string.
  • SEARCH(find_text, within_text, [start_num]): Returns the starting position of a substring within a text string (case-insensitive).
  • LEFT(text, [num_chars]): Returns the leftmost characters from a text string.
  • MID(text, start_num, num_chars): Returns a specific number of characters from a text string, starting at a specific position.

Answer: A) =SEARCH(" ", A2) and B) =FIND(" ", A2)

Explanation:

  • The FIND function returns the position of the space character in “Leslie Fields”, which is at position 7.
  • The SEARCH function also returns the position of the space character in “Leslie Fields”, which is at position 7.

R10.20 Substring extraction using spaces

You want to extract the middle word “Banks” from the patient name in cell A5 from Table 2, where the name is “Doyle Banks Junior”. Which of the following formulas would achieve this? (Note: Multiple answers may be correct.)

  • =MID(A5, 7, 5)
  • =MID(A5, FIND(" ", A5) + 1, FIND(" ", A5, FIND(" ", A5) + 1) - FIND(" ", A5) - 1)
  • =MID(A5, 7, 13)
  • All of the above

Answers:

    1. =MID(A5, 7, 5)
    • This formula directly extracts the middle word “Banks” starting from the 7th character and with a length of 5 characters.
    1. =MID(A5, FIND(" ", A5) + 1, FIND(" ", A5, FIND(" ", A5) + 1) - FIND(" ", A5) - 1)
    • This formula uses the FIND function to locate the spaces and extract “Banks” based on the positions of the spaces.
      • The first FIND locates the first space, which is at position 6. Therefore, FIND(" ", A5) + 1 gives the position of the character after the first space (B), which is 7.
      • The second FIND locates the second space.
        • FIND(" ", A5) + 1 gives 7, the position after the first space; this becomes FIND’s start_num argument.
        • FIND(" ", A5, FIND(" ", A5) + 1) = FIND(" ", A5, 7) finds the second space, starting from position 7. This returns 12.
        • FIND(" ", A5) is the position of the first space, which is 6.
        • The difference between the positions of the second and first spaces minus 1 gives the length of the middle word. So, 12 - 6 - 1 = 5.

R10.21 Length of first name

You want to calculate the total number of characters in the patient’s first name in cell A2 (“Leslie Fields”) from Table 2. Which formulas will give the correct result? (Note: Multiple answers may be correct.)

  • =LEN(LEFT(A2, FIND(" ", A2) - 1))
  • =LEN(LEFT(A2, FIND(" ", A2)))
  • =LEN(MID(A2, 1, FIND(" ", A2) - 1))
  • =LEN(A2) - LEN(A2) + FIND(" ", A2)

Answers: A and C.

    1. =LEN(LEFT(A2, FIND(" ", A2) - 1))
    • This formula correctly uses the LEFT function to extract the first name from the text in cell A2 by finding the position of the first space using FIND(" ", A2). The -1 ensures the space itself is excluded, and LEN calculates the length of the extracted first name.
    1. =LEN(LEFT(A2, FIND(" ", A2)))
    • This formula is incorrect because it includes the space after the first name in the result. The FIND function locates the space, but the LEFT function does not subtract the space, so the length includes the space as well.
    1. =LEN(MID(A2, 1, FIND(" ", A2) - 1))
    • This formula works correctly. It uses MID to extract the first name starting from the 1st character (1 as the start position) and up to the first space (FIND(" ", A2) - 1), excluding the space. LEN then calculates the length of the extracted first name.
    1. =LEN(A2) - LEN(A2) + FIND(" ", A2)
    • This formula is incorrect because it simplifies to just FIND(" ", A2), which returns the position of the first space (which is 7 in this case for “Leslie Fields”). This does not represent the length of the first name; it only gives the position of the space.

R10.22 Adding Mr. or Miss based on gender

In sheet patient_data, you have patient names in column A and their genders in column B. Assume every value in column B is either “male” or “female”. You want to add “Mr.” or “Miss” before each patient’s name in column A, depending on their gender. Which formulas can you use in cell C2? (Multiple answers may be correct.)

  • =IF(B2="male", "Mr. " & A2, "Miss " & A2)
  • =IF(B2="female", "Mr. " & A2, "Miss " & A2)
  • =IF(B2="male", "Miss " & A2, "Mr. " & A2)
  • =IF(B2="female", "Miss " & A2, "Mr. " & A2)

Answer: A) =IF(B2="male", "Mr. " & A2, "Miss " & A2) and D) =IF(B2="female", "Miss " & A2, "Mr. " & A2)

Explanation:

  1. The IF function checks if the value in B2 is “male”. If it is, it adds “Mr.” before the patient name in A2. Otherwise, it adds “Miss”.

  2. The IF function checks if the value in B2 is “female”. If it is, it adds “Miss” before the patient name in A2. Otherwise, it adds “Mr.”.

R10.23 Classifying grades

You have a list of student grades in column A, where A2 contains the value 85. You want to classify grades into letter grades as follows:

  • A: 90 and above
  • B: 80 or above and below 90
  • C: 70 or above and below 80
  • D: Below 70

Which formula would you use in cell B2 to assign the correct letter grade based on the numeric grade in A2?

  • =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D")))
  • =IF(A2>=90, "A", IF(A2>=80, "C", IF(A2>=70, "B", "D")))
  • =IF(A2<=90, "A", IF(A2<=80, "B", IF(A2<=70, "C", "D")))
  • =IF(A2=90, "A", IF(A2=80, "B", IF(A2=70, "C", "D")))

Answer: A) =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D")))

Explanation: The formula uses nested IF functions to classify the grade in A2.

  • If A2 is 90 or above, it assigns an “A”.
  • If A2 is 80 or above and below 90, it assigns a “B”.
  • If A2 is 70 or above and below 80, it assigns a “C”.
  • If A2 is below 70, it assigns a “D”.

R10.24 User input with case insensitivity and extra spaces

You have a list of patient names in sheet patient_data presented in Table 2, and the user will input a patient name in a specific cell in Sheet3 (see Table 3). You want to perform a case-insensitive search based on the user’s input and return the exact match from patient_data, regardless of whether the input is in upper case, lower case, mixed case, or has extra ordinary spaces.

Table 3: Sheet3 sheet.
A B
1 User Input Result
2

The user will type a patient name (e.g., “leslie fields”, “Leslie Fields”, or “LESLIE FIELDS”) in cell A2 on Sheet3. The formula in cell B2 should match the name without regard to case or extra spaces, then return the spelling and capitalization stored in column A of patient_data.

For example: If the user types “leslie fields” or “LESLIE FIELDS” into A2, the result in B2 should return “Leslie Fields” from patient_data.

Which formula would you use in cell B2 on Sheet3 to achieve this?

  • =XLOOKUP(A2, patient_data!A2:A5, patient_data!A2:A5, "Not Found", 0)
  • =XLOOKUP(LOWER(A2), LOWER(patient_data!A2:A5), patient_data!A2:A5, "Not Found", 0)
  • =XLOOKUP(TRIM(LOWER(A2)), TRIM(LOWER(patient_data!A2:A5)), patient_data!A2:A5, "Not Found", 0)
  • =XLOOKUP(TRIM(A2), patient_data!A2:A5, LOWER(patient_data!A2:A5), "Not Found", 0)
TipFunctions Used
  • XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode]): Searches a range or an array, and returns an item corresponding to the first match found.
    • lookup_value: The value to search for.
    • lookup_array: The range to search for the value.
    • return_array: The range to return the value from.
    • if_not_found: The value to return if no match is found.
    • match_mode: The type of match to perform (0 for exact match).
  • TRIM(text): Removes extra spaces from text.
    • text: The text from which to remove spaces.
  • LOWER(text): Converts text to lowercase.
    • text: The text to convert to lowercase.
  • UPPER(text): Converts text to uppercase.
    • text: The text to convert to uppercase.

Answer: C) =XLOOKUP(TRIM(LOWER(A2)), TRIM(LOWER(patient_data!A2:A5)), patient_data!A2:A5, "Not Found", 0)

Explanation:

  • TRIM removes ordinary extra spaces from the user’s input and the stored patient names.
  • XLOOKUP uses a case-insensitive exact match by default, so LOWER is redundant here. The unchanged return range preserves the spelling and capitalization stored in patient_data.

R10.25 Splitting full name into first name and surname

You have a list of patient names in patient_data sheet presented in Table 2, column A, where each name consists of a first name and a surname separated by a space. You want to extract the first name into one column and the surname(s) into another.

The formulas must work when copied through all names in A2:A5. For example, they should return the following values for “Leslie Fields” in A2:

  • First Name in C2: Leslie
  • Surname(s) in D2: Fields

Use “Arnold Ross” in A3 as a second check. The copied formulas must return Arnold and Ross.

Which combination of formulas should you use in C2 and D2 to extract the first name and surname from A2 and continue working when copied down?

  • =LEFT(A2, FIND(" ", A2) - 1) for First Name and =RIGHT(A2, LEN(A2) - FIND(" ", A2)) for Surname
  • =RIGHT(A2, FIND(" ", A2) - 1) for First Name and =LEFT(A2, LEN(A2) - FIND(" ", A2)) for Surname
  • =LEFT(A2, LEN(A2) - FIND(" ", A2)) for First Name and =RIGHT(A2, FIND(" ", A2) - 1) for Surname
  • =MID(A2, 1, FIND(" ", A2)) for First Name and =LEFT(A2, FIND(" ", A2)) for Surname
TipFunctions Used
  • LEFT(text, num_chars): Returns the first characters in a text string.
    • text: The text string from which to extract characters.
    • num_chars: The number of characters to extract from the left of the text.
  • RIGHT(text, num_chars): Returns the last characters in a text string.
    • text: The text string from which to extract characters.
    • num_chars: The number of characters to extract from the right of the text.
  • FIND(find_text, within_text): Returns the starting position of one text string within another.
    • find_text: The text you want to find.
    • within_text: The text in which you want to search for find_text.
  • LEN(text): Returns the number of characters in a text string.
    • text: The text string for which you want to calculate the length.
  • MID(text, start_num, num_chars): Returns a specific number of characters from a text string, starting at the position you specify.
    • text: The text string containing the characters you want to extract.
    • start_num: The position of the first character you want to extract.
    • num_chars: The number of characters you want to extract.

Answer: A)

Explanation:

  • First Name (C2): =LEFT(A2, FIND(" ", A2) - 1)
    • This formula extracts the first name by taking the characters from the left up to the first space.
  • Surname (D2): =RIGHT(A2, LEN(A2) - FIND(" ", A2))
    • This formula extracts the surname by taking the characters from the right, starting just after the first space.

R10.26 Using constants in formulas

You are calculating the total cost of products based on their price and the number of units sold. The prices of the products are listed in column A, the number of units sold is listed in column B, and you want to multiply the price by the units sold in column C. You also want to apply a fixed discount of 10% from cell D2 to all the totals. Your task is to write a formula that you can drag down to calculate the total cost for each product after the discount is applied.

Inspect the price, quantity, and fixed discount cells in Table 4 before choosing a formula.

Table 4: Data of products and units sold.
A B C D
1 Price Units Sold Total Cost Discount
2 10.00 5 10%
3 15.00 3
4 12.00 4
5 8.00 6

You need to calculate the total cost for each row using the formula:

\[ \text{Total Cost} = (\text{Price} \times \text{Units Sold}) \times (1 - \text{Discount}) \]

Which formulas can you use in C2 and drag down to calculate the total cost for each product, applying the fixed discount from D2? (Multiple answers may be correct.)

  • =A2 * B2 * (1 - D2)
  • =A2 * B2 * (1 - $D$2)
  • =A$2 * B$2 * (1 - D$2)
  • =$A2 * $B2 * (1 - D$2)

Answers: B) =A2 * B2 * (1 - $D$2) and D) =$A2 * $B2 * (1 - D$2)

Explanation:

  • Both formulas multiply the price by the number of units sold and keep the reference to the discount in row 2 fixed while the formula is copied down.
  • Formula B uses relative references for the price and units and an absolute reference for the discount.
  • Formula D uses mixed references. The dollar signs before columns A and B do not interfere with copying down, while D$2 keeps the discount in row 2.

R10.27 Using AutoFill with a gap in the data

On an otherwise empty worksheet, you have the following ordinary range in column A, where there is a gap in row 5. You want to auto-fill a sequence of numbers starting from 1 in cell B2. You enter 1 in cell B2 and 2 in cell B3. You then select both cells and double-click the fill handle to auto-fill down the column.

Inspect the gap in column A of Table 5 before predicting how far AutoFill will continue.

Table 5: Initial Data.
A B
1 Data Sequence
2 Alice Johnson 1
3 Bob Smith 2
4 Charlie Rogers
5
6 Diana Stone

What values will be filled in cells B4 to B6 when you double-click the fill handle?

  1. 1, 1, 1
  2. 3, 4, 5
  3. 1, 2, 1
  4. 3, blank, blank
TipAutoFill in Excel

AutoFill: A feature in Excel that allows you to quickly fill cells with a series of values or data based on the detected pattern.

When you point to the fill handle (a small square at the bottom-right corner of a cell), the cursor changes to a black cross. Double-clicking the fill handle will auto-fill the cells based on the pattern detected.

Solution: D) 3, blank, blank

Explanation: Excel’s AutoFill feature fills cells based on the detected pattern. Since there’s data in A2:A4, the sequence will continue from B2 (1), B3 (2), to B4 (3). However, the fill stops at row 4 because row 5 in column A is blank, and Excel recognizes the end of the data.