Operators in conditional formatting and filter condition expressions
Table 11‑3 lists the operators you can use when you create expressions for conditional formatting and filter conditions.
Table 11‑3 Operators in conditional formatting and filter condition expressions
Operator
Use to
Example
Between
Test if a column value is between two specified values.
Profit Between 1000 and 2000
Bottom N
Test if a column value is among the lowest n values.
SalesAmount Bottom N 10
Bottom Percent
Test if a column value is in the bottom n percent of all values.
SalesAmount Bottom Percent 5
Equal to
Test if a column value is equal to a specified value.
Country Equal to France
Greater Than
Test if a column value is greater than a specified value.
Total Greater Than 5000
Greater Than or Equal to
Test if a column value is greater than or equal to a specified value.
Total Greater Than or Equal to 5000
In
Test if a column value is in the list of specified values. Use to select more than a single comparison value.
Country In USA, Canada, Mexico
Is Blank
Test if a column value is blank (" "). This operator applies only to string values.
E‑mail Is Blank
Is False
Test if a column value is false.
LoanApproved Is False
Is Not Blank
Test if a column value is not blank. This operator applies only to string values.
Email Is Not Blank
Is Not Null
Test if a column value is not a null value. A null value means that no value is supplied.
CreditLimit Is Not Null
Is Null
Tests if a column value is a null value. A null value means that no value is supplied.
CreditLimit Is Null
Is True
Test if a column value is true.
LoanApproved Is True
Less Than
Test if a column value is less than a specified value.
Total Less Than 5000
Less Than or Equal to
Test if a column value is less than or equal to a specified value.
Total Less Than or Equal to 5000
Like
Test if a column value matches a string pattern.
ProductName Like %Ford%
Month to Date
Test if a column value matches a date value.
Month to Date 3/15/2011
Month to Date Last Year
Test if a column value matches a date value and displays last year’s values.
Month to Date Last Year 3/15/2011
Match
Test if a column value matches a string pattern.
ProductCode Match S20
Not Between
Test if a column value is not between two specified values.
Profit Not Between 1000 and 2000
Not Equal to
Test if a column value is not equal to a specified value.
Country Not Equal to France
Not In
Test if a column value is not in the specified list of values.
Country Not In USA, Canada, Mexico
Not Like
Test if a column value does not match a string pattern.
ProductName Not Like %Ford%
Not Match
Test if a column value does not match a string pattern.
Product Code Not Match S10
Top N
Test if a column value is among the top n values.
SalesAmount Top N 10
Top Percent
Test if a column value is in the top n percent of all values.
SalesAmount Top Percent 5
Year to Date
Test if a column value matches a date value.
Year to Date 3/15/2011
Year to Date Last Year
Test if a column value matches a date value and displays last year’s values.
Year to Date Last Year
3/15/2011