- You are here:
- Home »
- Uncategorized »
- Attention Excel Users and Business Owners: Allow Me to Introduce the FILTER() Function
Attention Excel Users and Business Owners: Allow Me to Introduce the FILTER() Function
Now, I know what you may be thinking – why use a function like FILTER() when pivot tables have a filter feature in them already?
The spreadsheets for this tutorial are on my GitHub repository.
That’s a fair question. But what if FILTER() gave you more flexibility? For one, the filter function (going forward, writing without the caps and parens to reduce awkwardness) works on non-aggregated data. That’s probably the biggest reason to at least know the function exists and when it could be used.
Want to see a video of this process?
Along these lines, isn’t it better to know about alternative functionality? You may still choose not to use these alternatives, but at least they are available to you if you need them.
Some Basics on the filter function:
If your data range is small enough, I suggest turning it into a table and naming it something memorable and relevant (e.g., Sales, etc.). When you do this, the filter table’s parameters are easier to manage. You can work with ranges, too, which may be necessary as your data grows. But if you are working with relatively small datasets, go for the table. You’ll thank me later.
Example: Sales Table
Since we already mentioned sales, why not use that as an example? The following dataset contains sales data:
Related: (Sponsored) Do you need some help learning about Excel and Analytics? Learn the correct methods now!
There is a lot here we could filter on. The following are good candidates:
- Region
- Customer
- Product
- Category
- Sales Rep (Rep)
FILTER() allows for multiple conditions, too! So you could filter by Electronics in the West. And you can filter by West and East, and so on.
If you decide to convert the above range to a table (recommended), anywhere within the range, press <Ctrl> T (on Mac, <Command> T). When you do, your range will transform into something that looks like the following:
It’s the same data, just in a different format.
Rename the table (it will default to something like Table1) to Sales by clicking on the Table Design menu option:
Note: It’s not required to change the name. But when you work with several tables that are all named something like Table1, Table2, etc., it can be difficult to know which table you are working with. It takes so little effort to change the name and saves you several headaches later.
Now that the table is ready to go and properly named, click on the Analysis tab/worksheet at the bottom.
At cell A4, enter the following:
=FILTER(Sales, Sales[Region]=B1)
When you do this, the filter function will pull data from the Sales table in the SalesData tab and filter it based on your condition (Region = whatever is in cell B1).
You will get the following after you enter the function:
Now, to see that it is working, try typing another valid region from the dataset. To save you time, the only four valid regions are North, South, East, and West.
What happens if you type a word or phrase that is not in the Region column?
What happens if you type in north instead of North?
What, No Headings?
You may have noticed that the main Sales dataset has headings. But they don’t seem to carry over when you perform the filter function. While there is no direct way to add headings, you can achieve this by nesting a VSTACK() function.
The VSTACK() function vertically stacks two or more items. The header and the data would be the two items. The syntax is straightforward:
=VSTACK(Sales[#Headers], FILTER(Sales, Sales[Region]=B1))
When you run this calculation, you’ll get:
Other Considerations
I like to keep my tutorials short. When learners do so, they absorb more information without becoming overwhelmed. I’ll cut off further examples here. But there are other features of the FILTER() function that can add even more power to an already powerful function.
I’ve included a link to the Microsoft documentation page for you to explore the sort features. Further, the documentation shows how to use ranges instead of tables, which is also good to know.
FILTER function – Microsoft Support
Conclusion
You now have another function to use with your data. It is a great feature that lets you quickly filter your dataset without worrying about the complexities of pivot tables. The FILTER() function is not a replacement for pivot tables. It is something that can be used in different situations where a pivot table could be overkill. Who knows? You may even find several other use cases for it. That kind of stuff happens all the time in Excel.
About the Author James
James is a data science writer who has several years' experience in writing and technology. He helps others who are trying to break into the technology field like data science. If this is something you've been trying to do, you've come to the right place. You'll find resources to help you accomplish this.
