Suppose you’re managing a list of support tickets in Excel. Some tickets are still open, others have been closed, and you want to identify the ones that have been waiting for at least three days.
There are two straightforward ways to do this.
You can use the FILTER function to create a separate list of matching tickets. Or you can add a helper column that shows whether each original row meets your conditions.
Both approaches work, but they answer slightly different questions.
FILTER is useful when you want a short list of results. Helper columns are useful when you need to understand why particular records were included or excluded.
Let’s compare them using the same sample data.
Start With a Small Ticket List
Open a blank Excel worksheet and enter the following data in cells A1.
| Row | A: Ticket | B: Status | C: Age (days) |
|---|---|---|---|
| 2 | T-11 | Open | 5 |
| 3 | T-12 | Closed | 7 |
| 4 | T-13 | Open | 1 |
| 5 | T-14 | Open | 3 |
For this example, a ticket should be selected only when it meets both conditions:
- Its status is Open.
- Its age is at least 3 days.
Looking at the data, two tickets qualify: T-11 and T-14.
T-12 is old enough but already closed. T-13 is open but hasn’t reached three days.
We’ll use these known results to check both methods.
Option 1: Create a Live List With FILTER
Excel’s FILTER function returns records that meet the conditions you provide.
Instead of manually hiding rows, it creates a separate result that updates when the source data changes.
Enter this formula in cell E2:
=FILTER(A2:C5,(B2:B5="Open")*(C2:C5>=3),"No tickets")
The expected output is:
| Ticket | Status | Age |
|---|---|---|
| T-11 | Open | 5 |
| T-14 | Open | 3 |
The results spill automatically from E2 into the neighboring cells.
You don’t need to copy the formula down for each ticket.
How the FILTER Formula Works
Let’s examine the individual parts.
A2:C5
This is the range containing the information we want to return.
B2:B5="Open"
This checks which tickets have an Open status.
C2:C5>=3
This checks which tickets are at least three days old.
The multiplication symbol * combines these conditions using AND logic.
For a row to appear in the final output, both tests must be TRUE.
The final argument, "No tickets", tells Excel what to display when no records meet the conditions.
What Happens When the Data Changes?
Suppose ticket T-13 changes from 1 day old to 4 days old.
It now meets both conditions.
When Excel recalculates, the FILTER output includes T-13 automatically.
This makes FILTER useful for worksheets where the source information changes regularly.
It’s particularly convenient for dashboards, exception reports, and filtered lists that other people need to review.
Option 2: Use a Helper Column to Explain Each Row
A FILTER result shows the records that qualify, but it doesn’t display all the excluded records alongside their decisions.
Sometimes that’s exactly what a reviewer needs to see.
In those situations, a helper column is often more practical.
Add a heading called Selected in cell D1.
Then enter this formula in D2:
=AND(B2="Open",C2>=3)
Copy the formula down through D5.
Your table should now look like this:
| Ticket | Status | Age | Selected |
|---|---|---|---|
| T-11 | Open | 5 | TRUE |
| T-12 | Closed | 7 | FALSE |
| T-13 | Open | 1 | FALSE |
| T-14 | Open | 3 | TRUE |
The AND function returns TRUE only when both conditions are satisfied.
This means you can immediately see which tickets qualify without removing the other records from view.
Note: Depending on your Excel regional settings, function arguments may use semicolons instead of commas. In that case, use =AND(B2="Open";C2>=3).
Why a Helper Column Can Be Easier to Review
Imagine someone asks why ticket T-12 wasn’t included in the report.
With a helper column, you can check its original row and see that the ticket is Closed.
You can also filter the Selected column to show only TRUE values.
The same TRUE/FALSE flag can be reused in other calculations, including COUNTIF, or as a field in a PivotTable.
If several reports use the same selection rule, keeping that rule in a clearly labeled helper column can make the workbook easier to maintain.
Make Complex Rules Easier to Understand
A single TRUE/FALSE column works well when the conditions are simple.
As the rules become more complicated, however, a FALSE result doesn’t always explain what went wrong.
For example, you might add two separate helper columns:
| Ticket | Status check | Age check | Selected |
|---|---|---|---|
| T-11 | TRUE | TRUE | TRUE |
| T-12 | FALSE | TRUE | FALSE |
| T-13 | TRUE | FALSE | FALSE |
| T-14 | TRUE | TRUE | TRUE |
Use this formula for the status check:
=B2="Open"
Use this formula for the age check:
=C2>=3
Then combine those results with AND.
This approach makes it clear whether a ticket failed the status rule, the age rule, or both.
It takes a little more space, but it can be useful when a team needs to justify reporting decisions.
FILTER vs Helper Columns: Key Differences
Both methods can identify the same records. The better choice depends on what you’re trying to produce.
| Reporting need | Better starting point |
|---|---|
| Automatically updated list of matching records | FILTER |
| Older Excel version without dynamic arrays | Helper column |
| Selection rule reused across several reports | Helper column |
| Reviewer needs to understand excluded records | Separate helper checks |
| Compact dashboard output | FILTER |
| Manual filtering within the source table | Helper column |
For a straightforward list of open tickets, FILTER may be the quickest solution.
For an operational workbook where people regularly investigate individual records, helper columns may provide more useful context.
You can also use both methods together: helper columns to document the rules and FILTER to display the selected rows.
Excel Version and Table Limitations
Before choosing FILTER, check which version of Excel you’re using.
FILTER is supported in Excel for Microsoft 365, Excel 2021, and Excel 2024.
Older versions without dynamic array support won’t be able to use the same approach.
There’s also an important difference between an Excel Table and a FILTER output.
You can use a Table as the source for FILTER, but a spilling FILTER formula needs to be placed outside the Table.
The result requires enough empty cells to expand.
If you put another value in its spill area, Excel may display a #SPILL! error.
What About Growing Source Data?
Our example uses the fixed range A2.
If you add a new ticket in row 6, that range won’t automatically include it.
For an expanding ticket log, convert the source range into an Excel Table and use structured references.
For example, if your table is named Tickets, the formula can be written as:
=FILTER(Tickets,(Tickets[Status]="Open")*(Tickets[Age]>=3),"No tickets")
The formula will include new rows added to the Table, provided its columns retain the expected names and data types.
Common Problems to Check
Even a correct formula can produce unexpected results when the source data contains errors.
1. FILTER Shows #SPILL!
Check whether another value is blocking the destination area.
Move the formula to an empty location or clear the cells preventing the output from expanding.
2. No Tickets Are Returned
Confirm that at least one source row meets every condition.
The "No tickets" argument is displayed when the selection contains no matching records.
It doesn’t automatically handle all other formula errors.
3. Age Values Are Stored as Text
The age column should contain valid numbers.
If the source contains text values, blanks, or errors, validate them before relying on comparisons such as >=3.
A formula that appears correct can still give misleading results when the underlying values aren’t consistent.
4. Status Labels Are Inconsistent
Check whether the source uses different labels such as Open, Pending, or In Progress.
Decide which statuses qualify before adjusting the selection rule.
5. Excel and Google Sheets Use Different FILTER Syntax
Google Sheets also has a FILTER function, but its argument structure differs from Excel’s.
In particular, Excel supports an if_empty argument, while Google Sheets does not use that argument in the same way.
If you move a formula between applications, review the syntax rather than assuming it will work unchanged.
How to Check Your Results
Before using either method in a real report, run a few simple tests.
- Confirm that T-11 and T-14 are selected from the original dataset.
- Verify that T-12 is excluded because it is Closed.
- Verify that T-13 is excluded because its age is less than 3 days.
- Change the age of T-13 to 4 and confirm that it becomes selected.
- Change T-11 to Closed and confirm that it is removed from the qualifying results.
These tests show whether the rule behaves as expected when the source values change.
For larger worksheets, it’s also worth checking that the source range includes all current records.
Final Thoughts
FILTER and helper columns aren’t competing solutions. They simply make different parts of a reporting task easier.
FILTER gives you a compact, automatically updated list of records that meet your conditions.
Helper columns keep the original rows visible and show which ones passed or failed the selection rule.
If someone only needs the current list of qualifying tickets, FILTER is a sensible option. If they need to investigate exclusions or reuse the same decision across reports, helper columns can be more useful.
Choose the approach that makes the results easiest to verify, not just the one with the shortest formula.
For further details, Microsoft’s official Excel documentation explains the FILTER and AND functions and their supported behavior.
The ticket data in this example is fictional. Test the formulas in your spreadsheet version and regional settings before using them in an operational report.