Filtering data with AutoFilter in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to apply AutoFilter to the data range A1:D10.

Excel

ActiveSheet.Range("A1:D10").[1]

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

ASelect

BFilter

CSort

DAutoFilter

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Filter' instead of 'AutoFilter' causes an error.

Using 'Sort' does not apply filtering.

The AutoFilter method applies filtering to the specified range.

2fill in blank

medium

Complete the code to filter the first column for cells equal to "Apple".

Excel

ActiveSheet.Range("A1:D10").AutoFilter Field:=1, Criteria1:=[1]

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

A'Apple'

BApple

C"Apple"

D="Apple"

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the criteria causes a syntax error.

Using single quotes instead of double quotes is incorrect.

The criteria must be a string enclosed in double quotes.

3fill in blank

hard

Fix the error in the code to filter column 2 for values greater than 100.

Excel

ActiveSheet.Range("A1:D10").AutoFilter Field:=2, Criteria1:=[1]

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

A>100

B">100"

C"100"

D100

Attempts:

3 left

💡 Hint

Common Mistakes

Using >100 without quotes causes an error.

Using just 100 filters for equal to 100, not greater than.

The criteria must be a string with the operator and number inside quotes.

4fill in blank

hard

Fill both blanks to filter column 3 for values between 50 and 200.

Excel

ActiveSheet.Range("A1:D10").AutoFilter Field:=3, Criteria1:=[1], Criteria2:="<=200", Operator:=[2]

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

A">=50"

BxlAnd

CxlOr

D"<=200"

Attempts:

3 left

💡 Hint

Common Mistakes

Using xlOr instead of xlAnd combines criteria incorrectly.

Not quoting the criteria strings causes errors.

Use Criteria1 for the lower bound and Operator xlAnd to combine with Criteria2.

5fill in blank

hard

Fill both blanks to filter column 3 for values between 50 and 200 using Criteria1 and Criteria2.

Excel

ActiveSheet.Range("A1:D10").AutoFilter Field:=3, Criteria1:=[1], Operator:=xlAnd, Criteria2:=[2]

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

A">=50"

B"<=200"

CxlAnd

D"50"

Attempts:

3 left

💡 Hint

Common Mistakes

Using numeric values without quotes causes errors.

Mixing up Criteria1 and Criteria2 values.

Criteria1 is the lower bound >=50, Criteria2 is the upper bound <=200, Operator xlAnd combines them.

Practice

1. What does the AutoFilter feature in Excel primarily do?

easy

A. It hides rows that don't match your selected criteria.

B. It deletes rows that don't match your selected criteria.

C. It changes the data in the cells to match your criteria.

D. It copies filtered data to a new sheet automatically.

Solution

1. Step 1: Understand AutoFilter purpose

   AutoFilter is used to hide rows that don't meet the filter criteria, not delete or change data.

2. Step 2: Compare options with AutoFilter behavior

   Only hiding rows matches what AutoFilter does; deleting or copying data is not automatic.

3. Final Answer:

   It hides rows that don't match your selected criteria. -> Option A

4. Quick Check:

   AutoFilter hides rows = A [OK]

Hint: AutoFilter hides, it never deletes data [OK]

Common Mistakes:

• Thinking AutoFilter deletes rows
• Confusing filtering with copying data
• Assuming AutoFilter changes cell values

2. Which of the following is the correct way to apply AutoFilter in Excel?

easy

A. Right-click any cell and choose Delete Filter.

B. Select your data range, then go to Data tab and click Filter.

C. Type =FILTER() in a cell to activate AutoFilter.

D. Use the Home tab and click Sort to apply AutoFilter.

Solution

1. Step 1: Recall how to activate AutoFilter

   AutoFilter is applied by selecting data and clicking Filter under the Data tab.

2. Step 2: Check each option's correctness

   Only Select your data range, then go to Data tab and click Filter. correctly describes the steps; others describe unrelated actions or functions.

3. Final Answer:

   Select your data range, then go to Data tab and click Filter. -> Option B

4. Quick Check:

   Data tab > Filter = B [OK]

Hint: Filter button is under Data tab, not Home [OK]

Common Mistakes:

• Confusing FILTER function with AutoFilter
• Looking for filter options in Home tab
• Trying to delete filter instead of applying

3. You have a table with a column "Status" containing values: "Complete", "Pending", "In Progress". After applying AutoFilter and selecting only "Pending", what will you see?

medium

A. The entire table will be copied to a new sheet.

B. All rows will be visible but "Pending" rows highlighted.

C. Rows with "Complete" and "In Progress" will be deleted.

D. Only rows where Status is "Pending" will be visible.

Solution

1. Step 1: Understand filtering by one value

   Selecting "Pending" in AutoFilter shows only rows matching "Pending" and hides others.

2. Step 2: Eliminate incorrect options

   Highlighting or deleting rows or copying table does not happen automatically with AutoFilter.

3. Final Answer:

   Only rows where Status is "Pending" will be visible. -> Option D

4. Quick Check:

   Filter shows matching rows only = D [OK]

Hint: Filter shows matching rows, hides others [OK]

Common Mistakes:

• Thinking filtered rows get deleted
• Expecting highlighting instead of hiding
• Assuming filter copies data automatically

4. You applied AutoFilter but the dropdown arrows do not appear on your header row. What is the most likely reason?

medium

A. You did not select the header row before applying the filter.

B. Your data contains empty rows inside the range.

C. AutoFilter only works on tables, not ranges.

D. You need to restart Excel to activate AutoFilter.

Solution

1. Step 1: Check selection before applying AutoFilter

   AutoFilter dropdowns appear on the selected header row; missing selection causes no arrows.

2. Step 2: Evaluate other options

   Empty rows inside data or restarting Excel do not prevent dropdown arrows; AutoFilter works on ranges too.

3. Final Answer:

   You did not select the header row before applying the filter. -> Option A

4. Quick Check:

   Select header row first = C [OK]

Hint: Always select header row before applying filter [OK]

Common Mistakes:

• Assuming AutoFilter needs tables only
• Thinking Excel restart fixes filter issues
• Ignoring selection step before filtering

5. You have a sales table with columns: Date, Region, Sales. You want to see only sales from "East" region in January 2024. How do you apply AutoFilter correctly?

hard

A. Use the FILTER function in a new sheet with criteria Region="East" and Date in January.

B. Sort the table by Region then manually delete rows not from "East" or January.

C. Apply filter on Region column selecting "East" and on Date column selecting dates from 01/01/2024 to 01/31/2024.

D. Apply filter only on Date column for January; Region filter is not needed.

Solution

1. Step 1: Apply multiple filters to narrow data

   Use AutoFilter dropdowns on both Region and Date columns to select "East" and January dates.

2. Step 2: Eliminate incorrect methods

   Sorting and deleting is manual and risky; FILTER function is different; filtering only Date misses Region filter.

3. Final Answer:

   Apply filter on Region column selecting "East" and on Date column selecting dates from 01/01/2024 to 01/31/2024. -> Option C

4. Quick Check:

   Filter both columns for exact data = A [OK]

Hint: Filter all needed columns to get precise results [OK]

Common Mistakes:

• Filtering only one column when multiple needed
• Deleting rows instead of filtering
• Confusing FILTER function with AutoFilter