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.