How to Use “Does Not Contain” in Excel Advanced Filter

Using "Does Not Contain" in Excel Advanced Filter allows you to filter out rows based on a specific text or value that you don't want to see in your filtered data. To use "Does Not Contain" in Excel, follow these steps:

1. Prepare your data: Make sure your data is organized in a table with clear headers.
2. Set up your criteria range: Create a separate area on your worksheet where you will specify the criteria for your advanced filter. This area should have the same column headers as your data table.
3. Define the criteria: In the cell below the corresponding header in the criteria range, use the following formula to specify that you want to filter out rows that contain a specific text or value:

<>"Text or value you want to exclude"

For example, if you want to exclude rows that contain the text "apple" in the "Fruit" column, the formula in the criteria cell would be:

<>"apple"

4. Apply the advanced filter: Click on any cell within your data table, then go to the Data tab in the Excel ribbon, and click on Advanced in the Sort & Filter group.
5. Configure the advanced filter settings: In the Advanced Filter dialog box, do the following:a. Select the "Filter the list, in-place" option if you want to filter the data in the original table, or select the "Copy to another location" option if you want to keep the original data and display the filtered results elsewhere.b. In the "List range" field, Excel should have automatically selected your data table. If not, click on the small icon to the right of the field, and manually select the data range.c. In the "Criteria range" field, click on the small icon to the right of the field, and manually select the criteria range you set up earlier, including the column headers and the criteria formulas.d. If you chose the "Copy to another location" option, specify the "Copy to" field by clicking on the small icon to the right of the field and selecting the top-left cell where you want the filtered data to appear.
6. Apply the filter: Click on the OK button to apply the advanced filter. Your data will now be filtered to exclude rows containing the specified text or value.

Example

Let's say you have a table with sales data, and you want to filter out rows where the Product column does not contain "Laptop".

1. Prepare your data table:

2. Set up your criteria range:

3. Apply the advanced filter as described in steps 4-6 above.
4. The filtered data will now exclude rows containing "Laptop" in the Product column: