5 Ways to Filter Excel Data Using Value Lists

Filtering data in Excel is an essential skill for anyone dealing with large datasets, allowing you to quickly isolate and analyze specific information. One effective method to filter data is by using value lists. This approach provides a flexible and precise way to manage your data. Let's dive into five methods to filter Excel data using value lists.

Method 1: Custom AutoFilter

Excel’s AutoFilter feature offers a basic but effective way to filter data based on specific values.

• Select the range or table you want to filter.
• Go to the Home tab, and click on Filter under Sort & Filter. Alternatively, press Ctrl + Shift + L.
• Click the filter dropdown arrow for the column you wish to filter by.
• Choose Filter by Value and then Custom Filter.
• Enter the list of values you want to filter for under OR, separated by commas.
• Press OK to apply the filter.

How to Use AutoFilter for Multiple Values

⚠️ Note: Excel’s Custom AutoFilter allows for up to two criteria at once, but you can use this in conjunction with Advanced Filter or VBA for more complex filtering.

Method 2: Advanced Filter

For more complex filtering tasks, the Advanced Filter option provides additional functionality.

• Select the data range you want to filter.
• Go to Data tab, and click on Advanced under Sort & Filter.
• Choose the Action you want: Filter the list, in-place or Copy to another location.
• Specify the List Range (your data), and Criteria Range where you list your filter criteria.
• If copying to another location, specify the Copy To range.
• Press OK to apply the filter.

Setting Up a Criteria Range

Here’s how to set up a criteria range:

Method 3: Using Named Ranges and Dynamic Arrays

With Excel 365 and Excel 2019, dynamic arrays make filtering even more versatile.

• Create a named range for your filter list.
• Enter your list values in a separate area of your worksheet, and name the range (e.g., “FilterList”).
• Use a formula with FILTER function to dynamically filter data:

=FILTER(A2:D100, ISNUMBER(MATCH(A2:A100, FilterList, 0)))

Where A2:A100 is the range you want to filter, and FilterList is the named range with your filter criteria.

📝 Note: Dynamic arrays are only available in Excel 365 and Excel 2019 or later versions.

Method 4: VBA Macros for Complex Filtering

If you need to perform filtering tasks that are repetitive or beyond Excel’s built-in capabilities, VBA macros can help.

• Open the VBA Editor by pressing Alt + F11.
• Insert a new module by clicking Insert > Module.
• Paste the following VBA code:

Sub FilterBasedOnValueList()
    Dim ws As Worksheet
    Dim rng As Range
    Dim filterList As Variant
    Dim i As Integer

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A2:D100")
filterList = Array("Sales", "Marketing") ' Add your filter values here

' Clear existing filters
ws.AutoFilterMode = False

' Apply new filter
rng.AutoFilter Field:=1, Criteria1:=filterList, Operator:=xlFilterValues


End Sub

This macro filters data in column A based on the value list provided in the code.

💡 Note: Ensure that your VBA macro security settings allow for macro execution.

Method 5: Power Query

Power Query, part of Excel’s data transformation tools, offers powerful data manipulation capabilities, including complex filtering with value lists.

• Select your data range and click Data > From Table/Range.
• In the Power Query Editor, click Home > Advanced Editor.
• Paste the following code to filter your data:

let
    Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
    FilterList = {“Sales”, “Marketing”},
    FilteredRows = Table.SelectRows(Source, each List.Contains(FilterList, [Department]))
in
    FilteredRows

This code filters rows where the Department column matches values in the FilterList. Click Done and then Close & Load to get your filtered data back into Excel.

📈 Note: Power Query is excellent for handling large datasets and merging data from multiple sources.

Summing up, these five methods for filtering data in Excel using value lists provide various levels of functionality and complexity. From the simple AutoFilter for quick tasks to the sophisticated Power Query for extensive data manipulation, Excel offers tools to meet diverse needs. Whether you're managing inventory, analyzing sales data, or preparing reports, understanding these techniques will streamline your data management processes and enhance your analytical capabilities. By incorporating these methods into your workflow, you'll save time and reduce errors, allowing you to focus on the insights rather than the sorting.

Can I use these filtering methods on multiple columns at once?

+

Yes, you can filter multiple columns using the Advanced Filter method by creating a criteria range that includes conditions for each column you wish to filter. With Power Query, you can also apply multiple filter conditions in a single step.

Is there a way to save filter settings for later use?

+

Yes, using Power Query allows you to save your queries, essentially saving your filter settings. For simpler cases, you can store filter criteria in named ranges or within macros for easy reuse.

Can I filter data based on partial text matches?

+

Yes, with custom filters and Power Query, you can set up text filters that look for partial matches, such as using wildcards in Custom AutoFilter or text functions in Power Query.

How can I filter data based on conditions not directly visible in the cell (like formula results)?

+

In Excel, you can use calculated columns or helper columns with formulas to make complex conditions visible for filtering. Alternatively, Power Query can perform filtering based on computed fields not visible in the original dataset.

Do these methods work in all versions of Excel?

+

Custom AutoFilter, Advanced Filter, and VBA are available in all versions of Excel. However, dynamic arrays and Power Query require Excel 365, Excel 2019, or later versions.