How to Use “Contains” in Excel Advanced Filter

Using the "Contains" function in Excel Advanced Filter allows you to filter data based on a specific text or value within the cells. This can be especially useful when working with large datasets and you want to narrow down your results based on a particular criterion. Here's how to use "Contains" in Excel Advanced Filter:

1. Prepare your data: Ensure that your data is organized with column headers, and there are no blank rows or columns within the dataset.
2. Click on any cell within the dataset.
3. Go to the "Data" tab on the Excel ribbon.
4. Click on "Advanced" in the "Sort & Filter" group.
5. In the "Advanced Filter" dialog box, choose whether to filter the list in-place or copy it to another location. If you choose to copy it, select a cell in the "Copy to" field where you want the filtered data to be placed.
6. In the "Criteria range" field, select the range of cells that contain your filter criteria. This range should include the column header(s) and the criteria you want to use for the "Contains" function.
7. To use the "Contains" function, you'll need to use a wildcard character. In Excel, the asterisk (*) serves as a wildcard, representing any number of characters. For example, if you want to filter based on cells that contain the text "apple," your criteria should look like this: *apple*.

Example

Let's say you have a dataset of fruit sales, and you want to filter the data to display only rows that contain the word "apple" in the "Product" column. Here's how you would set up the advanced filter:

1. Prepare your data and ensure it has column headers such as "Product," "Sales," "Date," etc.
2. Create your criteria range by typing the text *apple* in an empty cell. You should also copy the column header "Product" and place it above the cell containing *apple*. Your criteria range should look like this:

Product
*apple*

3. Click on any cell within your dataset.
4. Go to the "Data" tab and click on "Advanced" in the "Sort & Filter" group.
5. In the "Advanced Filter" dialog box, choose whether to filter the list in-place or copy it to another location.
6. In the "Criteria range" field, select the range of cells containing your column header "Product" and the criteria *apple*.
7. Click "OK" to apply the advanced filter. Excel will now display only rows where the "Product" column contains the word "apple".