AutoFill and Flash Fill in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

AutoFill Series Output

You enter the number 2 in cell A1 and 4 in cell A2. Then you select both cells and drag the fill handle down to cell A5 using AutoFill. What will be the value in cell A5?

A 10

B 2

C 6

D 8

💡 Hint

AutoFill detects the pattern between the first two cells.

AutoFill sees the difference between 2 and 4 is +2, so it continues adding 2 each step: 2, 4, 6, 8, 10. Cell A5 is the 5th value, which is 10.

🎯 Scenario

intermediate

Using Flash Fill to Extract Initials

You have full names in column A (e.g., "John Doe") starting from A2. You want to use Flash Fill in column B to get initials (e.g., "JD"). What should you enter in B2 before using Flash Fill?

A John D

B J D

C JohnDoe

D JD

💡 Hint

Flash Fill learns from the example you provide.

To get initials, you must type the exact desired output in B2, which is "JD" for "John Doe". Flash Fill then fills the rest automatically.

📊 Formula Result

advanced

AutoFill Date Series Behavior

You enter the date 01/01/2024 in cell A1 and 01/03/2024 in cell A2. You select both cells and drag the fill handle down to A5. What date will appear in cell A5?

A 01/09/2024

B 01/11/2024

C 01/07/2024

D 01/05/2024

💡 Hint

Look at the difference in days between the first two dates.

The difference between 01/01/2024 and 01/03/2024 is 2 days. AutoFill continues adding 2 days each step: 01/01, 01/03, 01/05, 01/07, 01/09. So A5 is 01/09/2024.

❓ Function Choice

advanced

Best Method to Separate First Names Using Flash Fill

You have full names in column A (e.g., "Alice Johnson") and want to fill column B with only the first names using Flash Fill. Which of the following steps is correct?

A Type the full name again in B2, then drag the fill handle down.

B Type the first name in B2, then press Ctrl+E to apply Flash Fill.

C Use the formula =LEFT(A2, FIND(" ", A2)-1) in B2 and drag down.

D Copy column A to B and then delete last names manually.

💡 Hint

Flash Fill shortcut is Ctrl+E after typing an example.

Typing the first name example and pressing Ctrl+E triggers Flash Fill to extract first names automatically.

❓ data_analysis

expert

Analyzing AutoFill Behavior with Mixed Data

You have the following data in column A: A1=100, A2=200, A3=Text, A4=400. You select A1:A4 and drag the fill handle down to A8. What will be the value in cell A8?

A 700

B 600

C Text

D Error

💡 Hint

AutoFill tries to detect a pattern but stops when data types differ.

Because of the text in A3, AutoFill treats the series as text and repeats the pattern. The value in A8 will be "Text" repeating the pattern.

Practice

1. What does the AutoFill feature in Excel do when you drag the fill handle down a column?

easy

A. It opens a new worksheet.

B. It deletes the selected cells automatically.

C. It formats the cells with colors and borders.

D. It copies or continues a pattern based on the selected cells.

Solution

1. Step 1: Understand AutoFill behavior and analyze options

   AutoFill copies or continues a pattern when dragging the fill handle. Only 'It copies or continues a pattern based on the selected cells.' describes this behavior correctly; others describe unrelated actions.

2. Final Answer:

   It copies or continues a pattern based on the selected cells. -> Option D

3. Quick Check:

   AutoFill = copy or continue pattern [OK]

Hint: Drag fill handle to copy or continue patterns fast [OK]

Common Mistakes:

• Thinking AutoFill deletes cells
• Confusing AutoFill with formatting tools
• Assuming AutoFill opens new sheets

2. Which keyboard shortcut triggers Flash Fill in Excel to automatically fill data based on a pattern?

easy

A. Ctrl + E

B. Ctrl + C

C. Ctrl + V

D. Ctrl + F

Solution

1. Step 1: Recall Flash Fill shortcut and verify options

   Flash Fill is triggered by pressing Ctrl + E in Excel. Ctrl + C copies, Ctrl + V pastes, Ctrl + F finds text, so only Ctrl + E fits Flash Fill.

2. Final Answer:

   Ctrl + E -> Option A

3. Quick Check:

   Flash Fill shortcut = Ctrl + E [OK]

Hint: Use Ctrl+E to auto-fill based on pattern quickly [OK]

Common Mistakes:

• Confusing Ctrl+E with copy or paste shortcuts
• Trying Ctrl+F which is find, not fill
• Not knowing Flash Fill shortcut

3. You have a column with full names like "John Smith" in column A. You type "John" in B1 and "Smith" in C1. You want to fill columns B and C for all rows using Flash Fill. What will happen if you select B2 and press Ctrl+E?

medium

A. Column B will fill with last names instead of first names.

B. Column B will remain empty because Flash Fill works only on numbers.

C. Column B will fill with first names extracted from column A.

D. Column B will copy the value from B1 to all selected cells.

Solution

1. Step 1: Understand and apply Flash Fill behavior

   Flash Fill detects patterns like extracting first names from full names. Typing "John" in B1 shows first name pattern from column A; pressing Ctrl+E in B2 fills first names down column B.

2. Final Answer:

   Column B will fill with first names extracted from column A. -> Option C

3. Quick Check:

   Flash Fill extracts patterns from text [OK]

Hint: Type example, select next cell, press Ctrl+E to fill pattern [OK]

Common Mistakes:

• Thinking Flash Fill works only on numbers
• Expecting Flash Fill to copy exact cell values
• Confusing first and last name extraction

4. You tried to use Flash Fill to separate dates in column A (like "2024-06-15") into day, month, and year in columns B, C, and D. But after pressing Ctrl+E in column B, nothing happens. What is the most likely reason?

medium

A. You need to use AutoFill instead of Flash Fill for dates.

B. You did not provide an example pattern in the first cell of column B.

C. Flash Fill only works with text, not dates.

D. Flash Fill requires the entire column to be selected before pressing Ctrl+E.

Solution

1. Step 1: Check Flash Fill requirements and identify issue

   Flash Fill needs an example typed in the first cell (B1) to detect the pattern. Without it, Flash Fill cannot guess what to fill, so nothing happens.

2. Final Answer:

   You did not provide an example pattern in the first cell of column B. -> Option B

3. Quick Check:

   Flash Fill needs example input first [OK]

Hint: Always type example before Ctrl+E for Flash Fill [OK]

Common Mistakes:

• Assuming Flash Fill works without example input
• Thinking Flash Fill can't handle dates
• Trying to select whole column before Flash Fill

5. You have a list of email addresses in column A like "[e-mail]". You want to use Flash Fill to extract just the username part (before the @) into column B. You type "jane.doe" in B1 and press Ctrl+E in B2, but Flash Fill fills the entire column with "jane.doe" instead of extracting usernames. What should you do to fix this?

hard

A. Type the correct username for B2 manually, then press Ctrl+E again.

B. Use AutoFill by dragging the fill handle instead of Flash Fill.

C. Format column B as text before using Flash Fill.

D. Select the entire column B before pressing Ctrl+E.

Solution

1. Step 1: Understand Flash Fill pattern learning and how to fix

   Flash Fill learns patterns from examples in consecutive cells. Typing only B1 causes it to copy that value; type correct username in B2 manually, then press Ctrl+E to learn the extraction pattern and fill correctly.

2. Final Answer:

   Type the correct username for B2 manually, then press Ctrl+E again. -> Option A

3. Quick Check:

   Flash Fill needs multiple examples to learn pattern [OK]

Hint: Give Flash Fill 2+ examples to learn pattern well [OK]

Common Mistakes:

• Using AutoFill instead of Flash Fill for pattern extraction
• Not typing second example to guide Flash Fill
• Thinking formatting affects Flash Fill behavior