Filtering data with AutoFilter in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the visible row count after applying this AutoFilter?

You have a table with 10 rows of sales data in columns A to D. You apply an AutoFilter on column C to show only rows where the value is greater than 100. Which option shows the correct count of visible rows after filtering?

Excel

Data in column C: 50, 120, 90, 130, 110, 80, 140, 70, 150, 60

A 4

B 6

C 5

D 7

💡 Hint

Count how many values in column C are greater than 100.

Only the values 120, 130, 110, 140, and 150 are greater than 100, so 5 rows remain visible after filtering.

❓ Function Choice

intermediate

Which function helps count visible rows after filtering?

After applying an AutoFilter on a data range, you want to count how many rows are visible (not hidden). Which Excel function should you use?

A COUNTA(range)

B COUNT(range)

C SUM(range)

D SUBTOTAL(3, range)

💡 Hint

Look for a function that ignores hidden rows.

SUBTOTAL with function number 3 counts visible non-empty cells, ignoring filtered-out rows.

🎯 Scenario

advanced

Filter data to show only sales between $100 and $200

You have a sales table with amounts in column B. You want to use AutoFilter to show only rows where sales are between $100 and $200 inclusive. Which filter criteria should you apply?

A Filter column B with 'Greater Than or Equal To' 100 and 'Less Than or Equal To' 200

B Filter column B with 'Equals' 100 and 'Equals' 200

C Filter column B with 'Less Than' 100 or 'Greater Than' 200

D Filter column B with 'Does Not Equal' 100 and 'Does Not Equal' 200

💡 Hint

Think about how to include all values from 100 up to 200.

Using 'Greater Than or Equal To' 100 and 'Less Than or Equal To' 200 filters the range inclusively between those values.

❓ data_analysis

advanced

Identify the error in this AutoFilter setup

You want to filter a list of products to show only those starting with 'A'. You apply AutoFilter with the criteria 'Begins With' set to 'a'. However, no rows appear after filtering. What is the most likely reason?

A The filter is case-sensitive and 'a' does not match 'A'

B The filter was applied to the wrong column

C The data contains no products starting with 'A'

D AutoFilter does not support 'Begins With' criteria

💡 Hint

Check if the filter was applied to the correct column.

If the filter is applied to a column without product names, no rows will match the criteria.

🧠 Conceptual

expert

What happens to formulas referencing filtered rows?

You have a table with formulas summing values in column D. After applying an AutoFilter that hides some rows, what will the formula =SUM(D2:D20) show?

A Sum of all values in D2:D20, including hidden rows

B Sum of only visible rows in D2:D20

C Sum of only hidden rows in D2:D20

D An error because some rows are hidden

💡 Hint

Regular SUM does not ignore hidden rows.

The SUM function adds all values regardless of filtering. To sum only visible rows, use SUBTOTAL with function number 9.