The short answer: Tableau does not have a dedicated NOT IN function. You achieve the same logic by writing NOT ( [Field] IN (value1, value2, ...) ) or by using the NOT operator with an IN expression. This works in calculated fields, filters, and sets. Below is a complete guide to using the IN and NOT IN patterns in Tableau, including syntax, examples, common pitfalls, and alternatives.
Understanding IN and NOT IN in Tableau
In SQL, NOT IN is a single operator that checks whether a value is absent from a list. Tableau’s expression language (used in calculated fields and filters) does not include a literal NOT IN keyword. Instead, Tableau provides the IN operator, which returns TRUE when a field’s value matches any item in a comma-separated list. To negate that, you wrap the entire IN expression with the NOT operator.
For example, to filter out orders from the USA and Canada, you would write:
NOT [Country] IN ("USA", "Canada")
This returns TRUE for every row where Country is not exactly “USA” or “Canada”. The parentheses after NOT are not required, but they make the logic clearer and avoid mistakes when combining with other conditions.
How to Write a NOT IN Calculated Field
To create a calculated field that flags rows that are not in a list:
- Right-click in the Data pane and select Create Calculated Field.
- Name it, for example,
Not in List. - Enter the expression:
NOT [Region] IN ("East", "West") - Click OK.
The result is a Boolean field (TRUE/FALSE). You can drag it to the Filters shelf and select TRUE to keep only rows that are not in the list. Alternatively, you can use it in a calculated field for more complex logic, such as:
IF NOT [Category] IN ("Furniture", "Office Supplies") THEN "Other" ELSE [Category] END
This returns “Other” for any category not in the list, and the original category otherwise.
Using NOT IN in Filters Directly
You don’t always need a calculated field. You can apply the same logic directly in a filter:
- Drag the field to the Filters shelf.
- In the filter dialog, choose the Condition tab.
- Select By formula and enter:
NOT [Region] = "East" AND NOT [Region] = "West"
However, the formula approach with IN is shorter and easier to maintain. Another way is to use the Exclude option in a filter’s context menu: right-click the field, select Filter, then choose the values you want to exclude, and check Exclude. That creates a filter that behaves like NOT IN for the selected values, but it’s not a formula you can reuse elsewhere.
Common Mistakes and Pitfalls
One frequent error is writing [Field] NOT IN ("a", "b") without parentheses. Tableau will throw a syntax error because the NOT operator must come before the whole expression. Always write NOT [Field] IN (...).
Another pitfall is case sensitivity. Tableau’s IN operator is case-sensitive for string comparisons. So NOT [Country] IN ("usa") will still return TRUE for “USA”. Use the UPPER() or LOWER() function to normalize cases if needed:
NOT UPPER([Country]) IN ("USA", "CANADA")
Also, be careful with NULL values. If a field contains NULL, NOT [Field] IN (...) returns NULL (not TRUE or FALSE), because NULL is unknown. To treat NULL as not in the list, add an explicit check:
IF ISNULL([Field]) THEN TRUE ELSE NOT [Field] IN ("a", "b") END
Alternatives to NOT IN: Sets, Groups, and Parameters
For recurring exclusions, Tableau offers several alternatives that are often more user-friendly than a calculated field.
- Sets: Create a set from a field (right-click > Create > Set). Then use the set in a filter and select Exclude. This is effectively a NOT IN on the set’s members.
- Groups: Group the values you want to exclude into a single group, then filter out that group. This works well for a small, fixed list.
- Parameters: Use a parameter with a list of allowed values, then write a filter like
[Field] = [Parameter]for a single value, or use[Field] IN ([Parameter])for multi-select parameters (available in Tableau 2020.1+). To negate, useNOT [Field] IN ([Parameter]).
Sets and groups are easier for end users to maintain because they don’t require editing formulas.
Performance Considerations
Using NOT IN in a calculated field can slow down large data sources because the calculation is evaluated row by row. If you’re filtering a large extract, consider using a data source filter instead, or pre-compute the exclusion in the database (e.g., in a custom SQL query). For most dashboards, the performance impact is negligible, but for millions of rows, it’s worth testing.
Another tip: when using NOT IN with a long list, keep the list short (under 100 items) to avoid readability issues. For longer lists, use a set or a join to a reference table.
Frequently Asked Questions
Why does my NOT IN formula return an error?
Most likely because you wrote [Field] NOT IN (...) instead of NOT [Field] IN (...). Also check that you’re using straight double quotes around string values and that the field name is spelled correctly. If the field is a dimension with spaces, wrap it in brackets, e.g., [Order Date].
Can I use NOT IN with dates or numbers?
Yes. The IN operator works with any data type, as long as the list values match the field type. For dates, use the DATE() function to ensure proper comparison: NOT [Order Date] IN (DATE("2023-01-01"), DATE("2023-02-01")). For numbers, just list them without quotes: NOT [Quantity] IN (1,2,3).
Is NOT IN the same as “does not contain”?
No. NOT IN checks for exact matches against a list. “Does not contain” is a substring search, which you’d write using NOT CONTAINS([Field], "text"). They are different: NOT IN excludes whole values, while NOT CONTAINS excludes any value containing the substring.
How do I use NOT IN with multiple fields?
You can combine conditions with AND/OR. For example, to exclude rows where both field A is in list1 and field B is in list2, write: NOT ( [A] IN ("x","y") AND [B] IN ("1","2") ). To exclude rows where either is in the list, use OR: NOT ( [A] IN ("x") OR [B] IN ("1") ). Be mindful of parentheses.
Can I use NOT IN in a set or group?
Sets and groups don’t use NOT IN directly; they use their own inclusion/exclusion mechanisms. For a set, you can right-click the set and choose Create Set then use the Exclude option. For groups, you can create a group of the values you want to exclude, then filter out that group. Both achieve the same result as NOT IN.
Conclusion
While Tableau lacks a literal NOT IN function, the pattern NOT [Field] IN (list) is simple, flexible, and works everywhere. Use it in calculated fields, filters, and sets. For large datasets, consider pre-aggregating or using data source filters to keep performance high. With the examples above, you can now exclude any set of values confidently in your Tableau workbooks.
Related
- Split in Oracle SQL: Turn Delimited Text Into Rows
- Python join() Explained: Syntax, Examples, and Tips
- Snowflake SPLIT_PART: Syntax, Examples, and Edge Cases
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculating Markup in Excel: Formulas and ExamplesTo calculate markup in Excel, subtract cost from selling price, then divide the difference by cost: with cost…
- Excel Combine Cells With Dates and Text: FormulasTo combine a date in A2 with text in B2, use =TEXT(A2,"mmmm d, yyyy")&" "&B2. If A2 contains…
- Unique Excel Values: Shortcuts and the UNIQUE FunctionExcel has no single keyboard shortcut that instantly lists unique values. In Microsoft 365 for Windows, the quickest…
- Days Left in Month in Excel: Formula and Examples for DatesIf the date is in A2, use =EOMONTH(A2,0)-A2 to count the days remaining after that date, not including…
- Daily Timesheet Excel Formula: Calculate Hours CorrectlyFor a daily timesheet with a start time in B2, an end time in C2, and an unpaid…
- Excel Formula for Hours Between Two TimesTo calculate hours between two times in Excel, subtract the start time from the end time and multiply…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.