How to Filter Rows by Status in Excel?
Filter Active Rows is a Excel function that filters a range to return only the rows where the status column equals 'active'.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Filtering data by specific criteria can streamline your analysis. Use dynamic arrays in Excel 365 to easily return only active rows.
The Formula
"Return only rows from a table where a status column equals 'Active', using Excel 365 dynamic arrays"
=FILTER(A2:C100, B2:B100 = "Active")This formula filters a range to return only the rows where the status column equals 'Active'.
Step-by-Step Breakdown
- FILTER function specifies the range to filter.
- A2:C100 is the range of data to return.
- B2:B100 is the column containing the status values.
- "Active" is the criteria for filtering.
Edge Cases & Warnings
- No rows match the criteria, resulting in a #CALC! error.
- The status column contains mixed case values, which may lead to unexpected results.
- Blank cells in the status column may affect the output.
- Using a dynamic range that exceeds the actual data range can lead to incorrect filtering.
Examples
"Table with statuses: Active, Inactive, Active"
Returns only the rows with 'Active' status."Table with mixed statuses: Active, Pending, Active"
Returns only the rows with 'Active' status.Frequently Asked Questions
Can I filter by multiple criteria?
Yes, you can use logical operators to combine multiple conditions.
What happens if there are no active rows?
The formula will return a #CALC! error if no rows match.
Is this formula compatible with older versions of Excel?
No, this formula requires Excel 365 or later due to dynamic array support.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.