How to Use FILTER, UNIQUE, and SEQUENCE in Excel?
Excel’s modern dynamic array functions — FILTER, UNIQUE, and SEQUENCE — simplify data handling by automatically spilling results into multiple cells. Let’s explore how to use them effectively.
FILTER — Extract Specific Data
The FILTER function extracts data that meets specific conditions.
=FILTER(A2:C20, C2:C20="North")This will return only the rows where the region is “North”.
UNIQUE — Remove Duplicates
The UNIQUE function generates a list of distinct values.
=UNIQUE(B2:B100)This formula lists all unique entries from column B.
SEQUENCE — Generate Numbers Automatically
The SEQUENCE function creates a range of sequential numbers.
=SEQUENCE(10,1,1,1)This produces numbers 1 to 10 in a column.
Combining FILTER, UNIQUE, and SEQUENCE
You can combine these formulas for advanced automation:
=SORT(UNIQUE(FILTER(A2:A100, B2:B100="Completed")))This returns a sorted list of unique items from column A where column B = “Completed”.
Real-World Examples
Sales Analysis with FILTER
=FILTER(A2:D100, B2:B100="North")Quickly view sales only for the “North” region without scrolling through large datasets.
Removing Duplicate Emails with UNIQUE
=UNIQUE(A2:A1000)Generate a clean mailing list without duplicates for email campaigns.
Creating a Dynamic Calendar with SEQUENCE
=SEQUENCE(31,1,DATE(2025,1,1),1)Generate all January 2025 dates automatically — perfect for attendance or project timelines.
Advanced Tips for Power Users
- Combine FILTER with SORT:
=SORT(FILTER(A2:C100, C2:C100>500)) - Dynamic Reporting with UNIQUE + COUNTIF:
=COUNTIF(A2:A100, UNIQUE(A2:A100)) - Generate Sequential IDs Automatically:
=SEQUENCE(COUNTA(A2:A100))
Common Mistakes to Avoid
=IFERROR(FILTER(A2:C100, B2:B100="East"), "No results found")=UNIQUE(FILTER(A2:A100, A2:A100<>""))=SEQUENCE(10,1,1,2) generates only odd numbers.Why Use These Functions?
- Efficiency: Work with large datasets instantly.
- Automation: Results update dynamically with source data.
- Simplicity: Reduce formula complexity while improving accuracy.
Conclusion: Simplify Formulas with TextToFormula
Learning formulas like FILTER, UNIQUE, and SEQUENCE can save hours of work and make your spreadsheets smarter. But remembering the syntax isn’t always easy.
TextToFormula is here to help — just type what you need (e.g., “list all sales above 500 for East region”) and get the exact formula instantly.
👉 Try TextToFormula Now — it’s free, simple, and saves you from formula headaches.
Frequently Asked Questions (FAQs):
-
Does FILTER work in all versions of Excel?
No, the FILTER function is only available in Excel 365 and Excel 2021. This is because it belongs to the family of Dynamic Array functions, which were introduced in these newer versions of Excel. Older versions like Excel 2016, 2013, or 2010 do not support FILTER. If you are using an older version, you’ll need to use alternatives such as Advanced Filter, AutoFilter, or complex formulas with INDEX, MATCH, and IF statements to achieve similar results. So, if you want to use FILTER seamlessly, it’s recommended to upgrade to Excel 365 for continuous feature updates.
-
Can I use UNIQUE in Google Sheets?
Yes, the UNIQUE function works perfectly in Google Sheets, and it has been available there for a long time-even before Excel introduced it. UNIQUE in Google Sheets lets you quickly remove duplicates from a dataset or extract a list of distinct values from a range. For example, if you have a long list of customer names with repeats, UNIQUE will instantly return a clean list of unique customers. This is very useful for data cleaning and analysis in Sheets. Since Google Sheets is cloud-based, you don’t need to worry about version issues like in Excel—it’s available to everyone by default.
-
How is SEQUENCE different from ROW()?
At first glance, both SEQUENCE and ROW() can generate numbers, but they are quite different in purpose and flexibility:
-
ROW() simply returns the row number of a given cell.
For example,
=ROW(A5)will return 5, because cell A5 is in the 5th row. -
SEQUENCE, on the other hand, is a powerful
Dynamic Array function that can generate an entire series of numbers
in rows and/or columns. For example,
=SEQUENCE(10)will instantly generate numbers 1 to 10 in a vertical list.
You can even control the number of rows, columns, starting point, and step size. For example,
=SEQUENCE(5, 2, 10, 2)will create a 5x2 grid starting from 10 and increasing by 2.So, while ROW() is static and limited, SEQUENCE is dynamic and versatile, especially when building automated lists or generating arrays for advanced formulas.
-
ROW() simply returns the row number of a given cell.
For example,